Solved: No mapping between account names and security IDs was done (SSAS)

ssasmismatcherror

"Error 118 The following system error occurred: No mapping between account names and security IDs was done."

This error may look a little bit strange, but once you know what it’s complaining about it’s an easy fix.

Most likely you have a role defined in database that contains users or user groups not available in deployment environment. For example you have user group Domain1\UserGroup1 specified in role, but you are trying to deploy this database in environment where Domain1 is not available. To fix this error simply replace users or user groups with values that are correct in deployment domain.

And…we’re done πŸ™‚








Advertisements

SSAS: Color Expressions for your calculated measures (calculations) in your cube

It’s a typical thing on a Friday…being a bit distracted sometimes.
I was designing a new cube and suddenly noticed the ‘Color Expressions’ thingy at the bottom while creating a new calculation in the Calculation-tab in Visual Studio:

SSAS-cube-calculated-measures-calculations-color-expressions-in-visual-studio

Haven’t tested it with different versions of Excel, but I think this is general client behavior if I may call it that:

SSAS-cube-calculated-measures-calculations-color-expressions-result-excel

Could be very useful I think, will discuss this option with the business….one day…next year…or probably not πŸ™‚








How to design your SSRS or Power BI dashboard

Here’s how you should design your SSRS of Power BI dashboard (thanks to Julie Koesmarno).
More info on her blog:
http://www.mssqlgirl.com/power-bi-in-a-jiffy-composition-in-power-view.html


To my delight, she mentioned about composition, in particular Fibonacci Spiral. It reminded me of one of the fundamental things that I have learned in photography (as a hobby). So this weekend, I spent a bit of time reviewing some of the data visualisations in Power View that I have created in the past. Instead of just looking at it as just data and information, I put my β€œamateur” photographer eyes on this. I begin thinking about design, technical and most importantly business components and how to put them together.

Here are a couple of Before-And-Afters, where I have revisited the objectives and composition aspects of these data visualisations in Power View.

Before and After

Before-and-After-design-ssrs-power-bi-dashboards

Fibonacci spiral is a pretty cool thing to use / apply in composition. Use it wisely and when it works, it works really well. Not all visualisations have to fit Fibonacci spiral though πŸ™‚


Another example based on a photo:

how-it-is-done-in-photography

More tips for photography (and dashboards):
http://photo-typ.blogspot.nl/2013/08/golden-rule-of-thirds-and-fibonacci.html


Another interesting blog post by Jason Thomas (also HowTo’s):
http://www.sqljason.com/2013/05/a-sample-ssrs-dashboard-and-some-tips.html

Dashboard-design-nice








SSAS file extentions (exclude from anti-virusscanner)

Here’s a complete list of all the file extentions which SSAS uses. You may want to exclude these when you’re running an anti-virus scanner on your server:
http://phoebix.com/2013/07/23/excluding-analysis-services-files-from-anti-virus-scanning/


.ahstore
.asstore
.asstoreidx
.astore
.bin
.bsstore
.bstore
.cub
.data
.det
.dim
.ds
.dstore
.hdr
.hstore
.khstore
.ksstore
.kstore
.lstore
.map
.ostore
.prt
.sstore
.xml

A list of all the ‘Data Directories‘ can be found here:
http://technet.microsoft.com/en-us/library/cc281997.aspx








SSRS/MDX Parameter values based on a (filtered) dimension (which actually relates to records in the facts)

[Dim Period] dimension values:
For this demo, I have a dimension called [Dim Period] in my SSAS cube which looks like:

ssas-cube-dim-period-id-values


All values
So here’s how you could diplays all values (children) of a certain dimension:
ssas-cube-dim-period-id-values-all-code

ssas-cube-dim-period-id-values-all-results



Filtered by NOW()/FORMAT

([Dim Period].[Id].&[200701] : StrToMember("[Dim Period].[Id].&[" + Format(Now(), "yyyyMM") + "]")


ssas-cube-dim-period-id-values-filtered-by-now-code

ssas-cube-dim-period-id-values-filtered-by-now-results



Filtered by HAVING

HAVING NOT ISEMPTY([Measures].[--Value--])


ssas-cube-dim-period-id-values-filtered-by-having-code

ssas-cube-dim-period-id-values-filtered-by-now-results



Code
Here’s the MDX script I used:

--All members in the [Dim Period] dimension:
WITH
MEMBER [Measures].[ParameterCaption] AS [Dim Period].[Period Description].CURRENTMEMBER.MEMBER_CAPTION
MEMBER [Measures].[ParameterValue] AS [Dim Period].[Id].CURRENTMEMBER.UNIQUENAME
SELECT
{
[Measures].[ParameterCaption]
,[Measures].[ParameterValue]
} ON COLUMNS,
ORDER([Dim Period].[Id].CHILDREN,[Dim Period].[Id].CurrentMember.Member_Name,DESC) ON ROWS
FROM [CUBE]

--Filtered based on the current date:
WITH
MEMBER [Measures].[ParameterCaption] AS [Dim Period].[Period Description].CURRENTMEMBER.MEMBER_CAPTION
MEMBER [Measures].[ParameterValue] AS [Dim Period].[Id].CURRENTMEMBER.UNIQUENAME
SELECT
{
[Measures].[ParameterCaption]
,[Measures].[ParameterValue]
} ON COLUMNS,
ORDER([Dim Period].[Id].CHILDREN,[Dim Period].[Id].CurrentMember.Member_Name,DESC) ON ROWS
FROM
(
SELECT
--Here's a trick that will work on date/time values:
([Dim Period].[Id].&[200701] : StrToMember("[Dim Period].[Id].&[" + Format(Now(), "yyyyMM") + "]")
) ON COLUMNS
FROM [CUBE]
)

--But for other non time related dimensional members, you need to a different method:

WITH
MEMBER [Measures].[ParameterCaption] AS [Dim Period].[Period Description].CURRENTMEMBER.MEMBER_CAPTION
MEMBER [Measures].[ParameterValue] AS [Dim Period].[Id].CURRENTMEMBER.UNIQUENAME
SELECT
{
[Measures].[ParameterCaption]
,[Measures].[ParameterValue]
} ON COLUMNS,
ORDER([Dim Period].[Id].CHILDREN,[Dim Period].[Id].CurrentMember.Member_Name,DESC)
HAVING NOT ISEMPTY([Measures].[BS Balance])
ON ROWS
FROM [CUBE]








MDX Cheat Sheet – Calculated Measures

Here’s an addition to the MDX Cheat Sheet I posted earlier:
https://clinthuijbers.wordpress.com/2014/05/03/mdx-cheat-sheet/

But this one focusses on SSAS/MDX Calculated Measures (e.g. Full Year SUM() or YTD())

Hierarchy:
For the demo, I’m using a Year(2014)/Month(201401) hierarchy in my SSAS cube which looks like:

Dim Period Hierarchy

How it looks like:
MDX Cheat Sheet - Calculated Measures - MDXCode1
MDX Cheat Sheet - Calculated Measures - MDXCode2

Here are the results:
MDX Cheat Sheet - Calculated Measures

Download it!:
MDX Cheat Sheet (Calculated Measures)








SSAS Ragged Hierarchy (BottomUp approach)

Hi there reader!
There are a couple of methods on how to populate so called ‘ragged hierarchies’ in SSAS, a few tricks:

Naturalizing a Parent-Child Hierarchy:
http://martinmason.wordpress.com/2012/02/26/the-ssas-financial-cubepart-1ragged-hierarchies/

TopDown and hide middle levels:
http://asmdx.blogspot.nl/2008/04/parent-child-hierarchy-to-level-base.html

BottomUp approach by Chris Webb (Ragged Hierarchies, HideMemberIf and MDX Compatibility):
http://cwebbbi.wordpress.com/2009/11/11/ragged-hierarchies-hidememberif-and-mdx-compatibility/

I really liked the BottomUp approach that Chris Webb proposed. This is how a TopDown-hierarchy looks like:

SSAS-Ragged-Hierarchy-TopDown-Approach

And here’s the BottomUp-hierarchy we’re going to be using for this demonstration:

SSAS-Ragged-Hierarchy-BottomUp-Approach

First things first, here’s the cube’s structure:

SSAS-Ragged-Hierarchy-Cube

Define the relationships (nothing fancy here):
SSAS-Ragged-Hierarchy-Cube-Structure-Relationships

Here’s where the magic happens! Set the HideMemberIf-property to ‘OnlyChildWithParentName‘, so basically hide the child when it has the same name as its parent.

SSAS-Ragged-Hierarchy-Cube-Structure

And here’s the result of that hard work:

Dimension-Browse-BottomUp

Excel-Browse-BottomUp

Important note
This method works for Excel without setting the “MDX Compatibility = 2” requirement, but in SSRS and your MDX query it will only return those records which have no hidden levels. Strange issue, I suggest you make another seperate hierarchy for reporting that has the HideMemberIf property set to ‘Never’ for all levels.