Posts Tagged ‘SSAS’

Perculiar Named Calculations

In regards to this article about named calculations in SSAS, I had the perculiar finding today, that NULLs are handled somewhat differently than I would have expected.
In my case I had constructed a named calculation, just as in the article, by concatenating two columns, both of type string and allowing NULLs.
The perculiar part was, that when the second value was NULL, the whole expression would result in a NULL.
So, in order for the concatenation to succeed, the expression should be as follows:

[Marital Status] + CASE WHEN LEN([Has Children]) > 0 THEN  ‘ ‘ + [Has Children] ELSE ” END


How to setup basic dimension security in SSAS

SQL Server Analysis Services offers the possibility to create roles, whereby you can manage the access certain users will have to data in the cube(s) on the database. The following is a basic tutorial on how to create a role Limited Access, used to limit the access to the Product dimension of the Adventure Works cube.

First we right-click the Roles collection on the database and select New Role:

Then we give the role a meaningfull name and perhaps a description to distinguish it even more:

Next we add the members of the role, eg. the users:

We can add as many users as we like, even groups to let he IT Administration handle the permissions:

We then need to decide which cube(s) the role is granted access to:

We jump a few steps here in order to get to the basic part of setting dimension security in SSAS 20008.
Select the Dimension Data tab, and select which dimension(s) the role is to be restricted on:

This particullar role is limited to seeing only members Accessories and Bikes, hence Clothing and Components are prohibited:

In order for us to test this newly created role, we can select to browse the cube using the credentials of the role.
This is done in the browse cube dialog:

We then select our new role:

And voilà, the permissions show up as expected:

For more advanced stuff on security in cubes, please refer to:

How to get SSAS to return the correct currency format.

Although setting the language and collation correctly, it’s not easy to see why a specific MDX would not return the right formatting, in regards to monetary, thousands seperator etc.
There is however a way of forcing the language, through a property available on the calculated measure. In the following example, I’ll show that even though the server settings are da-DK (Danish), the results are displayed as en-US.

First, a screenshot of the server settings:

Then the query, that returns en-US formatting:

For this to work, we need to add: Language=1030 as property to the calculated member, then we get:


MDX Query using other hierarchy in same dimension as in slicer

The other day a coworker of mine presented me with a problem in one of our solutions. His basic pain was, that the front-end of the application only allowed for one dropdown containing whatever dimension was set up to be displayed. This dimension is to act as slicer in a scorecard application. Since his KPIs were aiming different levels of different hierarchies in the date dimension, he was experiencing problems when faced with the choice of only one available date hierarchy.

Luckily we are able to solve this issue by means of MDX.
The following statement should display the solution, by the use of the method EXISTS and the knowledge of the structure of the date dimension hierarchies.

	MEMBER [Measures].[Test] AS 
		( EXISTS([Date].[Calendar Week].Members, [Date].[Calendar].CurrentMember).Item(1), [Measures].[Internet Sales Amount])
	{[Test]} ON COLUMNS
FROM [Adventure Works]
WHERE { [Date].[Calendar].[Date].&[20070201] }

We use the EXISTS function to find the members of the [Date].[Calendar Week] hierarchy with the slicer set to  a member of another hierarchy, in this case [Date].[Calendar].[Date].&[20070201]. Note that we need to get Item(1) of the collection returned, as we get the All member at first index (0).


As mentioned by Hrvoje Piasevoli (blog|twitter) it is a more clean solution to just query by the tuple made by the week hierarchy and the measure, eg.:

([Date].[Calendar Week].CurrrenMember, [Measures].[Internet Sales Amount])

In our case however, we discovered that the attribute relations in the Date dimension was not setup correctly to allow us to “convert” a member in one hierarchy to another. We ended up with the [All] member of that hierarchy.
We solved the issue by making a common root for the two date hierarchies, as seen in the Adventure Works cube.

Setting the maximum allowed memory consumption by Analysis Services

Here follows the step to find where to change the settings for LowMemoryLimit, TotalMemoryLimit and other memory related properties of the SQL Server Analysis Services instance.

Fire up your SQL Server Management Studio, and right-click the server and select ‘properties’

Then check the ‘Show Advanced (All) Properties’ and you’ll see al available properties, like this example:

If values listed are between 0 and 100, they are treated as a percentage of the physical memory on the server. Otherwise they are treated as bytes.

AMO DimensionPermission laver unoder under deploy

Stødte på et underligt scenarie, jeg mener jeg vil gemme og dele med andre der måske er lige så uheldige at rende ind i det.
Ved at fjerne alle dependencies på en rolle i SSAS 2008 gennem AMO, bliver det umuligt at sætte nye dependencies og den lidt mere mystiske, at deploye en ny (den samme) database fra Visual Studio.
Workaround er at omdøbe kuben på SSAS instansen, for derefter at deploye igen. Voila.
Deploy overskriver den nyligt omdøbte database, fikser de fejl der måtte være og re-omdøber igen databasen.

Man kunne interessere sig for et trace på hvad der sker under omdøbningen!?

Julian Date og supplementsmånede

Jeg sad og skimmede nettet for smarte tricks til en tidsdimension i et datawarehouse.
Jeg faldt over følgende indlæg: og fandt det egentlig meget interessant, lige indtil jeg stødte ind i kravet: supplementsmånede. Jeg kan ikke rigtig se, at man kan løse dette krav med en konvertering af date til julian hvor man samtidig håndterer den 13. månede eller?

Connectionstring til SSAS 2000 & 2005

Da jeg læste denne post, faldt jeg over linket til siden der beskriver alle properties i connectionstring til Analysis Services 2000 & 2005. Den gemmer vi lige 😉
edit: Det er ikke alle, men mange af de properties der findes der beskrives.

Pct af total

Dette MDX eksempel viser hvordan man kan beregne den aktuelle pct af total salget, og pct af total salget for niveauet over det aktuelle.

 MEMBER [Measures].[Pct of total] AS ' 
     SUM( {[Product].CurrentMember}, [Measures].[Unit Sales]) / 
     SUM( {[Product].DefaultMember}, [Measures].[Unit Sales])', 
 MEMBER [Measures].[Pct of parent total] AS ' 
     IIF( [Product].Parent  NULL, 
     SUM( {[Product].CurrentMember}, [Measures].[Unit Sales]) / 
     SUM( {[Product].Parent.Children}, [Measures].[Unit Sales]), 1)', 
 { [Measures].[Unit Sales], 
    [Measures].[Pct of Total], 
    [Measures].[Pct of parent total] } ON COLUMNS, 
    Order( DESCENDANTS( [Product]  ), 
    [Measures].[Pct of total], DESC ) ON ROWS 

Den absolutte værdi

Her er et eksempel på hvordan man beregner den absolutte værdi af et givent Measure.

Code Block

 MEMBER [Measures].[Neg Profit] AS ‘-1 * [Measures].[Profit]’
 MEMBER [Measures].[Abs Profit] AS ‘ABS([Measures].[Neg Profit])’
 {[Measures].[Neg Profit], [Measures].[Abs Profit]} ON COLUMNS,