SSAS DMVs for Monitoring & Querying Meta-Data

-- List of Dimensions.

SELECT

[CATALOG_NAME] AS Database,

[CUBE_NAME] AS [Cube],

[DIMENSION_NAME] AS [Dimension]

FROM

$SYSTEM.MDSCHEMA_DIMENSIONS

ORDER BY DIMENSION_NAME

-- List of Measures, along with MDX Formula for Calculated Measures.

SELECT

[CUBE_NAME] AS [Cube],

[MEASUREGROUP_NAME] AS [MeasueGroup],

[MEASURE_NAME] AS [Measure],

[EXPRESSION] AS [Calculation]

FROM

$SYSTEM.MDSCHEMA_MEASURES

ORDER BY

[MEASURE_NAME]

-- Dimension-MeasureGroup Relation.

SELECT

[CUBE_NAME] AS [Cube],

[MEASUREGROUP_NAME] AS MeasureGroup,

[DIMENSION_UNIQUE_NAME] AS Dimension_Real_Name,

[DIMENSION_IS_VISIBLE] AS Dimension_Visible,

[DIMENSION_GRANULARITY] AS Dimension_Granularity

FROM

$SYSTEM.MDSCHEMA_MEASUREGROUP_DIMENSIONS

ORDER BY

[DIMENSION_UNIQUE_NAME]

-- Current Sessions along with Resource Usage & Last Executed Command.

-- This can also be used to identify long running queries.

select * from $system.discover_sessions ORDER BY SESSION_LAST_COMMAND_ELAPSED_TIME_MS DESC

-- TO kill a long running query, note the SESSION_SPID from the above query and execute the following XMLA command

<Cancel xmlns="http://schemas.microsoft.com/analysisservices/2003/engine">

       <SPID>341157</SPID>

</Cancel>

-- Current Connections.

select * from $system.discover_connections

-- Current Commands. For Currently executing command, Command_End_Time will be blank.

select * from $system.discover_commands order by COMMAND_END_TIME

Ten Reasons to Use SSDT Database Projects.

1.       Schema Compare

Find differences between 2 databases or between database and database project.

In Visual Studio

Go to View -> SQL Server Object Explorer.

Add SQL Server.

Once Server is added, Select a database and choose Schema Compare.

2.     Create a database project. 

Right click on a database and select Create New Project. 

Once the project is created, it can be checked in source control tool (GITS or TFS).

3.       Automated Deployment.

The database project can be published to target database. 

Right click on the database project and select publish. This synchronizes the schema of target database with database project. Any existing data in the target database is retained.

5. Deployment Script Generation.

You can generate a deployment script and examine it before publishing to target database.

Right Click on Database Project, Select Publish and Generate Script. 

The script once generated can be deployed using SSMS.

The Advanced button gives you additional deployment options like Take Backup Before Publish etc.

6.  Snapshot & DACPAC.

 DACPAC is a single database file that contains entire database schema.  When you do not have access to production server, you can generate DACPAC and hand it over to DBA. The DACPAC can be deployed using SSMS "Upgrade Data Tier Application Task".

To create DACPAC right click database project and select "Snapshot Project". This creates DACPAC in snapshots folder of project.

When you do not have source control, DACPAC can be used to capture point in time snapshot or major/minor versions.

7.       Build Time Validation 

Visual studio includes a local debug database, when database project is built, it is deployed to local database, if there are errors (invalid table references for example), they will show up during build time as errors.

8. Automated Testing.

9. Continuous Database Integration.

Assigning SSIS Parent Package Variables to Child Packages in Project Deployment Mode

It’s very common in an ETL Process to pass variable values form parent package to child package. e.g Parent Package may generate Extract Window Begin, End Dates, ETL Execution ID etc. and pass it down to all child packages.

SSIS 2008 and earlier provided Package Configurations to set child package variables from parent package. In SSIS 2012 and later if you are using project deployment mode, you can’t use Package Configuration any more. The following are two ways of passing variables from parent package to child package.   

Execute Package Task Parameter Bindings.

Define a package level parameter in Child Package.

Use Execute Package Task Parameter Bindings in Master Package to bind master package variable to child package parameter.

The parameter in child package can be used directly or assigned to a child package variable and used. 

Using Script Task

The parent package variables are available to child package at run time only (not at design time). To use this behavior –

1. Define a variable in child package.

e.g. if Parent package variable is startDate. The child package variable can be cStartDate.

2. Create a script task in Child Package.

3. Configure parent package variable as read only in script task.

  

4. Inside the script task assign the parent package variable value to child package variable.

Dts.Variables["cStartDate"].Value = Dts.Variables["startDate"].Value;

The child package variable is now set with parent package value.

The disadvantage with this method is that child package cannot be run standalone (for trouble shooting, unit testing etc). The script task would have to be disabled to make the child package run when not called from parent.

SSIS Catalog Useful Queries.

Some useful queries for troubleshooting and monitoring performance of SSIS Packages running in Project Deployment Model.

-- Get All executions Packages.

SELECT * FROM catalog.executions AS E

-- Get Currently Executing Package.

SELECT * FROM catalog.executions AS E

WHERE E.end_time IS NULL

-- Get Error Messages from latest execution.

 SELECT  *

       FROM    SSISDB.catalog.event_messages em

       WHERE   em.operation_id = (SELECT MAX(execution_id) FROM SSISDB.catalog.executions)

           AND event_name LIKE '%OnError%'

-- Get Exeuction duration for each object

select  *

from catalog.executable_statistics

where execution_id = (SELECT MAX(execution_id) FROM SSISDB.catalog.executions) -- latest execution.

order by start_time

Using SSAS Formula Engine Cache with Non-Deterministic Functions

If you have complex MDX calculations which take long time to execute, you might be able to exploit formula engine (FE) cache and get good performance.

The following diagram from  “Microsoft SQL Server Analysis Services Multidimensional Performance and Operations Guide” gives overview of query processing.

If calculation results are available in formula engine cache, the values are returned immediately.

If calculation results are available in formula engine cache, the values are returned immediately.

The use on Non-Deterministic functions (e.g. Now() ),  causes FE cache to be not used. Instead, storage engine cache is used instead and calculations are re-evaluated.

As a work around, you can create a static named set using the Non-Deterministic function and use the named set in the calculation inside MDX script.

// Create Static Named Set.

CREATE SET CurrentCube.[Last Completed Month TY] as

(StrToMember("[Date of Service].[Calendar].[DateID].&[" + Format(DateAdd("d",-Day(Now()),Now()), "yyyyMMdd") + "]"));

// Use the named set in calculation.

CREATE MEMBER CURRENTCUBE.measures.[office visits new - ty]

 AS (

      [Measures].[Office Visits - New],

      [Date of Service Comparison].[Aggregation].[Year To Date] ,

      [Last Completed Month TY].Item(0)

),

VISIBLE = 1 ;  

The above calculation uses a date-tool dimension called “Date of Service Comparison” with a member “Year To Date”

// Using date-tool dimension.

SCOPE (

    [Date of Service].[Calendar].MEMBERS,

      [Date of Service].[DateID].MEMBERS );                                                      

   

    ///////////////////////////////////////////////////////////////////////////////////////

    [Date of Service Comparison].[Aggregation].[Year To Date]

          = Aggregate(

                     { [Date of Service Comparison].[Aggregation].DefaultMember } *

                       PeriodsToDate(

                                    [Date of Service].[Calendar].[Year Number],

                                    [Date of Service].[Calendar].CurrentMember

                     ) ); 

                    

END SCOPE;

Note: To use FE cache the calculations should be done in MDX Script in the cube, If the Calculations are done in MDX Query (using WITH Clause), they are re-evaluated every time.

To get the calculation results into FE cache, execute an MDX query which uses the calculation (after cube is processed).

SELECT measures.[My Complex Calculated Measure] FROM MYCUBE

T-SQL Date Calculations.

  -- Yesterday

  SELECT  CAST(GETDATE()-1 AS Date) AS Yesterday

 

  -- Beginning of Year

  SELECT DATEADD(yy, DATEDIFF(yy,0,getdate()), 0) AS StartOfYear

  -- End of Year

  SELECT CAST(DATEADD(s,-1,DATEADD(yy, DATEDIFF(yy,0,getdate())+1, 0)) AS Date) AS EndOfYear

 

  -- Beginning of Previous Year

  SELECT DATEADD(yy, DATEDIFF(yy,0,getdate())-1, 0) AS StartOfPreviousYear

  -- End of Previous Year

  SELECT CAST(DATEADD(s,-1,DATEADD(yy, DATEDIFF(yy,0,getdate()), 0)) AS Date) AS EndOfPreviousYear

  -- Beginning of Month

  SELECT DATEADD(mm, DATEDIFF(m,0,GETDATE()),0) StartOfMonth

  -- End of Month

  SELECT CAST(DATEADD(s,-1,DATEADD(mm, DATEDIFF(m,0,GETDATE())+1,0)) AS Date) EndOfMonth

 

  -- Beginning of Previous Month

  SELECT DATEADD(mm, DATEDIFF(m,0,GETDATE())-1,0) StartOfPreviousMonth

  -- End of Previous Month

  SELECT CAST(DATEADD(s,-1,DATEADD(mm, DATEDIFF(m,0,GETDATE()),0)) AS Date) EndOfPreviousMonth

 

  -- Rolling 12 Months. Begin.

  SELECT DATEADD(mm, DATEDIFF(m,0,GETDATE())-12,0) Rolling12MosBegin

  -- Rolling 12 Months End.

  SELECT CAST(DATEADD(s,-1,DATEADD(mm, DATEDIFF(m,0,GETDATE()),0)) AS Date) Rolling12MosEnd

-- Using SQL Server 2012 EOMONTH Function

-- Beginning of Year

select DATEADD(DAY,1,EOMONTH ( getdate(), - month(getdate()) )) StartOfYear

-- End of Year

select EOMONTH ( getdate(), 12 - month(getdate()) ) EndOfYear

-- Beginning of Previous Year

select DATEADD(DAY,1,EOMONTH ( getdate(), - month(getdate()) -12 )) StartOfPreviousYear

-- End of Previous Year

select EOMONTH ( getdate(),  - month(getdate()) ) EndOfPreviousYear

-- Beginning of Month

select DATEADD(DAY,1,EOMONTH ( getdate(), -1 )) StartOfMonth

-- End of Month

select EOMONTH ( getdate() ) EndOfMonth

-- Beginning of Previous Month

select DATEADD(DAY,1,EOMONTH ( getdate(), -2 )) StartOfPreviousMonth

-- End of Previous Month

select EOMONTH ( getdate(),-1 ) EndOfPreviousMonth

-- Rolling 12 Months Begin

select DATEADD(DAY,1,EOMONTH ( getdate(), -13 )) Rolling12MosBegin

-- Rolling 12 Months End

select EOMONTH ( getdate(),-1 ) Rolling12MosEnd

SSIS : Retry Extract on connection time.

Requirement: We pull AD Users through web service. The connection often times out. We would like to re-try connecting 3 times, if all three attempts fail continue with the ETL process without abend.

Send an email notification about the connection timeout.

Solution:

Part A

In the Extract Child Package -

 Create a for loop for data extract, which is initialized as follows –

Inside the for loop add on “On Success” and on “On Fail” script tasks apart from Data Flow Task which does the extract.

The “On Success” task sets vContinue variable to 0

The “On Fail” task increments vFail variable, if the loop fails three times, it sets vContinue variable to 0.

Go to On Error event handler for Data Flow task.

Create an empty On Error Event Handler.

Set Propagate system variable value to “False”.

Setting propagate to false will prevent  OnError from bubbling up to parent container.

Add a Send Mail Task with Precedence Constraint of @vFailCount>0 .

The solution so far will prevent the child package from failing and have it re-try 3 times before exiting.

The error will however still propagate up to parent package, to prevent parent package from failing follow the Part B of the solution.

Part B

In the parent package which calls this child package.

Go to the Execute Package Task for this Child Package.

Create an empty OnError Event handler and Set Propagate System variable to False.

End Result:

We have an extract package which connects to web service, if there are time outs it will retry 3 times before exiting. The extract package also sends email about timeout failure.
The extract package is called from a parent ETL package which ignores webservice connection failures and continues with other data load tasks.