Isura's Blog

Export Large SQL Tables into Multiple Text Files

2018-07-21T15:35:00.002-07:00

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

Execute the result of a dynamic SQL using SP_EXECUTESQL

2018-04-05T03:54:00.003-07:00


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
--------------------------------------

SSAS + Forecast calculations MDX

2017-10-19T01:58:00.001-07:00

Following forecast member returns forecast figures for a given measure based on the entire history.

WITH MEMBER
[Measures Utility].[Measure Calculations].[Linear Regression Forecast]
AS LinRegPoint(
    Rank(
        [Period].[Extended Year - Quarter - Month].CurrentMember,
        [Period].[Extended Year - Quarter - Month].CurrentMember.Level.Members
        ),
    Exists(
        [Period].[Extended Year - Quarter - Month].CurrentMember.Level.Members,
        [Period].[Calendar Year - Quarter - Month].[Month].Members),

    ([Measures].CurrentMember, [Measures Utility].[Measure Calculations].DefaultMember),
    Rank(
    [Period].[Extended Year - Quarter - Month].CurrentMember,
    [Period].[Extended Year - Quarter - Month].CurrentMember.Level.Members)
)

SELECT
([Measures].[RXA Value] *
[Measures Utility].[Measure Calculations].[Linear Regression Forecast]
) on 0,
NON EMPTY {[Period].[Extended Month].Children}
ON 1
FROM [Cube Name]

Forecasts based on 3 months data points
CREATE MEMBER CURRENTCUBE.[Measures Utility].[Measure Calculations].[Linear Regression Forecast - 3 Data Points]
AS LinRegPoint(
    Rank([Period].[Operational Year - Quarter - Month].CurrentMember,
            {
            Tail(Exists([Period].[Operational Year - Quarter - Month].Month.Members, [Period].[Calendar Year - Quarter - Month].[Month].Members),1).Item(0).Item(0).Lag(2):
            Tail(Exists([Period].[Operational Year - Quarter - Month].Month.Members, [Period].[Calendar Year - Quarter - Month].[Month].Members),1).Item(0).Item(0).Parent.Parent.LastChild.LastChild
            } --[Output Period]
        ), -- Measures.OutputX (Current Financial Year)
        Tail(Exists([Period].[Operational Year - Quarter - Month].Month.Members, [Period].[Calendar Year - Quarter - Month].[Month].Members),3), -- [Input Period] -- 3 Months
    (    [Measures].CurrentMember,
        [Measures Utility].[Measure Calculations].DefaultMember), -- Measures.Input Y
    Rank(
        [Period].[Operational Year - Quarter - Month].CurrentMember,
        Tail(Exists([Period].[Operational Year - Quarter - Month].Month.Members, [Period].[Calendar Year - Quarter - Month].[Month].Members),3) --[Input Period] -- 3 Months
        ) -- Measures.InputX
    ),
FORMAT_STRING = "#,#.00",
VISIBLE = 1;

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

2017-10-19T01:55:00.001-07:00

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

Unzip all the zip files in a folder + PowerShell

2017-10-19T01:54:00.000-07:00

PARAM

(
    [string] $ZipFilesPath = "E:\TEMP\TEMP DSE",
    [string] $UnzipPath = "E:\TEMP\TEMP DSE"
)
$Shell = New-Object -com Shell.Application
$Location = $Shell.NameSpace($UnzipPath)
 $ZipFiles = Get-Childitem $ZipFilesPath -Recurse -Include *.zip
 
$progress = 1
foreach ($ZipFile in $ZipFiles) {
    Write-Progress -Activity "Unzipping to $($UnzipPath)" -PercentComplete (($progress / ($ZipFiles.Count + 1)) * 100) -CurrentOperation $ZipFile.FullName -Status "File $($Progress) of $($ZipFiles.Count)"
    $ZipFolder = $Shell.NameSpace($ZipFile.fullname)
 
 
    $Location.Copyhere($ZipFolder.items(), 1040)
    $progress++
}

Function & SSIS Expression to Retrieve MonthNumber in YYYYMM Format

2017-10-19T01:50:00.004-07:00

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"
        )
    )

Use SQL to read XML data - Example

2017-03-20T03:50:00.001-07:00

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

Dynamic Management Views for SSAS

2016-03-07T09:05:00.000-08:00

Analysis Services (SSAS) Dynamic Management Views (DMV) are query structures that expose information about local server operations and server health. The query structure is an interface to schema row sets that return metadata and monitoring information about an Analysis Services instance.

SSAS DMVs can be used to monitor the server resources (e.g. connections, memory, CPU, users, aggregation etc. ) and find out the structure of SSAS databases (e.g. hierarchy , dimensions, hierarchies, measures, measure groups, data sources, cubes, actions and KPIs etc.) .

SQL statements can be used to query the row sets, but have following limitation in SQL 2008 version.

SELECT DISTINCT does not return DISTINCT values
ORDER BY clause accepts just one field to order by. Only one order expression is allowed for TOP Expression at line 1, column 1″
COUNT, SUM does not work
ORDER BY <number> does not ORDER, but no error
JOINS appear not to work
LIKE does not work
String functions like LEFT do not work

Usefull DMVs

SELECT * FROM $system.DBSCHEMA_CATALOGS -- list of the Analysis Services databases on the current connection.

SELECT * FROM $system.DBSCHEMA_COLUMNS

SELECT * FROM $system.DBSCHEMA_PROVIDER_TYPES

SELECT * FROM $system.DBSCHEMA_TABLES

SELECT * FROM $system.DISCOVER_SCHEMA_ROWSETS

SELECT * FROM $system.DISCOVER_COMMANDS

SELECT * FROM $system.DISCOVER_CONNECTIONS

SELECT * FROM $system.DISCOVER_JOBS

SELECT * FROM $system.DISCOVER_LOCKS

SELECT * FROM $system.DISCOVER_MEMORYUSAGE ORDER BY MemoryUsed DESC

SELECT * FROM $system.DISCOVER_OBJECT_ACTIVITY

SELECT * FROM $system.DISCOVER_OBJECT_MEMORY_USAGE

ORDER BY OBJECT_MEMORY_NONSHRINKABLE DESC

SELECT * FROM $system.DISCOVER_SESSIONS

SELECT * FROM $system.MDSCHEMA_CUBES

SELECT * FROM $system.MDSCHEMA_DIMENSIONS

SELECT * FROM $system.MDSCHEMA_FUNCTIONS

SELECT * FROM $system.MDSCHEMA_HIERARCHIES

SELECT * FROM $system.MDSCHEMA_INPUT_DATASOURCES

SELECT * FROM $system.MDSCHEMA_KPIS

SELECT * FROM $system.MDSCHEMA_LEVELS

SELECT * FROM $system.MDSCHEMA_MEASUREGROUP_DIMENSIONS

-- WHERE MEASUREGROUP_NAME = 'NSA NZ Sales'

SELECT * FROM $system.MDSCHEMA_MEASUREGROUPS

SELECT * FROM $system.MDSCHEMA_MEASURES

SELECT * FROM $system.MDSCHEMA_MEMBERS

SELECT * FROM $system.MDSCHEMA_PROPERTIES

SELECT * FROM $system.MDSCHEMA_SETS

Using DMV Queries to get Cube Metadata.

Referenceshttps://msdn.microsoft.com/en-us/library/hh230820(v=sql.110).aspxhttps://dwbi1.wordpress.com/2010/01/01/ssas-dmv-dynamic-management-view/
http://blogs.microsoft.co.il/yanivmor/2010/01/27/dmvs-for-analysis-services/
https://bennyaustin.wordpress.com/2011/03/01/ssas-dmv-queries-cube-metadata/

PowerShell - Get System information

2016-02-18T12:32:00.001-08:00

Get PowerShell Information
$Host

Get Computer System Information

# Useful Computer System Info.
Get-WmiObject -Class Win32_ComputerSystem -ComputerName . | `
    SELECT PSComputerName, BootupState, Status, Domain, UserName,  Manufacturer, `
    NumberOfLogicalProcessors, NumberOfProcessors, SystemType, `
    @{n="Memory(MB)";e={[math]::ROUND($_.TotalPhysicalMemory / ( 1024 * 1024) -as [Float],2)}} #,*

Get Operating System Information

# Useful Operating System Info.
Get-WmiObject -Class Win32_OperatingSystem -ComputerName DI21 | `
    Select-Object -Property PSComputerName, Caption,  CodeSet, CountryCode, 
    CreationClassName, CSCreationClassName, CSDVersion, CurrentTimeZone,  
    EncryptionLevel, ForegroundApplicationBoost,  InstallDate, LastBootUpTime, 
    LocalDateTime, Locale, Manufacturer, MaxNumberOfProcesses, MaxProcessMemorySize, 
    MUILanguages, NumberOfLicensedUsers, NumberOfProcesses, NumberOfUsers, 
    OperatingSystemSKU, OSArchitecture, OSLanguage, OSProductSuite, OSType, 
    ProductType, RegisteredUser, SerialNumber, ServicePackMajorVersion, 
    ServicePackMinorVersion, SizeStoredInPagingFiles,  SuiteMask, 
    SystemDirectory, Version, PSStatus, BuildNumber, BuildType,
    @{n="TotalVirtualMemorySize(MB)";e={[math]::ROUND( $_.TotalVirtualMemorySize / (1024*1024) -as [Float], 2)}}

Get Processes' Information

Clear-Host
Get-Process -ComputerName . | SELECT ProcessName, `
 @{n="WS(MB)";e={[math]::ROUND($_.WS / ( 1024 * 1024) -as [Float],0)}}, ` # WS,
 @{n="VM(MB)";e={[math]::ROUND($_.VM / ( 1024 * 1024) -as [Float],0)}}, ` # VM,
 @{n="PM(MB)";e={[math]::ROUND($_.PM / ( 1024 * 1024) -as [Float],0)}}, ` # PM,
 @{n="CPU";e={[math]::ROUND($_.CPU  -as [Float],0)}}, ` # CPU,
 Id, Path  | `
Sort-Object "WS(MB)" -Descending | `
Format-Table -AutoSize

Get Logical Disk Information

# Useful logical Disk Info.
Get-WmiObject -Class Win32_LogicalDisk -ComputerName DI21 | Select DeviceID,
    VolumeName, DriveType, ProviderName,FileSystem, Compressed,MediaTYpe,
    @{n="FreeSpaceGB";e={[math]::ROUND($_.FreeSpace / (1024*1024*1024) -as [Float],2)}},
    @{n="SizeGB";e={[math]::ROUND($_.Size / (1024*1024*1024) -as [Float],2)}}, 
    CreationClassName, VolumeDirty, Description, FilesystemSize, ErrorDescription

Get Installed Program Information

Get-WmiObject -Class Win32_Product -Computer . | ` 
    sort Name | Format-Table  Name, Version, Vendor

Get other system Information

Get-WmiObject -Class Win32_LogonSession -ComputerName DI21 

Get-WmiObject -Class Win32_LocalTime -ComputerName . 

Get-WmiObject -Class Win32_Service -ComputerName . | `
    Format-Table -Property Status,Name,DisplayName -AutoSize -Wrap

Get-CimInstance  Win32_Service
Get-CimInstance  Win32_Share
Get-CimInstance  Win32_ShareToDirectory
Get-CimInstance  Win32_StartupCommand
Get-CimInstance  Win32_SystemAccount | SELECT Caption, Name, 
    SID, Status| Format-Table -AutoSize -Wrap
Get-CimInstance  Win32_SystemDevices 
Get-CimInstance  Win32_SystemLoadOrderGroups
Get-CimInstance  Win32_SystemNetworkConnections
Get-CimInstance  Win32_SystemOperatingSystem
Get-CimInstance  Win32_SystemPartitions
Get-CimInstance  Win32_SystemServices
Get-CimInstance  Win32_SystemTimeZone
Get-CimInstance  Win32_SystemUsers
Get-CimInstance  Win32_UserAccount

Referance : https://msdn.microsoft.com/en-us/library/dn792258(v=vs.85).aspx

MDX - Parsing a value from one dim to another

2016-01-20T09:45:00.003-08:00

Following query pass year 2016 value from [Period] dimension to [Exchange Rate] Dim.

WITH
member YearX as
NONEMPTY(
[Period].[Operational Year - Semester - Quarter - Month].[Year].&[2016] ,
[Measures].[Targets - EdoxabanNetSalesTargets]).item(0).Properties("Key")

SELECT {[Measures].[Exchange Rate]}
on 0, non empty
(
[Exchange Rate].[From Currency].[From Currency]
, [Exchange Rate].[To Currency].[To Currency]
)
on 1 from [XXXX Cube]
where STRTOMEMBER( "[Exchange Rate].[Operational Year Key].[Operational Year Key].&[" +
YearX
+ "]")

Powershell, Infile search and file copy between the servers in same domain

2015-12-09T08:00:00.000-08:00

Find a keyword within multiple files in a folder.

$PATH = "D:\Veeva Activity\WE_00DA0000000Ci0mMAC_123\"
$FILES = Get-ChildItem -Path $path # -Name user.csv*

Foreach ($FILE IN $FILES )
{
Get-Content $PATH$FILE -First 1 | Where-Object { $_ -like '*lm_Presentation_Version_vod*' }

$FILE
}

File copy between the servers in the same domain
$SourcePath = "\\Server1\Data Input Area"
$DestinationPath = "\\Server2\Data Input Area"

$Folders = Get-ChildItem -Path $SourcePath -Name Aver*
FOREACH ($Folder in $Folders)
{
#$DestinationPath + "\"+ $Folder
#Get-ChildItem -Path $SourcePath\$Folder | Where-Object{!($_.PSIsContainer)}
Copy -Path $SourcePath\$Folder\* -Destination $DestinationPath\$Folder | Where-Object{!($_.PSIsContainer)} # Exclude Folders
}

Identify and disconnect / log off remote RDP sessions

2015-10-19T03:30:00.001-07:00

Run QWINSTA to extract the RDP session information

QWINSTA /SERVER:servername

If the session exists, read the username and session ID.

To disconnect user

TSDISCON /SERVER:servername sessionID

To log-off user / kill the session

RWINSTA /SERVER:servername sessionID

Ref : http://discoposse.com/2012/10/20/finding-rdp-sessions-on-servers-using-powershell/

Function to Get Folder Sizes in Powershell

2015-02-21T13:43:00.000-08:00

Following Powershell function can be used to get the sizes of given folders.
Folder names hould be given within double quotes seperated by commas.

 function Get-FolderSize {
    param(
        $FolderPaths = $null
        )
    [array]$FolderLists = $null
    foreach ( $FolderPath in $FolderPaths)
    {
        $FolderLists += 
        Get-ChildItem $FolderPath -Force -Recurse | 
        Measure-Object -property Length -Sum |
        select @{n="Path";e={"""" +$FolderPath + """"}} ,
            @{n="Size GB";e={[math]::ROUND($_.Sum / 1GB -as [Float],2)}} , 
            @{n="Size MB";e={[math]::ROUND($_.Sum / 1MB -as [Float],2)}} , 
            @{n="Size KB";e={[math]::ROUND($_.Sum / 1KB -as [Float],2)}}  
    }
    $FolderLists | Format-Table -AutoSize 
 }

The function rutuns the sizes of given folders in kilobyte (KB), megabyte (MB), gigabyte(GB).

Connect to a SFTP site using Windows Powershell

2015-01-31T07:19:00.000-08:00

This post explains how to access a SFTP server using Windows Powershell commands. There are many file transfer utilities which can be used for this purpose. Here, I use WinSCP in order to access SFTP server from windows powershell.

1. WinSCP PowerShell Wrapper can be downloaded from https://github.com/dotps1/WinSCP.
2. Import WinSCP Powershell module. $WinSCPFile path variable needs to be set to the WinSCP.psd1 file inside the downloaded folder.

$WinSCPFile = "C:\....\WinSCP-master\WinSCP.psd1"
Import-Module -Name $WinSCPFile -Verbose -ErrorAction Inquire -WarningAction Inquire | Out-null
3. Splat New-WinSCPSessionOptions.
$sessionOptions = @{
HostName = "SFTP-HostName"
UserName = "Username"
Password = "Password"
SshHostKeyFingerprint = "HostKey"
}

To get the SshHostKeyFingerprint, go to \WinSCP-master\NeededAssemblies\ folder and run WinSCP.exe.Then enter HostName, Username, & Password and try connecting to the SFTP site. You will get the SshHostKeyFingerprint when you log in for the first time.

4. Open new WinSCPSession using the splatted parameters.
$session = Open-WinSCPSession -SessionOptions (New-WinSCPSessionOptions @sessionOptions)

5. Send a file to SFTP server
Send-WinSCPItem -WinSCPSession $session -LocalPath "C:\localFile.txt" -RemotePath "./remoteDirectory/"

6. Get a file from FTP server
Receive-WinSCPItem -WinSCPSession $session -RemotePath "./remoteDirectory/rFile.txt" -LocalPath "C:\localFile.txt"
7. Close SFTP session
Close-WinSCPSession -WinSCPSession $session

Referance:
https://github.com/dotps1/WinSCP
http://winscp.net/eng/docs/ssh_verifying_the_host_key

Working with System date + SSAS + MDX

2014-12-31T14:42:00.002-08:00

The purpose of following MDX query is to create the CompleteDataMonthFlag flag which can be used to identify the latest month with complete data. For example, if we receive data with granularity less than a month (i.e. weekly or daily), we could use this flag to identify the latest month with complete data.

WITH
// Following set is to get the system year and month
SET [System Month - Operational] as
    StrToMember("[Period].[Operational Year - Quarter - Month].[Month].&[" + Format(now(), 'yyyy') + Format(now(), 'MM')+"]")
// Following set is to get the month with latest data
SET [Latest Data Month - Operational] AS Tail(Nonempty([Period].[Operational Year - Quarter - Month].[Month].members), 1)
//Member [Measures].[LatestDataMonth] AS LatestDataMonth.item(0).name
//Member [Measures].[CurrentMonth] AS CurrentMonth.item(0).name
SET LatestMonth AS
   CASE
   WHEN [Latest Data Month - Operational].item(0) = [System Month - Operational].item(0)
        THEN [Latest Data Month - Operational].item(0).lag(1)
WHEN [Latest Data Month - Operational].item(0) <> [System Month - Operational].item(0)
        THEN [Latest Data Month - Operational].item(0)
   END
MEMBER [Measures].[CompleteDataMonthFlag] as
   IIF ([Period].[Operational Year - Quarter - Month] is LatestMonth.Item(0),1, 0)

SELECT {
[Measures].[CompleteDataMonthFlag]
}
ON 0 ,
NON EMPTY
[Period].[Operational Year - Quarter - Month].[Month].members
ON 1
FROM [Cube Name];

IF the [CompleteDataMonthFlag] needs to be implemented within the SSAS cube as a calculated member, the following script can be used

Create Set CurrentCube.[System Month - Operational] AS
    StrToMember("[Period].[Operational Year - Quarter - Month].[Month].&[" + Format(now(), 'yyyy') + Format(now(), 'MM')+"]");
Create Set CurrentCube.[Latest Data Month - Operational] AS
    Tail(Nonempty([Period].[Operational Year - Quarter - Month].[Month].members), 1);
Create Set CurrentCube.LatestMonth AS
    CASE
    WHEN [Latest Data Month - Operational].item(0) = [System Month - Operational].item(0)
        THEN [Latest Data Month - Operational].item(0).lag(1)
    WHEN [Latest Data Month - Operational].item(0) <> [System Month - Operational].item(0)
        THEN [Latest Data Month - Operational].item(0)
    END;
CREATE MEMBER CURRENTCUBE.[Measures].[CompleteDataMonthFlag] as
    IIF ([Period].[Operational Year - Quarter - Month] is LatestMonth.Item(0),1, 0);

Ref:
http://sqlblog.com/blogs/mosha/archive/2007/05/23/how-to-get-the-today-s-date-in-mdx.aspx
http://sqljoe.wordpress.com/2011/07/22/dynamically-generate-current-year-month-or-date-member-with-mdx/

Backup and Restore Multiple Databases and Cubes

2014-12-01T07:30:00.001-08:00

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'

SSAS Scope function examples

2014-12-01T03:41:00.000-08:00

Define scope to a base measure.
SCOPE ([Measures].[Meetings]);
    this = ([Measures].[Meetings], [Contact Detail].[Event Type].&[Meeting]);
END SCOPE;

Creating a calculated member which use mutilple measures for different hierarchies.
Following calculated member can be used to define measures for different hierarchies. When [Target] measure is selected against [Territory].[Territory] hierarchy, it returns [Measures].[Territory Target].Otherwise it returns [Measures].[Area Target].

CREATE MEMBER CURRENTCUBE.[Measures].[Target]
    AS [Measures].[Area Target],
FORMAT_STRING = "#,#0",
NON_EMPTY_BEHAVIOR = [Measures].[Area Target],
VISIBLE = 1;
SCOPE ([Measures].[Target], [Territory].[Territory].[Territory].members);
    this = [Measures].[Territory Target];
    NON_EMPTY_BEHAVIOR(this) = [Measures].[Territory Target];
END SCOPE;

Create sub scope statementIn following calculated member, measure is limited to Efient products. (i.e defined in the sub scope statement). it returns null for other products.
CREATE MEMBER CURRENTCUBE.[Measures].[Stg - Retail Targets]
    AS NULL,
FORMAT_STRING = "#,#0",
VISIBLE = 1;
SCOPE ([Measures].[Stg - Retail Targets]);
    SCOPE( [Product].[Product].[EFIENT]);
    this = ([Measures].[Area Target] - [Measures].[Stg - SCM Hospital Targets]);
    END SCOPE;
END SCOPE;

Useful SSIS Expressions

2014-11-30T10:24:00.001-08:00

Search Expressions

If you want to find a specific text (e.g. 'Value') in a word ( underValue1), you could use following expression:
FINDSTRING(col, "value", 1) > 0
If you want to find a specific text (e.g. 'Value') at the beginning of a word (Value1), you could use following expression:
FINDSTRING(col, "value", 1) == 1
If you want to find a specific text (e.g. 'value') at the end of a word (underValue), you could use following expression:
REVERSE(LEFT(REVERSE(col), X)) == "Value"

Assign NULL values (tested in version 2012 )

(DT_STR, 4, 1252)NULL(DT_STR, 4, 1252)

Character replace expressionsUse hex notations to remove special characters in a fields of a Data Conversion transformation.

TRIM(REPLACE(ColumnName,"\x0009","")) - Remove horizontal
TRIM(REPLACE(ColumnName,"\x000D","")) - Remove carriage
TRIM(REPLACE(ColumnName,"\x000A","")) - Remove line feeds
TRIM(REPLACE(ColumnName,"\x00A0","")) - Remove Non breaking space (HTML tag - &nbsp)

Hex codes added in SSIS expression should have 6 characters that should start with "\x" and end with 4 characters. Therefore we might need to add extra 0s. (e.g to replace "&" we should use REPLACE(ColumnName,"\x0026","") )

Following websites can be used to find the hex code for known characters.
http://www.ascii-code.com/

SSAS [Period Utility] calculations + Accumulated (Addictive) figures

2014-11-30T09:33:00.000-08:00

The following calculated members return accumulated figures for a given measure and selected level in period dimension (you need to have a period (time) utility dimension in the cube in order to create this member).
YTD (Year To Date Calculation)
CREATE MEMBER CURRENTCUBE.[Period Utility].[Period Accumulations].[YTD (Operational)]
AS NULL,
FORMAT_STRING = "#,#.0",
VISIBLE = 1;
SCOPE ([Period Utility].[Period Accumulations].[YTD (Operational)]);
    this = SUM(
               PeriodsToDate(
                   [Period].[Operational Year - Quarter - Month].[Year],
                   [Period].[Operational Year - Quarter - Month].CurrentMember
               ), ([Measures].CurrentMember, [Period Utility].[Period Accumulations].DefaultMember)
           );
END SCOPE;   Following MDX can be used to validate figures.
SELECT
(
{[Measures].[Stg - Measure Name]} * {[Period Utility].[Period Accumulations].[YTD (Operational)]}
)
On Columns,
Non Empty {
[Period].[Operational Year - Quarter - Month].[Month].members
} On Rows
From [Cube Name]

QTD (Quater To Date Calculation)
CREATE MEMBER CURRENTCUBE.[Period Utility].[Period Accumulations].[QTD (Operational)]
AS NULL,
FORMAT_STRING = "#,#.0",
VISIBLE = 1;
SCOPE ([Period Utility].[Period Accumulations].[QTD (Operational)]);
    this = SUM(
               PeriodsToDate(
                   [Period].[Operational Year - Quarter - Month].[Quarter],
                   [Period].[Operational Year - Quarter - Month].CurrentMember
               ), ([Measures].CurrentMember, [Period Utility].[Period Accumulations].DefaultMember)
           );
END SCOPE;

Following MDX can be used to validate figures.
SELECT
(
{[Measures].[Stg - Measure Name]} * {[Period Utility].[Period Accumulations].[QTD (Operational)]}
)
On Columns,
Non Empty {
[Period].[Operational Year - Quarter - Month].[Month].members
} On Rows
From [Cube Name]

SSIS 2005 - Read data from an excel 2007 file and format it

2014-02-26T10:02:00.001-08:00

In order to read data from an Excel 2007 file, create an OLE DB connection and add file path and the file name as the server name.

Read data from [CountryOverview] sheet.
If you select "Table name or View name" as the data access mode, you could select the sheet name which you wish to read from the drop down menu. Instead of that, as you can see in the image below, a SQL query can be writted to read data from excel sheet.

SQL Server Agent Jobs to Backup / Restore SSAS Cube

2014-02-18T08:52:00.000-08:00

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

SSAS Local Cube

2014-01-22T08:58:00.000-08:00

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

SQLServer function to update the same table where cursor selects values

2013-10-24T07:51:00.001-07:00

--Check whether cursor is open

IF CURSOR_STATUS('global','c4') >= 0

BEGIN

CLOSE c4

DEALLOCATE c4

END

DECLARE @comparison VARCHAR(200)

DECLARE @source VARCHAR(200)

DECLARE @supplier VARCHAR(200)

DECLARE @itemname VARCHAR(200)

DECLARE @atc3names VARCHAR(max)

DECLARE c4 CURSOR FOR

SELECT DISTINCT

[Comparison NAME],

[Data Supplier NAME],

[Data Source NAME],

[Item NAME]

FROM dbo.[Data Validation Attribute Items] WHERE [Comparison NAME] = 'Product'

FOR UPDATE OF [ATC3]

OPEN c4

FETCH NEXT FROM c4 INTO @comparison, @supplier, @source, @itemname

WHILE @@FETCH_STATUS = 0

BEGIN

-- Combine multile rows in to a column seperated by a ';'

SET @atc3names = 'DECLARE @atc3names AS VARCHAR(200)

SELECT @atc3names = COALESCE( @atc3names + '';'', '''') + [ATC3 Name]

FROM

(

SELECT DISTINCT [ATC3 Name]

FROM dbo.[' + @source + ' Fact Data] a

INNER JOIN dbo.[Product Dim Product-Pack-Country] b

ON a.[Product-Pack-Country KEY] = b.[Product-Pack-Country KEY]

INNER JOIN dbo.[Product Dim Product-Country] c

ON b.[Product-Country KEY] = c.[Product-Country KEY]

INNER JOIN dbo.[Product Dim Product-Pack] f

ON b.[Product-Pack KEY] = f.[Product-Pack KEY]

INNER JOIN dbo.[Product] e ON c.[Product KEY] = e.[Product KEY]

INNER JOIN dbo.[ATC4] g ON f.[ATC4 KEY] = g.[ATC4 KEY]

INNER JOIN dbo.[PRODUCT DIM ATC4] h ON h.[ATC4 KEY] = g.[ATC4 KEY]

INNER JOIN dbo.[ATC3] i ON i.[ATC3 KEY] = h.[ATC3 KEY]

WHERE e.[Product NAME] = ''' + @itemname + '''

) A

UPDATE .dbo.[Data Validation Attribute Items]

SET [ATC3] = @atc3names

WHERE [Comparison NAME] = ''Product''

AND [Data Source NAME] = ''' + @source + '''

AND [Item NAME] = ''' + @itemname +''''

EXEC (@atc3names)

SET @atc3names = '';

FETCH NEXT FROM c4 INTO @comparison, @supplier, @source, @itemname

END

CLOSE c4

DEALLOCATE c4

Find an object in a SQL Server Instance (across databases)

2013-09-27T13:32:00.000-07:00

Following block generates a concatenated string for a set of selected databases defined in the sub query.
DECLARE @command VARCHAR(max) ;
SELECT @command = coalesce (@command + 'union' , ' ') +
'   SELECT ''' + [DBName] + '''AS DBName,
   [type_desc] AS ObjectType
   FROM ['+ [DBName] + '].sys.objects
   WHERE [name] = ''ATC4''
'
FROM (SELECT TOP 1000 name AS DBName FROM master.sys.sysdatabases) d
EXEC (@command)

sp_MSforeachdb is an undocumented stored procedure, which can be used to run sql statements across databases in a SQL Server instance.
DECLARE @command varchar(1000)
SELECT @command =
'    USE [?] ; SELECT
    DB_NAME() AS DBName, [type_desc] AS ObjectType
    FROM sys.objects
    WHERE name = ''ATC4''
'
EXEC sp_MSforeachdb @command ;

Following cursor can also be used to execute SQL Statements across databases in a SQL Server instance.
DECLARE @DB_name varchar(1000)
DECLARE cur CURSOR LOCAL for
    SELECT name FROM master.sys.databases
OPEN cur
FETCH NEXT FROM cur into @DB_name

WHILE @@FETCH_STATUS = 0
BEGIN
    EXEC ('SELECT
    DB_NAME() AS DBName, [type_desc] AS ObjectType
    FROM [' + @DB_name + '].sys.objects
    where name = ''ATC4''
    ')
    FETCH NEXT FROM cur INTO @DB_name
END
CLOSE cur
DEALLOCATE cur

Generate date range (Year and Month)

2013-07-16T08:48:00.000-07:00

Following T-SQL query can be used to generate Month Names and Year Names within a given period of time.

DECLARE
    @StartDate DATETIME,
    @EndDate   DATETIME;
--Define start date and end date
SELECT @StartDate = '2013-04-01', @EndDate = '2014-03-01';
WITH cteDates AS (
    SELECT TOP (
        DATEDIFF (mm, @StartDate, @EndDate) + 1)
        DATEADD( mm, DATEDIFF(mm,0, @StartDate ) + (ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) -1),0
    ) AS MonthDate
FROM sys.all_columns ac1
CROSS JOIN sys.all_columns ac2
)
SELECT
    YEAR(MonthDate) AS [Year],
    MONTH(MonthDate) AS [Month]
FROM cteDates

Ref:http://stackoverflow.com/questions/4181286/t-sql-select-get-all-months-within-a-range-of-years