Drillthrough aggregates

Until now I thought drillthroughs will return the single rows - but this is not the case. It groups by the Dimensionkeys. Well ok, but I had a design issue with that case:


We built up a cube with approximatly 1,000,000,000 records in the fact table. The business also wanted to have access to an fact attribute which is nearly the primary key of the facttable like a TransactionID. So there is no chance to load a dimension with this attribute. Our solution was to design this attribute as Non-Aggregatable measure in the cube. Worked wonderful. Due to the fact that Non-Aggregatable measures are only available at leaf level, this information was only available at Drillthrough.


BUT, some records were only distinguishable by this attribute - what did SSAS: it aggregates this attribute - very bad.
So first idea, Maybe a ROLAP Dimension helps, but at Drillthrough time, SSAS executes an SQL Query on the ROLAP dimension - so not working.


Our solution: create an surrogate key for the records which have the same Dimensionkey with:


ROW_NUMBER() OVER


(PARTITION BY <<DimensionKeys>> ORDER BY TransactionID)


Based on this attribute I was able to create a dimension with only a few records. Dimension is also hided and not used in my custom drillthroughs and, of course my Drillthrough resultset returns only non-aggregated rows.

Different Collations between Source and Cube

One of the most common scenarios I see is Oracle as a Source for SSAS Cubes.
When I started in my actual company and I looked at processing jobs I saw a lot of Errors which been ignored and/or mapped to Unknown member. I was just shocked that these looks like no problem in Production environment. So I asked whats happening, no problems?.
Well one reason was of different collations between source and cube like Case sensitive in the source and CI on the cube.
Many of the problems occure due to dirty data like Names which are loaded: one time starting with a uppercase, second time without uppercase. This happening due to missing Data cleansing process. Well these doesn't seem to be a problem directly. But in my cube I like to have it loaded without problem.
So what to do:

1) I changed the collation in my cube to Binary. So no more errors due to this point.
    The sorting from my point of view looks ugly like:
    AAA
    ABC
    AaA

   Also with this Sorting I get now two members instead of one-> increasing my Cubesize
   But this is a point which has to be decided by the business. "If u want an effective cube, watch your data".

2) Introduce a sort field for that attribute where I lowercase the complete name.

This is also one of the positive things of building up cubes: All the small dirty things u do or don't do comes to your eys :)

Custom Drillthrough on YTD Calculation

As everybody knows, a Drillthrough on Calculations is not possible. In my actual project we make some YtD Calculations. Now the business wanted in the Drillthrough to get all effected rows for that.
The solution is to build your own custom drillthrough based on the ASSP by making an own Drillthrough for each Period and then merging the resultset.

Step1: I'm using the built-in function of ASSP to capture my currentCellAtributes.

Step2: checking on which Level in my Datedimension the user is analysing. This is be done by checking which is the lowest level of the hierarchy with a selected member except the All-Member.
In the follwoing example the user is analysing on Month:

/*Declaring some variables*/
string monthuniquekey = "";
string monthkey = "";
List<string> months = new List<string>();

/*Getting UniqueKey of the Month member*/
monthuniquekey = currentCellAttribute.Substring(currentCellAttribute.IndexOf("[Period].[Month]"), 26);

/*Getting Key of the Month member*/
monthkey = monthuniquekey.Substring(monthuniquekey.IndexOf("&[") + 2, 6);

/*Getting the Year Key of the selected Month member*/
string year = monthkey.Substring(0, 4);
/*Building up a list with all months which are needed for Drillthrough*/for (int i = 1; i <= Int32.Parse(monthkey.Substring(4, 2)); i++)
{
  months.Add(year + i.ToString().PadLeft(2, '0'));
}
/*Iterate over all months in my list*/foreach (string month in months)
{
  string tempCellAttributes = currentCellAttribute;

/*Need to modify the CellAttributes for the Drillthrough command to replace the selected month by the actual from the iteration*/
  tempCellAttributes = tempCellAttributes.Replace(monthuniquekey, "[Period].[Month].&["+  month + "]");

/*Need to modify the CellAttributes for the Drillthrough command to replace the Cumulative type from YtD to Not Cumulated*/
  tempCellAttributes = tempCellAttributes.Replace("[Cumulation Type].[Cumulation Type].&[2]", "[Cumulation Type].[Cumulation Type].&[1]");

/*Fill a Temp Datatable with the result from the Drillthrough*/
  DataTable dttemp = General.ExecuteAccountDrillthrough("drillthrough select (" + tempCellAttributes + ") on 0 from [Cube] return " + sReturnColumns);

/*Finally merge the Temp Datatable with the Main Datatable*/
  dt.Merge(dttemp);
}

 Step3: Actually there is some "Bug" with the built in function "ExecuteDrillthrough". I always got errors when it comes to filling the Datatable with the measures value. Therefor I created a new Datatable and specify the datatypes of each column seperatly. Specially for the measures I need decimal datatype:

DataTable dtResult = new DataTable();dtResult.Columns.Add("Ultimo Volume on Balance - liability side", typeof(decimal));

TopCount ResultSet with always the same number of rows

Some days ago I had the request to create an MDX Query which should return the Top 10 Customers. Well, no big problem - just using the TopCount() function. But the requirement also includes, that there should be always 10 rows returned, nevertheless there can be 10 records delivered by the query or not.
For the example here I demostate it by using the AdventureWorks:


Resolution:

First, I created 10 Dummy Members:

MEMBER [Customer].[Customer Geography].[All Customers].[Placeholder1] as NULL
MEMBER [Customer].[Customer Geography].[All Customers].[Placeholder2] as NULL
MEMBER [Customer].[Customer Geography].[All Customers].[Placeholder3] as NULL
MEMBER [Customer].[Customer Geography].[All Customers].[Placeholder4] as NULL
MEMBER [Customer].[Customer Geography].[All Customers].[Placeholder5] as NULL
MEMBER [Customer].[Customer Geography].[All Customers].[Placeholder6] as NULL
MEMBER [Customer].[Customer Geography].[All Customers].[Placeholder7] as NULL
MEMBER [Customer].[Customer Geography].[All Customers].[Placeholder8] as NULL
MEMBER [Customer].[Customer Geography].[All Customers].[Placeholder9] as NULL
MEMBER [Customer].[Customer Geography].[All Customers].[Placeholder10] as NULL


Then a Set based on that Dummy Members

SET [Placeholder] as
{[Customer].[Customer Geography].[All Customers].[Placeholder1]
,[Customer].[Customer Geography].[All Customers].[Placeholder2]
,[Customer].[Customer Geography].[All Customers].[Placeholder3]
,[Customer].[Customer Geography].[All Customers].[Placeholder4]
,[Customer].[Customer Geography].[All Customers].[Placeholder5]
,[Customer].[Customer Geography].[All Customers].[Placeholder6]
,[Customer].[Customer Geography].[All Customers].[Placeholder7]
,[Customer].[Customer Geography].[All Customers].[Placeholder8]
,[Customer].[Customer Geography].[All Customers].[Placeholder9]
,[Customer].[Customer Geography].[All Customers].[Placeholder10]} 
Then a Set based for the TopCount

SET [Top10] as
TopCount([Customer].[Customer Geography].[All Customers].children
,10
,[Measures].[Internet Sales Amount]) 

Next I created a combined Set of my DummySet and may Top10 Set

SET [Top10PlaceholderCombined] as
UNION([Top10], [Placeholder])

Then I created a measure to get the rownumber of the combined set by using the Rank() Function:

MEMBER [Measures].[Row_Number] as
RANK([Customer].[Customer Geography].currentmember
,[Top10PlaceholderCombined])

Finally I come to my Select Statement where I just Filter() my set.

Select FILTER([Top10PlaceholderCombined], [Measures].[Row_Number]<11) on rows,
[Measures].[Internet Sales Amount] on columns
FROM [Adventure Works]

Result:

Custom Access for Reporting Users and Analysis Users

In SSAS your are very flexible to build a complex security implementation. You can also use dynamic security and AS Stored procedures to enhance the security concept.

Some time ago my employer raised the requirement for splitting the access to our cubes in users which are allowed to analyse the cube and users which are only allowed to run Standard reports against the cube. And of course a user could be a Reporter for Cube 1 but an Analyser for Cube 2.

 
My first answer was:  If a user is allowed on the cube, he is allowed.
The answer of my employer was: not acceptable. –puuh
So I went for holidays  and thought about it while I was lying on the beach

In our infrastructure we use a Webportal for our Standard reports and Excel for analysing (in detail: XLCubed).

Users can only access over the Datapump to our cubes. So there is no direct access possible.

My first approach was to build up a service, like profiler, checking all connections and if it was established by a user which is not allowed – cancel it. Unfortunately the response of the query was faster then my evaluation of the connection.

My second approach was to build up a custom module and listen on the traffic and modifying the response stream based on the rights. Unfortunately the stream is encrypted. Details can be found here http://sqlblog.com/blogs/mosha/archive/2005/12/02/analysis-services-2005-protocol-xmla-over-tcp-ip.aspx

So coming to my last approach: SSAS Proxy. The basic  code can be found here https://code.msdn.microsoft.com/office/SSASProxy-Custom-HTTP-7db6d016/

I created 2 roles on the cube (Analyser and Reporter). Both roles uses dynamic security with factless facttables. In the Analyser role every user with appropriate rights is added over an own Active Directory Group. This role uses the MDX Function Username() for the security.

In the second role only one system user is added. The security implementation is the same except the MDX Function. Here I use CustomData().

Until now only the Analysing users can access the cube.

Next step is implementing the custom handler on the IIS. Just saying the all calls to eg. .cube extensions should be handled by my customized SSAS Proxy.

In my proxy I just impersonate the actual user to the system user which has access to the cube over the Reporting role.

Finally: the Webportal has the ability of some connectionmappings. So the Analyser designs a report, mapping to the basic datapump and upload this report. In the Webportal this report is automatically mapped to my custom handler. If a user downloads the excel he get the connection to the basic datapump again and if this user is just a reporter he won’t get access to the cube.