Showing posts with label MSSQLServer. Show all posts

Saturday, July 21, 2018

Export Large SQL Tables into Multiple Text Files

A requirement has been arisen to export large SQL tables into a cloud storage. Nowadays, there are many ETL tools and cloud services in the market that support this process. However, in this particular scenario, the requirement was to develop a custom proc to export a given table into multiple CSV files based on a batch size (i.e. specific number of rows per file)

Following proc generates a dynamic BCP command and then execute it using xp_cmdshell system proc.

CREATE PROCEDURE [dbo].[sp_Export_Large_SQL_Tables]
 @Server  VARCHAR(200)
 , @DBName VARCHAR(200)  
 , @TableName VARCHAR(200)  
 , @Delimiter VARCHAR(200) = '|'  -- Output File Delimiter
 , @OutputExt VARCHAR(200) = 'csv' -- Output File Extension
 , @OutputDir VARCHAR(200) 
 , @LogDir VARCHAR(200) = null
 , @OrderByCol VARCHAR(200) = null
 , @BatchSize INT = 500000
AS
/**************************************************************************  
-- Name   : [dbo].[sp_Export_Large_SQL_Tables]
-- Desc   : 
-- Notes  : 
-- Dependencies         : bcp.exe
**************************************************************************  
-- Ve   Date        Author     Description  
-- --   --------    -------    --------------------------- 
-- 1    2018-05-31 Isura Silva  Created
*************************************************************************/
BEGIN
 SET NOCOUNT ON
 DECLARE @vcStep VARCHAR(max) = '' -- Store Custom error message
 DECLARE @vcSQL VARCHAR(max)  = '' -- Store execution command
 DECLARE @Proc_Name varchar(255) = OBJECT_NAME(@@PROCID) -- Automatically picks the proc name
 ------- 
 SET @LogDir = ISNULL(@LogDir,@OutputDir) ;
 SET @Server = ISNULL(@Server, 'localhost');
 SET @Delimiter = ISNULL(@Delimiter, '|');
 SET @OutputExt = ISNULL(@OutputExt, '.csv'); -- Output File Extension
 IF @OrderByCol IS NULL OR @OrderByCol = ''
  BEGIN
  SET @vcSQL = 'SELECT TOP 1 @OrderByCol = COLUMN_NAME FROM ' 
   + @DBName + '.[INFORMATION_SCHEMA].[COLUMNS] WHERE CONCAT([TABLE_SCHEMA],''' + '.' +''',[TABLE_NAME]) = ''' 
   + @TableName + ''' OR [TABLE_NAME] = ''' + @TableName + ''''
  -- PRINT @vcSQL;
  DECLARE @vcStep2 NVARCHAR(max) = CAST(@vcSQL AS NVARCHAR(MAX))
  EXEC sp_executesql @vcStep2, @params = N'@OrderByCol VARCHAR(200) OUTPUT', @OrderByCol = @OrderByCol OUTPUT
  END
 ------- 
 DECLARE @BCPexe VARCHAR(200) = CASE 
  -- WHEN CONVERT(VARCHAR(128), SERVERPROPERTY ('productversion')) like '10.0%' THEN 'unknown' -- 'SQL2008'
  -- WHEN CONVERT(VARCHAR(128), SERVERPROPERTY ('productversion')) like '10.5%' THEN 'unknown' -- 'SQL2008 R2'
  -- WHEN CONVERT(VARCHAR(128), SERVERPROPERTY ('productversion')) like '11%' THEN 'unknown' -- 'SQL2012'
  -- WHEN CONVERT(VARCHAR(128), SERVERPROPERTY ('productversion')) like '12%' THEN 'unknown' -- 'SQL2014'
  WHEN CONVERT(VARCHAR(128), SERVERPROPERTY ('productversion')) like '13%' THEN 'C:\Program Files\Microsoft SQL Server\Client SDK\ODBC\130\Tools\Binn\bcp.exe' --'SQL2016'     
  WHEN CONVERT(VARCHAR(128), SERVERPROPERTY ('productversion')) like '14%' THEN 'C:\Program Files\Microsoft SQL Server\Client SDK\ODBC\130\Tools\Binn\bcp.exe' --'SQL2017' 
  ELSE 'C:\Program Files\Microsoft SQL Server\Client SDK\ODBC\130\Tools\Binn\bcp.exe'
  END

 DECLARE @SQLQuery VARCHAR(8000) = '';
 DECLARE @SQLbcp  VARCHAR(8000) = '';
 DECLARE @OutputPath VARCHAR(200);
 DECLARE @LogPath VARCHAR(200);
 -------
 DECLARE @SQLRowCount NVARCHAR(4000)= N'';
 DECLARE @CurrentRowCount INT = 0;
 DECLARE @TableRowCount INT;
 BEGIN TRY 
 Print '---------------------------------------------------------------------------------------------------------------'
  -- Get the row count of SQL table
  SET @SQLRowCount = CAST(('SELECT @TableRowCount = COUNT(*) FROM ' + @DBName + '.' + @TableName) AS nvarchar(4000)) 
  EXEC sp_executesql @SQLRowCount, @params = N'@TableRowCount INT OUTPUT', @TableRowCount = @TableRowCount OUTPUT
  --PRINT @SQLRowCount
 
  WHILE (@CurrentRowCount < @TableRowCount) 
  BEGIN 
   -- Pupulate the input query for BCP command based on the batch size (default batch is 500K rows)
   SET @SQLQuery = 'SELECT * FROM ' + @DBName + '.' + @TableName + ' ORDER BY ' + quotename(@OrderByCol) + ' ASC '
   + ' OFFSET ' + CAST(@CurrentRowCount as VARCHAR(100)) + ' ROWS FETCH NEXT ' + CAST(@BatchSize AS VARCHAR(100)) 
   + ' ROWS ONLY'

  ---------------------------------------- 
   SET @OutputPath =  @OutputDir + '\' + @TableName + '_' + CAST(@CurrentRowCount as VARCHAR(100)) + '-' + CAST((@CurrentRowCount+ @BatchSize) as VARCHAR(100)) + '.csv';
   SET @LogPath = @LogDir + '\' + @TableName + '.log';
   -- Pupulate BCP command
   SET @SQLbcp = 'CALL ' +  quotename(@BCPexe, '"') + ' ' 
    + '"' + @SQLQuery + '"'
    + ' queryout ' + quotename(@OutputPath, '"')
    + ' -S ' + quotename(@Server, '"') 
    + ' -d ' + quotename(@DBName, '"') 
    + ' -t ' + quotename(@Delimiter, '"') 
    + ' -e ' + quotename(@LogPath, '"') 
    + ' -T -c -C 65001 -q '

   PRINT @SQLbcp
   DECLARE @result int;  
   
   --------
   EXEC @result = xp_cmdshell @SQLbcp , NO_OUTPUT;  
   IF (@result = 0 and (@SQLbcp <> '' OR @SQLbcp IS NOT NULL))  
      PRINT 'Success'  
   ELSE  
    PRINT 'Failure'
    PRINT @SQLbcp;

  SET @CurrentRowCount = @CurrentRowCount + @BatchSize 
  END
  ----------------------------------------
  Print '---------------------------------------------------------------------------------------------------------------'
 END TRY  
 BEGIN CATCH  
  PRINT ERROR_MESSAGE() 
        RAISERROR(@Proc_Name,16,1)
 END CATCH
END

e.g. Call procedure - TSQL

EXEC [dbo].[sp_Export_Large_SQL_Tables] @Server = 'Localhost'
 , @DBName = 'Insight'
 , @TableName = 'dbo.YOU_Detailed'
 , @Delimiter = '|' 
 , @OutputExt = 'csv'
 , @OutputDir = 'E:\Insight\Export'
 , @LogDir = null
 , @OrderByCol = null
 , @BatchSize = 500000

e.g. Call procedure - PowerShell

$ServerInstance = "Localhost"
$Database       = "Insight"
$TableName      = "dbo.YOU_Detailed"
$OutputDir      = "E:\Insight\Export"
   
## Export SQL tables to CSV
$SQLcmd  = "EXEC [dbo].[sp_Export_Large_SQL_Tables] @Server = " + "'" + $ServerInstance + "'" `
    + ", @DBName = " + "'" + $Database + "'" `
    + ", @TableName = " + "'" + $TableName + "'" `
    + ", @Delimiter = " + "'|'" `
    + ", @OutputExt = " + "'csv'" `
    + ", @OutputDir = " + "'" + $OutputDir + "'" `
    + ", @LogDir = " + "null" `
    + ", @OrderByCol    = " + "null" `
    + ", @BatchSize = " + "500000" `
 
Invoke-Sqlcmd -ServerInstance $ServerInstance -Query $SQLcmd  -Database "Utils" -QueryTimeout 0 -verbose

Thursday, April 5, 2018

Execute the result of a dynamic SQL using SP_EXECUTESQL


Recently, I came across T-SQL procedure which is developed to take backups of MS SQL tables in a separate database (i.e. Trash ) with 'xxx_TableName_YYYYMMDD' format. Since the proc keeps on adding backup tables every time it gets executed, the following query will limit it to last 5 tables in the trash database.



The following code also shows how to execute a result of a dynamic query (@SQL1) in another dynamic query (@SQL3) using SP_EXECUTESQL system procedure.



--------------------------------------
-- Keep the last 5 backup tables and delete the rest for a given table in Trash DB
DECLARE @Source_Table VARCHAR(100)
DECLARE @Backup_Table VARCHAR(100)
DECLARE @Backup_Table_Prefix VARCHAR(100)
DECLARE @WhereClause VARCHAR(Max) 
DECLARE @Date VARCHAR(8)
DECLARE @SQL1 NVARCHAR(MAX) ;
DECLARE @SQL3 NVARCHAR(MAX) = '' ;
--------------------------------------
BEGIN
 SET @Source_Table = 'Test_Table'
 SET @Backup_Table_Prefix = 'xxx_'
 SET @Date = CONVERT(VARCHAR(8),GETDATE(),112) -- YYYYMMDD
 SET @Backup_Table = @Backup_Table_Prefix + @Source_Table + '_' + @Date -- 'xxx_Test_table_YYYYMMDD'
 SET @WhereClause =  @Backup_Table_Prefix + @Source_Table + '_[0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9]';
 --PRINT @WhereClause
 
 SET @SQL1 =  
  'SELECT @SQL2 += '' DROP TABLE trash.dbo.'''  + ' + a.name + '' ; ''' +
  ' FROM trash.sys.tables a
   JOIN (
    SELECT name,
    RankRows = ROW_NUMBER()over (ORDER by name DESC) 
    FROM trash.sys.tables 
    WHERE name like ''' + @WhereClause 
   + ''' ) b ON a.name = b.name WHERE b.RankRows > 5; '

 IF @SQL1 IS NOT NULL AND  @SQL1 <> ''
  --PRINT @SQL1; 
  EXEC SP_EXECUTESQL @SQL1 , N'@SQL2 NVARCHAR(MAX) OUTPUT', @SQL3 OUTPUT

 IF @SQL3 IS NOT NULL AND  @SQL3 <> ''
  --PRINT @SQL3; 
  EXEC SP_EXECUTESQL @SQL3
END
--------------------------------------

Thursday, October 19, 2017

Find SQLserver sessions to a DB and Kill all in one goal

Get the list of connections

Select * from master.dbo.sysprocesses
where dbid = db_id('databaseName')

Kill all the connection to a given database

Use Master
Go

Declare @dbname sysname

Set @dbname = 'databaseName'

Declare @spid int
Select @spid = min(spid) from master.dbo.sysprocesses
where dbid = db_id(@dbname)
While @spid Is Not Null
Begin
        Execute ('Kill ' + @spid)
        Select @spid = min(spid) from master.dbo.sysprocesses
        where dbid = db_id(@dbname) and spid > @spid
End
GOReference : http://stackoverflow.com/questions/1154200/when-restoring-a-backup-how-do-i-disconnect-all-active-connections

Function & SSIS Expression to Retrieve MonthNumber in YYYYMM Format

TSQL Function
E.g. 1. Following function can be used to return Month number in YYYYMM format for a month name in "MON-YYYY", "MON YYYY", "MON/YYYY" Formats.

CREATE FUNCTION dbo.[Retrieve MonthNumber(YYYYMM)](@MonthName Varchar(50), @MonthNameFormat Varchar(50))
RETURNS INT AS
BEGIN
    DECLARE @MonthNumber INT;
    SET @MonthName = UPPER(@MonthName);
    RETURN CASE
        WHEN UPPER(@MonthNameFormat) = 'MON-YYYY' OR UPPER(@MonthNameFormat) = 'MON YYYY' THEN
            CASE
                WHEN LEFT(@MonthName, 3) = 'JAN' THEN CAST(RIGHT(@MonthName,4) + '01' AS INTEGER)
                WHEN LEFT(@MonthName, 3) = 'FEB' THEN CAST(RIGHT(@MonthName,4) + '02' AS INTEGER)
                WHEN LEFT(@MonthName, 3) = 'MAR' THEN CAST(RIGHT(@MonthName,4) + '03' AS INTEGER)
                WHEN LEFT(@MonthName, 3) = 'APR' THEN CAST(RIGHT(@MonthName,4) + '04' AS INTEGER)
                WHEN LEFT(@MonthName, 3) = 'MAY' THEN CAST(RIGHT(@MonthName,4) + '05' AS INTEGER)
                WHEN LEFT(@MonthName, 3) = 'JUN' THEN CAST(RIGHT(@MonthName,4) + '06' AS INTEGER)
                WHEN LEFT(@MonthName, 3) = 'JUL' THEN CAST(RIGHT(@MonthName,4) + '07' AS INTEGER)
                WHEN LEFT(@MonthName, 3) = 'AUG' THEN CAST(RIGHT(@MonthName,4) + '08' AS INTEGER)
                WHEN LEFT(@MonthName, 3) = 'SEP' THEN CAST(RIGHT(@MonthName,4) + '09' AS INTEGER)
                WHEN LEFT(@MonthName, 3) = 'OCT' THEN CAST(RIGHT(@MonthName,4) + '10' AS INTEGER)
                WHEN LEFT(@MonthName, 3) = 'NOV' THEN CAST(RIGHT(@MonthName,4) + '11' AS INTEGER)
                WHEN LEFT(@MonthName, 3) = 'DEC' THEN CAST(RIGHT(@MonthName,4) + '12' AS INTEGER)
                ELSE
                    0
            END
        WHEN UPPER(@MonthNameFormat) = 'MM/YYYY' OR UPPER(@MonthNameFormat) = 'MM-YYYY' THEN
            CASE
                WHEN LEFT(@MonthName, 2) = '01' THEN CAST(RIGHT(@MonthName,4) + '01' AS INTEGER)
                WHEN LEFT(@MonthName, 2) = '02' THEN CAST(RIGHT(@MonthName,4) + '02' AS INTEGER)
                WHEN LEFT(@MonthName, 2) = '03' THEN CAST(RIGHT(@MonthName,4) + '03' AS INTEGER)
                WHEN LEFT(@MonthName, 2) = '04' THEN CAST(RIGHT(@MonthName,4) + '04' AS INTEGER)
                WHEN LEFT(@MonthName, 2) = '05' THEN CAST(RIGHT(@MonthName,4) + '05' AS INTEGER)
                WHEN LEFT(@MonthName, 2) = '06' THEN CAST(RIGHT(@MonthName,4) + '06' AS INTEGER)
                WHEN LEFT(@MonthName, 2) = '07' THEN CAST(RIGHT(@MonthName,4) + '07' AS INTEGER)
                WHEN LEFT(@MonthName, 2) = '08' THEN CAST(RIGHT(@MonthName,4) + '08' AS INTEGER)
                WHEN LEFT(@MonthName, 2) = '09' THEN CAST(RIGHT(@MonthName,4) + '09' AS INTEGER)
                WHEN LEFT(@MonthName, 2) = '10' THEN CAST(RIGHT(@MonthName,4) + '10' AS INTEGER)
                WHEN LEFT(@MonthName, 2) = '11' THEN CAST(RIGHT(@MonthName,4) + '11' AS INTEGER)
                WHEN LEFT(@MonthName, 2) = '12' THEN CAST(RIGHT(@MonthName,4) + '12' AS INTEGER)
                ELSE
                    0
            END
    ELSE
        0
    END
END;
GO

Following qeury can be used to test / execute above function.
SELECT dbo.[Retrieve MonthNumber(YYYYMM)]('01/2015', 'MM/YYYY')

E.g. 2. Following function can be used to return Month number in YYYYMM format for a month name in "### Mon YYYY" format.

CREATE FUNCTION [dbo].[GetMonthID] (
@Period VARCHAR(50))
RETURNS INT
AS
BEGIN
DECLARE @Year VARCHAR(50) = 0;
DECLARE @Month VARCHAR(50)= 0;
DECLARE @MonthID INT = NULL;

SELECT @Year = SUBSTRING( @Period, CHARINDEX('20',@Period, 1) , 4)
SELECT @Month = CASE
WHEN @Period LIKE '%JAN%' THEN '01'
WHEN @Period LIKE '%FEB%' THEN '02'
WHEN @Period LIKE '%MAR%' THEN '03'
WHEN @Period LIKE '%APR%' THEN '04'
WHEN @Period LIKE '%MAY%' THEN '05'
WHEN @Period LIKE '%JUN%' THEN '06'
WHEN @Period LIKE '%JUL%' THEN '07'
WHEN @Period LIKE '%AUG%' THEN '08'
WHEN @Period LIKE '%SEP%' THEN '09'
WHEN @Period LIKE '%OCT%' THEN '10'
WHEN @Period LIKE '%NOV%' THEN '11'
WHEN @Period LIKE '%DEC%' THEN '12'
END
IF ISNUMERIC(@Year + @Month) = 1
SET @MonthID = CAST(@Year + @Month AS INT);
ELSE
SET @MonthID = NULL

RETURN @MonthID

END;

Query
SELECT dbo.GetMonthID([Time Period NAME]) FROM dbo.TableName

SSIS Expression
Following expression can be used to return Month number in YYYYMM format for a month name in "MON-YYYY", "MON YYYY", "MON/YYYY" Formats.
(DT_I4)(SUBSTRING(Month,4,2) + RIGHT(Month,2) +
    (    FINDSTRING(Month,"JAN",1) > 0 ? "01" :
        FINDSTRING(Month,"FEB",1) > 0 ? "02" :
        FINDSTRING(Month,"MAR",1) > 0 ? "03" :
        FINDSTRING(Month,"APR",1) > 0 ? "04" :
        FINDSTRING(Month,"MAY",1) > 0 ? "05" :
        FINDSTRING(Month,"JUN",1) > 0 ? "06" :
        FINDSTRING(Month,"JUL",1) > 0 ? "07" :
        FINDSTRING(Month,"AUG",1) > 0 ? "08" :
        FINDSTRING(Month,"SEP",1) > 0 ? "09" :
        FINDSTRING(Month,"OCT",1) > 0 ? "10" :
        FINDSTRING(Month,"NOV",1) > 0 ? "11" :
        FINDSTRING(Month,"DEC",1) > 0 ? "12" :
        "00"
        )
    )

Monday, March 20, 2017

Use SQL to read XML data - Example

There are a few ways to read XML data using SQL and following example demonstrate how to read a simple XML file in tabular format.

DECLARE @MyXML XML
-- SET @MyXML = (SELECT CONVERT(xml, BulkColumn, 2) xml 
-- FROM OPENROWSET(Bulk 'C:\sample.XML', SINGLE_BLOB) [rowsetresults])
SET @MyXML = '
<Order xmlns="XMLSchema">
  <Database>
    <Tables>
      <Table Name="Product">
        <Files>
          <File FileName="Product.txt" NumberOfRows="17" RowDelimiter="{LF}" />
        </Files>
        <Columns>
          <Column Name="ATC_1_CD" Length="30" DataType="nvarchar" />
    <Column Name="IMS_PROD_SHRT_NM" Length="80" DataType="nvarchar" />
          <Column Name="PACK_DESC" Length="80" DataType="nvarchar" />
        </Columns>
      </Table>
   <Table Name="Corporation">
        <Files>
          <File FileName="Corporation.txt" NumberOfRows="5" RowDelimiter="{LF}" />
        </Files>
        <Columns>
          <Column Name="CORP_ID" Length="30" DataType="nvarchar" IsPrimaryKey="true" />
          <Column Name="CORP_SHRT_NM" Length="80" DataType="nvarchar" />
        </Columns>
      </Table>
    </Tables>
  </Database>
</Order>
'
;WITH XMLNAMESPACES (DEFAULT 'XMLSchema') -- Define Default XML schema (i.e. xmlns )
SELECT 
 FileName  = files.x.value('@FileName','varchar(200)'),
 TableName  = tabs.x.value('@Name','varchar(200)'),
 ColumnName  = cols.x.value('@Name','varchar(200)'),
 ColumnDataType = cols.x.value('@DataType','varchar(200)'),
 ColumnLength = cols.x.value('@Length','varchar(200)')
FROM
     @MyXML.nodes('/Order[1]/Database[1]/Tables[1]/Table') tabs(x) 
CROSS APPLY tabs.x.nodes('Columns[1]/Column') cols(x) -- Loop through Columns level

CROSS APPLY tabs.x.nodes('Files[1]/File') files(x) -- Loop through Files Level

Result

Reference

https://www.simple-talk.com/sql/database-administration/manipulating-xml-data-in-sql-server/

https://msdn.microsoft.com/en-GB/library/ms191184.aspx

Monday, December 1, 2014

Backup and Restore Multiple Databases and Cubes

SSAS Cubes
1. List all the cube names in a MS Analysis Server instance.
Open a MDX / DAX query window and run followoing DMVquery.
    SELECT * FROM $system.dbschema_catalogs
    ORDER BY [catalog_name] ASC

2. Backup multiple databases in a MS Analysis Server instance.
Sometimes, database name may not be same as database ID. In that case, DatabaseID needs to be found by going to properties of the database.
<Batch xmlns="http://schemas.microsoft.com/analysisservices/2003/engine" Transaction="false" >
   <Backup xmlns="http://schemas.microsoft.com/analysisservices/2003/engine">
       <Object>
   <DatabaseID>Cube 2</DatabaseID>
   </Object>
   <File>C:\Data\SQL Server Data\MSSQL.2\OLAP\Backup\Cube 1.abf</File>
       <AllowOverwrite>true</AllowOverwrite>
   </Backup>
   <Backup xmlns="http://schemas.microsoft.com/analysisservices/2003/engine">
   <Object>
   <DatabaseID>C:\Data\SQL Server Data\MSSQL.2\OLAP\Backup\Cube 2</DatabaseID>
   </Object>
   <File>Cube 2.abf</File>
   <AllowOverwrite>true</AllowOverwrite>
   </Backup>
</Batch>
Ref: http://blog.sqltechie.com/2010/01/how-to-backup-multiple-database-using.html

SQLServer Databases
1. Backup all the databases in a SQLServer instance can be done using following script (you can exclude any database by defining it within the where clause of select statement).

DECLARE @name VARCHAR(50) -- Database name
DECLARE @path VARCHAR(256) -- Backup files' path
DECLARE @fileName VARCHAR(256) -- Backup files' filename
DECLARE @fileDate VARCHAR(20) -- used for file name

-- specify database backup directory
SET @path = 'D:\Microsoft SQL Server 2005\Analysis Services\MSSQL.2\OLAP\Backup\DatabaseMigration-09-06-2014\'

-- specify filename format
SELECT @fileDate = CONVERT(VARCHAR(20),GETDATE(),112)

DECLARE cursor1 CURSOR FOR
SELECT name FROM master.dbo.sysdatabases
WHERE name NOT IN ('master','model','msdb','tempdb','Report Administration','ReportServer','ReportServerTempDB' )

OPEN cursor1
FETCH NEXT FROM cursor1 INTO @name

WHILE @@FETCH_STATUS = 0
BEGIN
       SET @fileName = @path + @name + '.BAK'
       BACKUP DATABASE @name TO DISK = @fileName

       FETCH NEXT FROM cursor1 INTO @name
END
CLOSE cursor1
DEALLOCATE cursor1

2. Restore database using following command
Back files need to be copied to MS SQL Server\MSSQL.1\MSSQL\data Folder.
RESTORE DATABASE [AnalyticsPortal]
   FROM DISK = 'D:\Microsoft SQL Server\MSSQL.1\MSSQL\Backup\AnalyticsPortal.BAK'
   WITH MOVE 'AnalyticsPortal' TO 'd:\Microsoft SQL Server\MSSQL.1\MSSQL\data\AnalyticsPortal.mdf',
   MOVE 'AnalyticsPortal_log' TO 'd:\Microsoft SQL Server\MSSQL.1\MSSQL\Data\AnalyticsPortal_1.LDF'

Tuesday, February 18, 2014

SQL Server Agent Jobs to Backup / Restore SSAS Cube

This post demostrates a few scripts which can be used within SQL Server Agent Jobs.

XMLA Script to backup a cube
<Backup xmlns="http://schemas.microsoft.com/analysisservices/2003/engine">
<Object>
<DatabaseID>Company UAT Cube</DatabaseID>
</Object>
<File>Company UAT Cube.abf</File>
<AllowOverwrite>true</AllowOverwrite>
<Password>AABB01</Password>
</Backup>

XMLA Script to restore a .ABF file

<Restore xmlns="http://schemas.microsoft.com/analysisservices/2003/engine">
<File>C:\Data\SQL Server Data\MSSQL.2\OLAP\Backup\Company Test Cube.abf</File>
<DatabaseName>Company Test Cube</DatabaseName>
<AllowOverwrite>true</AllowOverwrite>
<Password>AABB01</Password>
</Restore>
SSAJ - operating system command to copy a file
copy "D:\FTP Root\Test Cube.abf" "D:\Microsoft SQL Server 2005\Analysis Services\MSSQL.2\OLAP\Backup\Test Cube.abf" /Y

SSAJ - operating system command to run a batch file
cmd.exe /c "C:\PharmaAnalytics\Daiichi Sankyo Europe Instance\System\Scripts\CopyTestCubeToSrerver1ABFBackups.bat"

Batch script to FTP an ABF file
@echo off
echo user USERNAME PASSWORD>ftp.ftp
echo put "C:\Data\SQL Server Data\MSSQL.2\OLAP\Backup\Cube Test.abf">>ftp.ftp
echo bye>>ftp.ftp
ftp -n -s:ftp.ftp 94.1.1.2
del /q ftp.ftp

Wednesday, January 22, 2014

SSAS Local Cube

1 Creating a local cube

Local cubes and local mining models allow analysis on a client workstation while it is disconnected from the network. For example, a client application might call the OLE DB for OLAP 9.0 Provider (MSOLAP.3), which loads the local cube engine to create and query local cubes, as shown in the following illustration:

Ref: Microsft

1.1 Create the XMLA script

Create the XMLA script using server cube. Then copy script to Clipboard.

1.2 Create the local cube file and connect to the local cube.

Specify the local cube path and the filename (including .cub as the extention) as the server name. Then press "connect" which will create cube file.

1.3 Open a new XMLA query

Open a new XMLA query window by right clicking on local cube connection and paste the script copied above.

1.4 Execute the script file

You can search for following elements in the script file to make sure that you are connecting to right database.

1.5 Process the cube

Right click on the local cube database and click “Process”

Ignore following error messages

Value cannot be null.

Parameter name: key (System)

2 Access local cube file

2.1 Microsoft Excel

.cub file can be simply browsed using Excel (2010/2013) and you will get the same interface that you get when access SSAS server cube using Excel.

2.2 XLCubed

.cub file can be also simple connected to XLCubed by creating XLCubed connection. Connection type should be “Analysis Service Cube File.

3 Benefits

Local cube files can dramatically improve browsing speed performance, especially when analysing low levels of large dimensions.
Local cube files can also improve browsing performance because requests for additional data are handled on the local computer rather than across a network on an Analysis Server.
Local cube files can now be encrypted and password-protected.
Analysis Services 2005 provides more precise control over the creation of local cube files.
When you use local cube files you can give each user the specific data they need, or want, or are allowed to see.

4 Limitations

When creating local cubes from server-based cubes, the following considerations apply:
Distinct count measures are not supported.
When you add a measure, you must also include at least one dimension that is related to the measure being added.
When you add a parent-child hierarchy, levels and filters on a parent-child hierarchy are ignored and the entire parent-child hierarchy is included.
Member properties are not created.
When you include a semi-additive measure, no slices are permitted on either the Account or the Time dimension.
Reference dimensions are always materialized.
When you include a many-to-many dimension, the following rules apply:

You cannot slice the many-to-many dimension.
You must add a measure from the intermediary measure group.
You cannot slice any of the dimensions common to the two measure groups involved in the many-to-may relationship.

Only those calculated members, named sets, and assignments that rely upon measures and dimensions added to the local cube will appear in the local cube. Invalid calculated members, named sets, and assignments will be automatically excluded.

Ref: http://technet.microsoft.com/en-us/library/bb522640.aspx

Isura's Blog

Pages