Real Time ROLAP Setup in SSAS Enterprise Edition

If you need to access the underlying data in an SSAS measure group in Real Time with no latency (without any processing involved), proactive caching setting Real Time ROLAP has to be configured.  If you work on a typical Datawarehouse, this need is not normal. But if you use SSAS for real time calculations this setup will be useful.

Procedure:

1) Select the Storage Settings of the partition to be configured and select Real Time ROLAP.

2) Click on Options to launch the Storage Options window as below .

Note that the Latency is mentioned as 0 (zero). 

3) Go to the Notifications tab and select SQL Server (if the fact table resides in SQL Server)

You can select more than one tracking tables to watch for any updates. When the measure group is deployed SSAS watches for trace events raised against the specific tables. To do this, SSAS needs ALTER TRACE privileges in the underlying database (as mentioned in the bottom of the dialog box below)

How to check for Contiguous Dates

Let’s say we have an SCD 2 Dimension called Dimension.Store.  Lets assume the Business Key is BranchCode and the type II rows is versioned using RowStartDate & RowEndDate  columns.

How do we verify if the dates are contiguous with no gaps between different Type II Versions?

1) FromDate is not duplicated within the same Business Key

IF EXISTS
(SELECT BranchCode, [RowStartDate], COUNT(*)
FROM Dimension.Store
GROUP BY BranchCode, [RowStartDate]
HAVING COUNT(*) > 1)
SELECT ‘Error::Duplicate From Date Found for the same Branch’

2) FromDate should not be earlier than ToDate

IF EXISTS
(SELECT 1 FROM Dimension.Store WHERE RowStartDate > RowEndDate
)
SELECT ‘Error::To Date is Earlier Than From Date’

3) There should be no gaps Between EndDate of the previous Row and StartDate of the next row

SELECT BranchCode,
CASE WHEN DATEDIFF(DAY, MIN(RowStartDate),MAX(RowEndDate)) + 1 <> SUM(DATEDIFF(D, RowStartDate,RowEndDate) + 1) THEN ‘Bad::GapsInDateColumns’ ELSE ‘Good::NoGapsFoundInDateColumns’ END AS ErrorStatus
FROM Dimension.Store
GROUP BY BranchCode

Chart Design Guidelines

I have been reading about Information Design recently. After having looked at a few charts which provide no useful data / misleading data, I searched if there are any set of standard guidelines for this.

HICHERT+PARTNER is a company specialising in this area and they have a set of guidelines for Information Design. They call these guidelines – SUCCESS

S – Say
U – Unify
C – Condense
C – Check
E – Enable
S – Simply
S – Structure

This looks interesting !

http://www.hichert.com/en/success

MDX Studio – Tool to Debug MDX

MDX Studio is a great tool developed by Mosha to help in tuning and debugging MDX. This is a Windows application which one can install in a PC / Windows Server.

There is a cut-down version of the tool online as well.

Some of the Features:

a) MDX Execution related Performance monitor values such as Storage Engine (SE) Queries, Memory Usage, Cache hits etc

b) Suggest Best Practice MDX after analysing the MDX

c) MDX visualisation using a Trace Tree

There is a detailed talk done by Ashwani Roy, in the URL below.  There is a slide deck and a video you can download from here.

http://sqlbits.com/Sessions/Event6/Supercharge_MDX_performance_using_MDX_Studio

Regards

Jegan

 

SSAS Data Connection and Impersonation Info

When we create a datasource in SSAS, we need two types of connection information

Data Connection:

As the name goes, this information is used to connect to data source. This can be a standard User ID / Password combination (can be used for SQL Server, Oracle or any DB). If you connect to SQL Server, you can use Windows Authentication also (preferred method due to better security) for this.

Impersonation Information:

This specifies a Windows account used for SSAS Server management such as cube processing (create aggregations, SSAS indexes and write those into SSAS internal files such as *.lstore, *.asstore found in D:\Program Files\Microsoft SQL Server\MSAS10.SQL2008\OLAP\Data\<ProjectName>\ ). This cannot be a SQL account since OS tasks need to be achieved using this authentication.

Regards

Jegan

Open Source Web Pivot Table Control – CellSetGrid Download

Recently We were working on an SSAS Cube which needed direct end user web access using Pivot Table like functionality. We used CellSetGrid – an open Source ASP .Net Web Control. I have downloaded this control some time ago from www.SQLServerAnalysisServices.Com. This was developed by MS folks and hosted in the site.

When I checked if there is any new release of this control, I realized the source code is not hosted anymore in this site.This is because the domain name is acquired by another company to promote their products .

So I have uploaded the source of the control CellSetGrid here.

http://www.box.net/shared/6gi0n79q6t

-Jegan

SSIS ETL Framework

 

In my current job, we have a custom ETL framework to control the execution of SSIS packages.

Some of the features of our ETL framework is as follows:

a) Control the execution of packages using metadata tables

We use a manager package to call other packages which in turn call other packages as shown below.

ManagerPackage (Level 1)

ExtractManager (Level 2)

ExtractOne (Level 3)

ExtractTwo (Level 3)

DataLoadManager (Level 2)

TransformAndLoadOne (Level 3)

TransformAndLoadTwo (Level 3)

If we want to run a snapshot every day, week or month – we can set the frequency and the snapshot packages would run based on the settings.

b) Detailed logging of the execution times and success or failure status.

c) Rerun a component if it failed in the previous run

d) Ability to rerun Data Load for Snapshot tables using control tables

e) Audit information detailing how many records are loaded in every LoadRun.

When I googled to see if there are any free / open source frameworks available I found this.

http://ssisetlframework.codeplex.com/

This provides metadata tables, SSIS Packages (Manager and sample child packages), SSRS Reports to measure performance of the package execution.

- Jegan