SSAS tools: mdx studio

So many good tools out there, and yet not all of them get noticed…..

At the SQL Pass conference the week before, I attended one of the classes by the Microsoft CSS groups on common Analysis Services support issues.  While not the best presentation format or speaking, the information and thought processes were very relevant and thought out, so all in all – good session. 

What surprised me was how few of the attendees were familiar with Mosha’s MDX Studio that the Microsoft speaker presented as a resource – so in the nature of spreading the good word, here is the link:http://www.mdxstudio.com/

What is it? From Mosha’s site:

MDX Studio is tool which helps users of Microsoft Analysis Services to analyze complex MDX expressions, monitor performance characteristics of MDX queries and get insight into how MDX interacts with other UDM features such as attribute relationships. MDX Studio provides unique visualization representation of MDX expression and allows the user to interact with different stages of MDX execution.

November 17, 2009 16:39 by Sid
E-mail | Permalink | Comments (4) | Comment RSS

Awesome speaker for our next BI User group!!

I am excited to announce that we have secured a great speaker for our next Central Texas Business Intelligence User Group meeting on October 21st at the Microsoft office in Austin.

Bryan C. Smith, prolific SSAS author and now of Microsoft fame, will be coming down to speak on a yet to be disclosed topic on SQL Server Analysis Services.

If you have not done so, and you live/work in the area, be sure to join our group on LinkedIn or send me an e-mail and I will add you to our distribution list – this is a speaker you do not want to miss (if you love all things SSAS).

On a broader note, we will be upping the frequency of the meetings to monthly after the October meeting.

In that vein, we are looking for some more presenters (Larry Clark (MS) and I love presenting, but I think the group would like to see some new faces). Please e-mail me or post to the LinkedIn group board if you are interested.

Hope to see some new faces there!

September 14, 2009 16:45 by Sid
E-mail | Permalink | Comments (9) | Comment RSS

Design Tip - Analysis Services Aggregations

I was cleaning up a cube design for a customer, primarily working with the current implementation to resolve some performance issues and make some minor alterations to the dimensions, when I ran across an aggravation in designing aggregations for the partitions I implemented ( there were none before, part of the issue).

I had just finished designing my partitions when I started to design the aggregations for my partitions – when the designer returned this obnoxious message:

    0 aggregations have been designed. The optimization level is 0% (0 bytes).

I will mention again that this cube was not my design, and when given the opportunity, everything from the dimensional model to the entire cube will be redesigned, however, this message was a nuisance. Fortunately, I kept my cool and figured out what I was doing wrong. The Aggregation Design Wizard will not allow a resulting aggregation set to exceed a certain percentage of the fact table (I hear it is 30%, but I can't find this documented anywhere).

Here are a few general rules with aggregations that will help avoid this message:

1. Make sure the partitions you are trying to assign an aggregation design are evenly distributed. In other words, do they have a similar number of facts/rows in them or are they varied? One aggregation design can work for multiple partitions so long as those partitions are similar or close in overall row counts
2. If you have a large number of attributes in your dimensions related to a particular partition set you are designing for, you may consider changing some of the options on the Review Aggregation Usage screen in the wizard. Attributes that are rarely used can potentially be set to None
3. On the Specify Object Counts page, check the counts tabulated for each dimension as well as the measure group. On the dimensions, the wizard does not calculate the partition count. This number is the count of that member distinctly appearing inside the specific partition. For example, if your partition broken by year, then the count of dates on your Calendar dimension would be 365, months would be 12 and so on.

In addition to the above, take a look at your individual partitions -- are the individual partitions too large? BIDS Helper suggests a 20 million plus row count per partition is probably not partition with enough detail.

As for my specific problem – due to the "funky" (lack of a better word) design of the fact tables, I did not have reasonable splits in both my row counts and related member counts for my initial partition designs as I had initially suspected, and being in a hurry that evening, I did not update the counts inside the partition design screen before moving over to designing aggregations. I simply assumed I had picked a good way of doing it on the first try!

So, a lesson in patience reiterated yet again, and after getting some sleep, I looked at the issue the next morning with some fresh eyes and found my solution.

Hopefully this little explanation will save some time for someone out there – feel free to let me know if any further explanations are necessary, or feel free to share your aggregation design issue!

August 17, 2009 12:17 by Sid
E-mail | Permalink | Comments (17) | Comment RSS

Calculated Measures/Members and solve order

It has been a terribly busy month – I actually started writing this post at the end of January, but only now am I able to finish it and fire it off to my blog. Feel free to post or e-mail me with any follow up questions

I ran across an issue at one client where a calculated measure was not behaving as desired (though it was behaving as designed!). Since this is a topic not likely to be broached in bar conversation or in the break room over hot tea, I wanted to cover a few cogent points here that might be useful to others.

Though several items were at the root of the measure not being calculated as expected, the issue that is common to anyone using calculated members is a misunderstanding of calculation passes and solve order (more here on MSDN). The MSDN article does a great job of showing calculated members in an MDX query, using solve order and the different result sets you will get with differing solve order sets, however the article feels a little dearth in its explanation of calculation passes and why solve order sometimes is necessary for correct calculations in your queries.

Analysis Services loads data into the cube in passes – on the first pass (pass 0) leaf members are loaded from fact data, the second pass (pass 0.5) loads the values of cells associated with sum, count, min, max, distinct and distinct count aggregate functions. After this is completed, then the load process can start on the cube calculations represented in the calculations tab. This is where, though intuitively you may recognize this, it is important to understand how calculation passes and solve order come into play.

For this example, I will reference the ubiquitous AdventureWorks example, now available via CodePlex:

In this cube, the calculated member [Total Sales Ratio to All Products] has a calculation reference to [Total Sales Amount]. Calculation pass comes into play here via the order of the calculated sets in the script. Notice that [Total Sales Amount] is at line 2 and [Total Sales Ratio to All Products] – just via placement in the calculation pane, you are declaring the pass order for these cube calculations. This is easy to see in this example, however on the customer that was experiencing a calculation problem, they literally had hundreds of cube calculations, with multiple developers having touched the product, so the last person in to work on new calculations and tweak old ones had not noticed the order of referenced calculations and thus had a hard time finding where the calc had gone wrong.

It is also important to remember that the last, or highest pass trumps previous passes when it comes to calculations. In the AdventureWorks cube, if we added another measure at line three that named [Total Sales Amount Revised], with the expression = [Total Sales Amount], then provided nothing else changed, we would then have two measures that had the same value. If we add to the script down at the last line that [Total Sales Amount] equals 20, then both [Total Sales Amount] and [Total Sales Amount Revised] would now equal 20 as the last pass wins in assigning values to the calculated members.

This is where FREEZE comes into play – in the scenario above, we can say: FREEZE([Total Sales Amount Revised]) on the line right after we set [Total Sales Amount Revised]'s value, and then when [Total Sales Amount] gets reset at the end of the script, the [Total Sales Amount Revised] will retain its values as calculated in the earlier pass. This can be incredibly useful for using role playing calculations in financial scenarios (which was part of my client's mission).

Solve order then, is used when you are writing out your query or sub-cube in MDX and need to replicate the calculation passes that the Calculations Tab manages in Visual Studio. You do not normally use solve_order in calculations in the cube designer, however it can be used to give Analysis Services your desired calculation path if in one script block on the calculations pane, you are doing two more advanced calculations that are linked. Since these calculations would be solved in the same pass, i.e., you placed them within the same scope statement, etc, then solve_order would explicitly spell out the order for the engine to calculate. Mostly, however, solve_order is used in MDX query examples as provided in the MSDN article.

February 20, 2009 16:29 by Sid
E-mail | Permalink | Comments (0) | Comment RSS

Analysis Services Tools

Happy New Year! I know it is a bit late, but this is the first post I have been able to produce thus far, so please forgive…

After many moons, I am backing to working on Analysis Services. As such, I am dusting off old tools and references, as well as building some new ones. I will be posting more on SSAS this month (as it is easiest to keep posting on what I get to work on), but for now, the tools and references:

1. Analysis Services Specific
   1. Script performance analyzer, basic, but it helps explode the details of the query for you
   2. Various bits and bobs posted by the user community, most notable to my needs being the Load Simulator
2. Performance related reading material on Analysis Services
   1. SQL Server Best Practices Article: Identifying and Resolving MDX Query Performance Bottlenecks in SQL Server 2005 Analysis Services
   2. SQL Customer Advisory Team blog post on SSAS performance tips (if you don't have their site bookmarked already, do it now)
   3. TechNet post here on OLAP Design Best Practices – design is key to performance
3. BI Tools in General:
   1. BIDS Helper: http://www.codeplex.com/bidshelper. I had heard this mentioned in passing, but after a customer showed it to me recently, I had to dig into it myself to really see how cool it is. Available for Visual Studio 2005 and 2008

There are tons more on CodePlex.com and in TechNet, but these are a few of my favorites.

Also, having burned through most of the MS Press books on Analysis Services 2005 (and not wanting to spend money yet on the 2008 glosses), I found the SAMS Publishing book, Microsoft SQL Server 2005 Analysis Services by Melomed, Gorbach, Berger and Bateman to be a fantastic reference, in addition to covering in depth subjects areas I have not yet mastered -- highly recommended for anyone who is pretty adept at SSAS, but wants some more detail. Great for beginners as well, though for extreme newbies, I would recommend the MS Press Analysis Services Step by Step as a primer to this book.

Amazon product link below:

January 12, 2009 22:17 by Sid
E-mail | Permalink | Comments (2) | Comment RSS