SSAS: Slicing and dicing over data differences between SSAS databases via C# UDF and shell dimension

  • Article

 

Introduction and Problem

Sometimes we would like to compare cell values with the same multidimensional coordinates when the values are calculated over different SSAS databases.

The following scenarios can be considered (but not restricted to):

  • troubleshooting: consumers "feel that something is wrong with the data today but yesterday it was good"
  • helping to establish stable ALM (application lifecycle management): new system version values vs old system version values

In the scenarios, the common is that while we can have some considerations about that data seems to be incorrect while looking at aggregated values but it can be problematic to discover where exactly the problem is. 

The "slice and dice" OLAP databases process is exactly dedicated to discovering the source of data changes in this case so in the current article we consider an approach where values from different SSAS databases can be viewed in one cube. Special utility dimension (or "shell dimension" or "calculations dimension") with values

Current value

Previous value

Difference

is added to the cube in order to perform the comparison.

The approach is based on SSAS User Defined Function (UDF) with functionality similar to LookupCube SSAS MDX function with the difference that values are calculated over another database. The UDF is added to sample Analysis Services Stored Procedure Project (ASSP) assembly. In the article, the solution is implemented within sample Adventure Works 2014 Sample Databases.

The discovery of data differences yielded from changing a single row in one of the fact tables can be started with the screen like in the picture below while using the approach:

Solution

Transitioning to the solution can be depicted as in the picture below:

In the upper part of the picture, we can see a simplified scheme of an unmodified data query execution process. We pass coordinates and desired measurables to the request and receive corresponding measure values from the cube in each cell.

1. Additional dimension

In modified database below we:

 Add additional dimension to the database - utility dimension [Comparison Operations] with three calculated members:

  • [Comparison Operations].[All].[Previous Value]
  • [Comparison Operations].[All].[Current Value]
  • [Comparison Operations].[All].[Difference]

Definition of the  members is presented in the following snippet from the Default MDX Script:

/Comparison shell dimension operations
  
//use calculated members instead of scope to leverage FE cache
CREATE MEMBER CURRENTCUBE.[Comparison Operations].[Comparison Operations].[All].[Previous Value]
 AS [ASSP].GetDBVal("AdventureWorksDW2014Multidimensional-EE-Original"),
VISIBLE = 1  ; 
  
CREATE MEMBER CURRENTCUBE.[Comparison Operations].[Comparison Operations].[All].[Current Value]
 AS [Comparison Operations].[Comparison Operations].[All],
VISIBLE = 1  ; 
  
CREATE MEMBER CURRENTCUBE.[Comparison Operations].[Comparison Operations].[All].[Difference]
 AS IIF([Comparison Operations].[Comparison Operations].[All].[Current Value]=[Comparison Operations].[Comparison Operations].[All].[Previous Value],NULL,[Comparison Operations].[Comparison Operations].[All].[Current Value]-[Comparison Operations].[Comparison Operations].[All].[Previous Value]),
VISIBLE = 1  ;

2. Add members to query

When we add these members to the query as additional coordinates we split one cell with the measured value into three cells and each cell receives information about current coordinates and measure name (in the picture for simplicity we consider two cells).

2.1 For [Current Value] member we overwrite** [Comparison Operations]** coordinate to default member so the further calculation process returns unmodified measure.

2.2 For [Previous Value] member we call [ASSP].GetDBVal user defined function (SSAS UDF) which receives current coordinates (and measure name as a coordinate) internally via Context.CurrentCube.Dimensions collection and corresponding CurrentMember property of each dimension. Based on information about current coordinates we build corresponding MDX Query to retrieve the value from the previous database. As a starting point for the query construction code FindCurrentTuple() function was used.

Here we must have the same limitation (not checked) as for the FindCurrentMember() function:

public static object GetDBVal(string dbName)
 {
 object returnValue;
 string measuresText = "";
 string dimensionsText = "";
 Boolean addcomma = false;
 
 foreach (AMDS.Dimension d in Context.CurrentCube.Dimensions)
 {
 foreach (AMDS.Hierarchy h in d.AttributeHierarchies)
 {
 
 if (d.DimensionType == DimensionTypeEnum.Measure)
 {
 measuresText = h.CurrentMember.UniqueName;
 }
 else
 //eliminate comparison dimension from the query
 if ((h.CurrentMember.UniqueName != h.DefaultMember) && (!h.CurrentMember.UniqueName.Contains("[Comparison Operations]")))
 {
 if (addcomma == false)
 addcomma = true;
 else
 dimensionsText += ",";
 
 dimensionsText += h.CurrentMember.UniqueName;
 }
  
 }
 }
 //if all the dimensions in default members do not compose where clause of the query
 if(dimensionsText!="") dimensionsText = " where (" + dimensionsText + ")";
 
 string query=" select "+ measuresText+" on 0 from ["+Context.CurrentCube.Name+"] "+ dimensionsText;
 
 Microsoft.AnalysisServices.AdomdClient.AdomdConnection conn = new Microsoft.AnalysisServices.AdomdClient.AdomdConnection("Data Source=" + Context.Server.Name+";Catalog="+dbName);
 conn.Open();
 try
 {
 AMDC.CellSet queryCellset;
 Microsoft.AnalysisServices.AdomdClient.AdomdCommand queryCommand = new Microsoft.AnalysisServices.AdomdClient.AdomdCommand();
 queryCommand.CommandText = query;
 queryCommand.Connection = conn;
 queryCellset = queryCommand.ExecuteCellSet();
 returnValue = queryCellset[0].Value;
 }
 finally
 {
 conn.Close();
 }
 return returnValue;
 
 }

3. Explicitly we pass only previous database name to** [ASSP].GetDBVal** function - AdventureWorksDW2014Multidimensional-EE-Original. The name is used in connection string construction along with the server name. In this version of the solution both of the databases reside on the same current server.

Usage and Limits

Caution: This is a very first current implementation and is made from scratch. The previous version was pretty stable (and has worked for years) but is unavailable as for now. As an approach presentation and a solution starter, the current article can be useful.

Usage limits/practices:

  • Measures must present in the compared database. If you have the temporary calculated measure in the session there will be no data to compare and the comparison fails in this case?

Known Issues

  • Some calculated members have wrong values and probably can be solved in MDX.

To Do:

  • Reuse the same connection to all calculations in order to improve performance?

Steps to Reproduce

Below are approximate steps considered to reproduce the test configuration on a local SQL Server 2014 deployment with:

  • SSAS
  • Database Engine
  • Visual Studio 2013 SSDT-BI
1. Restore

From Adventure Works 2014 Sample Databases

  1. Restore AdventureWorksDW2014Multidimensional-EE.abf contained in Adventure Works Multidimensional Model SQL 2014 Full Database Backups.zip into SSAS database with name AdventureWorksDW2014Multidimensional-EE-Original. This will be the database with previous values.
  2. Restore AdventureWorksDW2014.bak contained in Adventure Works DW 2014 Full Database Backup.zip into SQL database with name AdventureWorksDW2014. This will be the data warehouse where we will change the value in some of the tables to get the difference to discover

Run the following script in SQL in order to make test changes:

UPDATE [dbo].[FactInternetSales]
SET UnitPrice = IIF(UnitPrice = 3578.27, 3579.27, 3578.27)
WHERE ProductKey = 310
 AND OrderDateKey = 20101229
 
SELECT UnitPrice
FROM [dbo].[FactInternetSales]
WHERE ProductKey = 310
 AND OrderDateKey = 20101229

Next run of the same script to return the database to initial state.

2. Get corresponding SSDT-BI project

Get corresponding SSDT-BI project from the article's GitHub repository and open the solution in Visual Studio:

Press F5 (or button with green triangle - Start). This will compile the projects, deploy databases with assemblies to the local SSAS server and process the database from the data warehouse.

In case of missing assembly references their locations can be determined using Google Chrome by navigating to C:/Windows/assembly/GAC_MSIL:

3. Open discovery start Excel file

Open discovery start Excel file - SSASDatabasesDataComparison.xlsx from the Solution Items folder or directly from GitHub to start the exploration of the differences. 

Resources

Product documentation

Sample projects

Community