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 πŸ™‚








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.








MDX Cheat Sheet

I’ve Googled for MDX Cheat Sheets, found a couple, but never exactly what I was looking for.
So I made my own, hope this one explains things a lot easier for you (and also as a reference for myself) πŸ˜‰

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

Dim Period Hierarchy

Code:

MDX Cheat Sheet

Results:

MDX Cheat Sheet results

Here’s a more graphical explanation on how things work:
SSAS Dimension hierarchies levels

Download it!
SSAS MDX Cheat Sheet


Here’s another MDX Cheat Sheet I posted after, all about Calculated Measures:
https://clinthuijbers.wordpress.com/2014/08/15/mdx-cheat-sheet-calculated-measures/








Yahoo! 24TB SSAS Cube – Big Data Case Study + Slides

Thanks Denny Glee for sharing this.

It’s old news, but still it’s very cool πŸ™‚
I always thought that Yahoo! had a 7TB SSAS Cube, but it has been growing of course.
As of dec 2012, it reached 24TB!!

Microsoft Case Study:
Yahoo! Improves Campaign Effectiveness, Boosts Ad Revenue with Big Data Solution

Some key numbers from this case study include:

24TB Analysis Services MOLAP cube
2PB source data of a 14PB Hadoop cluster
700M unique users, 47% of the global online population
3.5B ad impressions/day

Yahoo-24TB-Cube-Platform-Architecture

Yahoo-24TB-Cube-Querying-the-Platform-Architecture

More slides can be found here.

Source:
http://dennyglee.com/2012/12/08/yahoo-24tb-ssas-big-data-case-study-slides/








Predictive Analytics vs Data Mining

I just read a very good article about Predictive Analytics, source can be found here.

Predictive Analytics vs Data Mining

Technology Cycle:
Data warehousing is a mature technology, with approximately 70 percent of Forrester Research survey respondents indicating they have one in production. Data mining has endured significant consolidation of products since 2000, in spite of initial high-profile success stories, and has sought shelter in encapsulating its algorithms in the recommendation engines of marketing and campaign management software. Statistical inference has been transformed into predictive modelling. As we shall see, the emerging trend in predictive analytics has been enabled by the convergence of a variety of factors.

Technology Hierarchy:
In the technology hierarchy, data warehousing is generally considered an architecture for data management. Of course, when implemented, a data warehouse is a database providing information about (among many other things) what customers are buying or using which products or services and when and where are they doing so. Data mining is a process for knowledge discovery, primarily relying on generalizations of the “law of large numbers” and the principles of statistics applied to them. Predictive analytics emerges as an application that both builds on and delimits these two predecessor technologies, exploiting large volumes of data and forward-looking inference engines, by definition, providing predictions about diverse domains.

Methods:
The method of data warehousing is structured query language (SQL) and its various extensions. Data mining employs the “law of large numbers” and the principles of statistics and probability that address the issues around decision making in uncertainty. Predictive analytics carries forward the work of the two predecessor domains. Though not a silver bullet, better algorithms in operations research, risk minimization and parallel processing, when combined with hardware improvements and the lessons of usability testing, have resulted in successful new predictive applications emerging in the market. (Again, see Figure 1 on predictive analytics enabling technologies.) Widely diverging domains such as the behaviour of consumers, stocks and bonds, and fraud detection have been attacked with significant success by predictive analytics on a progressively incremental scale and scope. The work of the past decade in building the data warehouse and especially of its closely related techniques, particularly parallel processing, are key enabling factors. Statistical processing has been useful in data preparation, model construction and model validation. However, it is only with predictive analytics that the inference and knowledge are actually encoded into the model that, in turn, is encapsulated in a business application.

Definition
This results in the following definition of predictive analytics: Methods of directed and undirected knowledge discovery, relying on statistical algorithms, neural networks and optimization research to prescribe (recommend) and predict (future) actions based on discovering, verifying and applying patterns in data to predict the behavior of customers, products, services, market dynamics and other critical business transactions. In general, tools in predictive analytics employ methods to identify and relate independent and dependent variables – the independent variable being “responsible for” the dependent one and the way in which the variables “relate,” providing a pattern and a model for the behavior of the downstream variables.

In data warehousing, the analyst asks a question of the data set with a predefined set of conditions and qualifications, and a known output structure. The traditional data cube addresses: What customers are buying or using which product or service and when and where are they doing so? Typically, the question is represented in a piece of SQL against a relational database. The business insight needed to craft the question to be answered by the data warehouse remains hidden in a black box – the analyst’s head. Data mining gives us tools with which to engage in question formulation based primarily on the “law of large numbers” of classic statistics. Predictive analytics have introduced decision trees, neural networks and other pattern-matching algorithms constrained by data percolation. It is true that in doing so, technologies such as neural networks have themselves become a black box. However, neural networks and related technologies have enabled significant progress in automating, formulating and answering questions not previously envisioned. In science, such a practice is called “hypothesis formation,” where the hypothesis is treated as a question to be defined, validated and refuted or confirmed by the data.

More info about Data Mining?