Limitations / Disadvantages of using Calculated Measures / Calculated Members in SSAS

In my views, Designing is a process that is driven entirely by impact analysis. However trivial a product / tool / technology feature may be, if it's used without thoroughly analyzing the impact it can have on the overall system / solution that has to be developed, this would mean that you just signed up a guaranteed future roadblock.

Calculated measures / members seem like a very straightforward design feature, and whenever you feel need / shortage of some members / measures, calculated ones seems to be a very easily available option from a development perspective. But there is another side of the same, and these points should be kept in consideration before you make the decision of going with calculated members / measures.

1) Drillthrough does not operate on calculated measures or any other calculations that reference calculated measures / calculated members. This means, for example, if you have created a calculated measure on the cube which the user might opt to use as a part of drillthrough dataset, this means that now you are stuck and you need to find a workaround.

2) Calculated measures cannot be secured using Dimension Security in a straight forward manner, in fact they won't be listed at all in the Dimension tab of the role where we define the Dimension security. Also when security is applied on regular members, and due to the same, if they are not available to calculated members, they would fail i.e. when such measures are browsed in client tools like Excel, the value that would be displayed is an error value like #VALUE.

Based on the above two points, calculated measures / members should be scanned against drillthrough and security requirements, so that a trivial overlook in design doesn't translate into a showstopper issue over the period of time at a later stage.

MS BI POC and SQL Azure POC for better ROI on future projects

In my views, POCs ( Proof Of Concepts ) is a very versatile way of creating and promoting business, especially for vendors and solution providers. There was a very interesting article on POCs on BeyeNetwork some days back, that talked about how decisions based on limited scoped POCs can be catastrophic for an enterprise.

If POCs are carried out with a viewpoint of extracting results that can be translated into patterns / benchmarks that can catalyze agile and/or economic BI solution development methodology as well as provide avenues for appliance / product development, such POCs have a very high probability of an assured Return On Investment (ROI) against the investments done for conducting such POCs. For example, MDX is quite a complex query language, and developers need time and experience to master the same. If templates or CLR class libraries are created using ADOMD.Net and MDX, same can be reused via template explorer in SSMS and .Net application development, which can effectively boost up the development process and reduce the learning curve. This is very similar to DAX available in Powerpivot. Also if such libraries are developed with a broad vision in mind, it can be promoted as a off-the-shelf component. And I am sure that the ROI would be exponentially more compared to investments.

Below is a brief list of few such POCs, which has the potential to provide a very high ROI, provided it's carried out with a vision and target in mind.

1) SaaS solution development methodology using MS BI Stack and SQL Azure

2) Pattern development for synchronizing in-premise SQL Server and SQL Azure

3) Plugging / Integrating Cloud gateway tools at different phases of BI lifecycle

4) Economic BI solution development using SQL Azure / SQL Express , SSRS and Sharepoint Foundation 2010

5) MDX Script library in compiled / script format

6) Pattern development for economic cloud database (SQL Azure) consumption in BI and application development projects

7) Pattern development for Inter cloud integration and multi cloud incorporation into MS BI projects for provider-dependency free cloud consumption in MS BI projects.

I am not claiming that these are the best ones, but I can say that these are the best ones in my mind.

Cross Join in SSIS and SSAS / MDX : Creating a cartesian product

Recently Todd posted about Cross Join in SSIS, and he explained in great detail about how to do a cross join in SSIS, which can be read from here. It's important to understand the how part, but the "Why" part is also equally important in my views. If someone is able to answer how to do a cross join in SSIS, I would also like to know some real life scenarios where you would want to perform a cross-join using SSIS in a Data Flow. There are two real life scenarios where performing cross joins in SSIS would be beneficial, and of course, these are such scenarios where either you are not concerned about performance at all, or your intention is to test performance.

1) Populating Junk Dimensions: Junk dimensions are often formed with unrelated columns (i.e. attributes) in a single dimension table. Generally such tables contains very few records and can be easily populated using a script. But as a part of the policy, sometimes you may face situations where you need to populate Junk dimensions or even dimensions like "Date" using SSIS. In such situations you may find the need to use SSIS packages to populate such dimensions. Often such dimension populating is a one time process, and you would not use these packages often.

2) Performance testing SSIS packages: I personally feel that cross joining is a great way to generate huge test data with very less effort. Often when you need to test packages for performance or even other factors, the first thing that is required is test data. Say if I have 100 records in one data stream and 100 records in another data stream, by cross joining I can easily create 100 * 100 = 10000 records. This is some pretty quick test data !!

There can be many other scenarios, but these are the scenarios where I would use SSIS to create a cross join / Cartesian product, instead of using the database engine for the same purpose.

Cross Join is one such thing that is required in your MDX queries too. When you have two different hierarchies, for ex. say Products and Colors and you want to use it on the same axis i.e. you want a pivot kind of view where you want to see all products of all colors on the row axis / columns axis itself, you cannot achieve the same in MDX without cross joining these two different hierarchies on the same axis. This is one typical use where you would need to use Cross Join in SSAS / MDX.

In MDX, you can cross join different hierarchies by three different syntax or ways.

1) CrossJoin function: CrossJoin (Hierarchy1.level.Members , Hierarchy2.level.Members)

2) * operator: This is one of the shorthand for cross joining. Hierarchy1.level.Members * Hierarchy2.level.Members

3) () round brackets: To specify a set you would use curly braces {}. But if you intend to cross join, you can simply use the round brackets. (Hierarchy1.level.Members , Hierarchy2.level.Members).

Keep in view the I have included hierarchy.level.members for clarity. You can also just use hierarchy.level and that would bring all the members accordingly. In summary, in my views when you prepare for your interviews for questions like this, keep in mind that "why would you do something" is equally important to knowing "how would you do something".

MDX Coding Standards for non-spaghetti code

MDX is one such language that is not used as heavily as SQL, but coding standards or coding guidelines are one such aspect of development that needs to be applied to any programming language. Spaghetti code is one of the worst nightmares of any developers. Some of you might not know what does the term "spaghetti code" mean, though it a very popular term in the development parlance. In simple words, spaghetti code means a code that is like a crystal maze where it's quite complex to find the other end of the exit. In such code it's hard to figure out the tail of the code or the final result of the code when you start analyzing the code. Some key rules that I have learned from my experience to avoid such code getting created in MDX are as below:

1) Comment in detail about the query: When you use dimensions and measures in your MDX query, knowing the configuration and properties of MDX is quite important. It's always beneficial, if the intention of the query is made clear with a text explanation of the query logic in comments. This much it much easier to understand the query.

2) Try to make the code / query as explicit as possible: Many times the code becomes confusing due to the laziness observed while coding. For ex. one can omit specifying the measure to be used on any axis or on the slicer, if the measure to be used it the default measure. But consider that at some point in time the default measure of the cube needs to be changed, all the queries that has not been specified explicitly, the measure to be used, those queries would break.

3) Create a list of navigation shorthands: In MDX like in T-SQL, there are more than one ways of querying the same thing. For ex one can use brackets ( ), * operator as well as crossjoin function to achieve the same result. Another ex, one can use .parent.children function on a member and the same effect can be achieved by the .siblings function. In querying a cube, MDX helps how you navigate at a particular slice of the cube. One should make a list of common navigation functions that would be required in the code, and state down the shorthands to avoid creation of any spaghetti code.

4) Use functions instead of operators: In MDX, you can use * instead of crossjoin function, - instead of except function. Using text functions makes the code more readable and easy to understand instead of using the operators.

Above suggestions are based on my personal experiences and not any universal standard for MDX coding. One learns about the ways of avoiding spaghetti code only when one gets dumped into the spaghetti created by someone else. And fortunately or unfortunately this experience has made me conclude many rules some of which are mentioned above to avoid spaghetti coding.

MDX Interview Questions : How to prepare for SSAS MDX interview

As I serve / can serve on the interview panel for my employer, I cannot share any kind of interview questions, but this post is about how to prepare for an interview where you expect questions on Multi Dimensional Expressions (MDX). Below are certain points that should provide some hint / direction to keep in view, while preparing for your interview.

1) Work Profile: The depth and number of questions that you might face on the MDX section depends on the job profile for which you are appearing an interview. If you are appearing for a full time SSAS developer, expect the same level of brain storming on MDX as a T-SQL developer might face on T-SQL. If your interview is for a job profile of MS BI Tech Lead, MDX would be inevitably one of the sections. But that section might comprise of few complex queries to test whether you have ever worked on MDX. If you are a reports developer on a junior profile, you might skip away with questions on MDX, but if you are on a senior reports developer profile, you would still have to face MDX queries to a moderate level.

2) Cube Anatomy: If you do not understand cube anatomy, having knowledge of MDX would seem completely bookish to the interviewer. For ex. if you are not able to explain the difference between levels on attribute hierarchies and levels on user hierarchies, that would mean that though you have an idea of MDX but you do not understand how to navigate the cube using MDX. In T-SQL terms, this means that you know "select" command, but you do not know the difference between a view and a stored proc. In this case your knowledge on MDX would be rated on a scale of 1-3 on a scale of 10.

3) Operators and Functions: You should knowing the frequently used operators and functions in MDX. If you are poor at that, again it would project to the interviewer that you have bookish knowledge. No healthy querying can be done without operators and functions, and though not all, but if you do not have even an idea of even the frequently used MDX functions and operators for reporting purposes, your knowledge on MDX would be rated in a range of 3-5 on a scale of 10.

4) Effects of cube modeling: MDX is heavily influenced by the dimensional model of the cube. And many times by looking at the query results, one can tell the nature of the entity that is being used for retrieving the results or any issue with the dimensional model. For ex. if a measure is not associated with a dimension and you use them on two axes in your query, looking at the result, you can easily tell that there is a high probability that both are not associated. A clever interviewer would try this technique too to judge whether you have practically worked on MDX or you have just mugged up a few queries. If you perform well in this area, there is a high probability that you would score in a range of 5-8 on a scale of 10.

5) Interviewer's knowledge: Many a times it happens that interviewer might not be that good on MDX too. You need to score a good percentile, and not a good percentage. This means that you need to prove yourself competent enough to interviewer's knowledge. If you are just asked one question on MDX section like "Can you tell me a complex MDX query that you have used in your project or Can you tell me the syntax of MDX query", there is a high probability that the interviewer does not wish to screen you in detail on MDX or the interviewer himself / herself has limited knowledge on MDX. This means that you just hit a jackpot on your interview for the MDX section.

I hope this helps for all those who are scared of MDX. MDX is not at all similar to SQL, but also if you attempt to learn MDX without comparing it to SQL, MDX is a wonderful and a very powerful query language.

SSIS Books , SSAS Books , SSRS Books , MS BI Books ( My Recommendation )

I am a book-worm and most of the time I spend my leisure time reading books, but only technical and related to Business Intelligence. I have read a lot of books on MS BI and out of those, I would like to recommend a few of those books which I felt are upto my satisfaction. The books below that I am recommending are for all kind of audiences, whether you are novice or a pro, you would find something from the below list for yourself. Also these books covers topics like data warehousing, SSIS, SSAS, SSRS and MDX.

1) MDX Solutions: With Microsoft SQL Server Analysis Services 2005 and Hyperion Essbase (Paperback)

2) Professional Microsoft SQL Server Analysis Services 2008 with MDX (Wrox Programmer to Programmer) (Paperback)

3) Microsoft SQL Server 2008 MDX Step by Step (Step By Step (Microsoft)) (Paperback)

4) Professional SQL Server Analysis Services 2005 with MDX (Programmer to Programmer) (Paperback)

5) Delivering Business Intelligence with Microsoft SQL Server 2005: Utilize Microsoft's Data Warehousing, Mining & Reporting Tools to Provide Critical Intelligence to A (Paperback)

6) The MicrosoftData Warehouse Toolkit: With SQL Server2005 and the MicrosoftBusiness Intelligence Toolset (Paperback)

7) Foundations of SQL Server 2005 Business Intelligence (Paperback)

8) Microsoft SQL Server 2008 Analysis Services Unleashed (Paperback)

9) Expert Cube Development with Microsoft SQL Server 2008 Analysis Services (Paperback)

10) Professional Microsoft SQL Server 2008 Integration Services (Wrox Programmer to Programmer) (Paperback)

11) Professional SQL Server 2005 Reporting Services (Paperback)

MDX Debugging : OLAP PivotTable Extensions

In the spaceo of MDX debugging from the OLAP client side, one of the hidden gem (from my eyes) got discovered by me, when one of the reader of my blog posted a comment on my previous post. The this gem is knows as "OLAP Pivot Table Extensions".

Out of sone of the striking features of this add-inl, one is "If a PivotTable is performing poorly or returning incorrect numbers, it may be necessary for the Analysis Services administrator to troubleshoot the MDX query which the PivotTable is using. The MDX tab of the OLAP PivotTable Extensions dialog shows you this MDX". This add-in also has quite a number of limitations too and it has a great potential for improvement. But the value that it provides, that too for free is very similar to PTPower and interesting. It has some of the features which PTPower is missing.

I feel that PWPower + OLAP PivotTable Extensions used together, can make one's experience on debugging issues from the client end (i.e. excel as a client or consumer of Analysis Services) much warmer.

MDX Generator - PTPower Excel 2007 Add-In

One of the most fantastic add-in that I have seen in recent times, I am going to introduce it right now. I find it amazing due to the value it brings to MDX development. I have come across too many MDX aspirants who want to learn the same. I have found too less tools that can help in MDX debugging. This add-in has two great striking features, one is it comes for free and second is that it can extract MDX from aggregations. It's built upon ADOMD.Net that's a part of SQL Server 2008 feature pack.

PTPower is an Excel 2007 Add-In that lets you view the MDX and/or create Calculated Members for pivot tables based on Analysis Services (2005 or 2008) cubes. Thou I have not used/tested this add-in, but if it lives upto what it states, I give it a 5-star.

Reference: Andrew Fryer's Blog

MDX : MDX Tutorial , MDX Query , MDX Script , How to in MDX, MDX Select Query

I have seen developers searching for a last minute MDX query / script to create a calculated member on the cube. Many start in an organized fashion with a start from some simple MDX tutorial, and learning tuples, namedsets, axis, MDX Select Query Syntax etc... But still after that, many queries remain unsolved, and the real answer comes after a hands-on experience of some real-time project when one really starts applying functions, tweaking or nesting queries and parameters.

Any material found in MDX should be treasured. By far I have just seen 3-4 books on SSAS flavor of MDX. Below are the links to code-snippets which are FAQ's or used frequently while either querying the cube or creating calculated members on the cube.

Hope this helps all the last minute MDX requirements of those developers who don't have time to spend in learning all the syntax, structure and theory behing MDX syntax just to solve a little query or requirement on a cube in production or last-minute patch.

1. How do I find the bottom 10 customers with the lowest sales in 2003 that were not null?


2. MDX query to get sales by prod line for period plus number of months with NON EMPTY sales?


3. How do I group dimension members dynamically in MDX?


4. MDX-How do you write query that returns measure ratio to parent value?


5. In an MDX query how can I get the top 3 sales years based on the order quantity?


6. MDX-How can I compare members from different dimensions that have the same key values?


7. MDX - How do you extract first tuple from the set?


8. MDX - How do you extract first member from the tuple?


9. MDX - How do you compare dimension level name to specific value


10. MDX - How can I get attribute key with MDX


11. MDX - Query to get sales by product line for specific period plus number of months with sales


12. How do I create a Rolling 12 Months Accumulated Sum that can show a trend without seasonal variations


13. MDX - How can I setup default dimension member in cube calculation script?


14. MDX - How do you make calculated measure to be default cube measure


15. MDX - I would like to create calculated measure that instead of summing children amounts,uses last child


16. MDX query for the count of customers for whom the earliest sale in the selected time period (2002 and 2003) occurred in a particular Product Category


17. How to calculate YTD monthly average and compare it over several years for the same selected month

Download MDX Tutorial - Code Snippets of Various FAQ Queries (PDF)

1. How do I check for division by zero and null to avoid -1.#INF in cells


2. How do I check for the top level in a dimension to avoid division by an nonexsting parent(-1.#INF)


3. MDX - How can I use dimension members/measures in query when sometimes they might not exists

Download MDX Tutorial - Code Snippets of Error Handling (PDF)

1. MDX - How to use the Filter function in a SCOPE statement


2. MDX - How to create a SCOPE statement based on the Member property


3. MDX - How do you specify SCOPE For Dimension to include all members before certain member


4. MDX - How do you specify SCOPE For Dimension to include all members after certain member


5. MDX - How Can I define SCOPE statement for all measures in one measuregroup


6. MDX - How Can I define SCOPE statement for all but one measures in one measure group


7. MDX - How Can I define SCOPE in MDX for all measures in multiple measuregroups?

Download MDX Tutorial - Cope Snippets SCOPE statement (PDF)

1. MDX - How do you get Last month in the time dimension



2. MDX - Query example that returns list of months from start of year up to specified month



3. MDX - Query to show the first day of the last date month in the cube



4. How in the report can I order date dimension members in descending order?



5. MDX - query to get the last Month loaded into a cube



6. MDX - Query to get the first month of the last year loaded into a cube



7. MDX - How do you write query that uses execution date/time ( NOW() ) as a parameter?



8. MDX - Query to get latest months and previous years same months data



9. MDX - Query to show year level data for all years except last one, and month level data for last year



10. MDX - How to create calculated member for AVG sales over last 3 years based on NOW()?



11. MDX - How do I calculate sales for 12 Month to date?



12. MDX - How can I get Last (Previous) Year to Date (YTD) values?



13. MDX - Query to get count of months with sales amount > 0 in defined period



14. MDX - How do you calculate monthly average of one year, optionally including empty months?



15. MDX - How do you calculate monthly average of one year, optionally including empty months?

Download MDX Tutorial - Code Snippets for Time Dimension (PDF)

Content Host / Courtsey : SSAS-Info

Tool to profile MDX query processing and MDX Performance Analysis

All my career till date, most of the time I have been working on assignments where my primary role have remained of improvising performance, whether it may be application performance, query performance, database performance or system performance. A good debugging tool acts as a backbone while working on performance tuning and optimization. Industry class tools are already available for application in Visual Studio and for databases like SQL Profiler. But I have not seen a tool of the likes of SQL Profiler for MDX, at least in SSAS parlance.

I found an interesting tool on Codeplex that serves this purpose.

MDX Script Performance Analyser Overview (reference: Codeplex)

The MDX Script Performance Analyser allows the user to identify how much each calculated member, named set and scoped assignment in an Analysis Services cube's MDX script contributes to the overall performance of a given MDX query.

To use the tool, all you need to do is connect to the database and cube you'll be querying and then enter an MDX query in the main textbox and hit the 'Run Query' button. When you do this, the tool does the following:

1. Connects to the cube using ADOMD.Net and AMO
2. Read's the cube's MDX Script and splits it up into its constituent statements, storing them in an array
3. Executes a Clear Cache command to ensure that all queries are run on a cold cache
4. Executes a Clear Calculations command so that for the current session the cube appears as though its MDX Script contains no commands
5. For each statement in the array of statements from the MDX Script, it then:
   1. Executes the first statement in the MDX Script within the session, so that the cube now acts as though its MDX Script contains only this statement and all previously executed statements
   2. Runs the query you entered in the textbox
   3. Stores how long the query took to run, plus other interesting metrics
6. Once the query has run on the equivalent of the entire MDX Script in the cube, a report is generated which contains graphs and charts illustrating the data captured earlier

Download MDX Script Performance Analyser

MDX Tutorial : Generate MDX visually for SSAS using ProClarity

In OLAP / SSAS parlance, at least basic knowledge of MDX is a de-facto requirement. There are quite a few books published on SSAS flavor of MDX. It can be said that MDX is one of the best kept secrets in OLAP, as number of books and reference material available is scarce. Also understanding of Cube and developing the slicing query keeping N number of axis in mind is also not easy.

BIDS does a good job by providing MDX query editor with intellisense. Still my experience with Proclarity has been sweeter than with BIDS. Just try and use Proclarity Desktop Professional v6.3 for a visual experience of slicing and dicing Cube ( which is similar to Office Web Component in BIDS for browsing data ) and for generating MDX just by drag and drop.

Also Proclarity has some of the fascinating charts and bars which are still not available in Performancepoint Analytics module. Though Proclarity has been procured by Microsoft long back, but as per my views, it still stands taller where it comes to OLAP data analysis.

Visit the Tutorials link from the Community section for series of MDX articles and MDX tutorials on SSAS flavor of MDX.