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 

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

SSAS + Forecast calculations MDX

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

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

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

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

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.

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41

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

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

Get PowerShell Information
$Host

Get Computer System Information

1 2 3 4 5

# 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

1 2 3 4 5 6 7 8 9 10 11 12

# 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

1 2 3 4 5 6 7 8 9

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

1 2 3 4 5 6

# 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

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