Category Archives: SSAS

MDX, SSAS, SSRS

Use Date Picker Control with MDX Based Reports

If you’ve done a couple cube based reports, you’ve run into the issue of the dates being in string format. Because the dates are strings inside your cube, you can’t use the neat little date picker control reporting services gives you for datetime parameters. While you as a developer may understand this issue, your "tech savvy" end users may not. Fret no more. Here is a quick and easy way to use the built in date picker control for parameters in your MDX reports.

1) In the Report Data pane of BIDS, open the parameter properties for the parameter(s) you are using to specify a date or begin and end dates and change the data type to datetime. Change available values to None.

2) Go ahead and delete any hidden datasets that populate the drop down lists for your date parameters. You won’t need those with the date picker control.

3) In your data set that populates your report, open the properties, and in the parameters view, you want to use an expression on the parameter value field. So instead of the parameter value containing something like "=Parameters!DateDate.Value", you want to change it to something like:

="[Date].[Date].&[" + Format((Parameters!DateDate.Value), "MM-dd-yyyy") + "]"

Keep in mind that you’ll need to perform step 3 on any data sets that use your date parameters.

The key is to format the expression in such a way that it looks exactly like the members of your date dimension. Once you do that, click preview and you have the beautiful and end-user friendly date pickers in your cube based reports!

You can also go back to your parameters to specify default values using today and/or dateadd (ex. "=dateadd("M",-12,today)").

One of the downsides to using the date picker control with reports using your cube is a data source is that if the end user selects a date in the date picker that is not in your cube, your report is going to blow up, so just be aware of that.

If you have any questions, feel free to leave a comment!

SSAS

SSAS 2008 Storage Modes

In SQL Server Analysis Services 2008, we have three storage mode options available to us: Relational Online Analytical Processing (ROLAP), Multidimensional Online Analytical Processing (MOLAP) and Hybrid Online Analytical Processing (HOLAP). There are advantages and disadvantages to each, so I figured I’d take a few minutes to give a quick overview describing the storage modes and laying out some of the pros and cons of each.

Relational Online Analytical Processing (ROLAP)

The ROLAP storage mode allows the detail data and aggregations to be stored in the relational database. If you plan on using ROLAP, you need to make sure that your database is carefully designed or you’ll run into some bad performance issues.

Pros:

  • Since the data is kept in the relational database instead of on the OLAP server, you can view the data in almost real time.
  • Also, since the data is kept in the relational database, it allows for much larger amounts of data, which can mean better scalability.
  • Low latency.

Cons:

  • With all the data being stored in the relational database, query performance is going to be much slower than MOLAP.
  • You must maintain a permanent connection with the relational database to use ROLAP.

Multidimensional Online Analytical Processing (MOLAP)

MOLAP is the default and thus most frequently used storage mode. With MOLAP storage, the data and aggregations are stored in a multidimensional format, compressed and optimized for performance. This is both good and bad. When a cube with MOLAP storage is processed, the data is pulled from the relational database, the aggregations are performed, and the data is stored in the AS database.

Pros:

  • Since the data is stored on the OLAP server in optimized format, queries (even complex calculations) are faster than ROLAP.
  • The data is compressed so it takes up less space.
  • And because the data is stored on the OLAP server, you don’t need to keep the connection to the relational database.
  • Cube browsing is fastest using MOLAP.

Cons:

  • Because you don’t have a real time connection to the relational database, you need to frequently process the cube to update your data.
  • If there’s a large amount of data, processing is going to take longer.
  • There’s also an additional amount of storage since a copy of the relational database is kept on the OLAP server.
  • High latency.

Hybrid Online Analytical Processing (HOLAP)

HOLAP is a combination of MOLAP and ROLAP. HOLAP stores the detail data in the relational database but stores the aggregations in multidimensional format. Because of this, the aggregations will need to be processed when changes are occur. With HOLAP you kind of have medium query performance: not as slow as ROLAP, but not as fast as MOLAP. If, however, you were only querying aggregated data or using a cached query, query performance would be similar to MOLAP. But when you need to get that detail data, performance is closer to ROLAP.

Pros:

  • HOLAP is best used when large amounts of aggregations are queried often with little detail data, offering high performance and lower storage requirements.
  • Cubes are smaller than MOLAP since the detail data is kept in the relational database.
  • Processing time is less than MOLAP since only aggregations are stored in multidimensional format.
  • Low latency since processing takes place when changes occur and detail data is kept in the relational database.

Cons:

  • Query performance can head downhill fast when more detail data is queried from the relational database.

This is by no means an exhaustive list of the details in regards to each storage mode, but I hope this has given you a good general understanding of the differences between ROLAP, MOLAP, and HOLAP. Feel free to post any comments or questions. Thanks, guys!

Free Training, Pragmatic Works, SSAS, SSRS

Recording Available for Reporting on a Cube with SSRS 2008 Webinar

Thanks to all of the attendees that showed up for my webinar yesterday. I had a great time speaking and I hope everyone enjoyed the webinar and maybe even learned something.

If you missed the webinar, Reporting on a Cube with SSRS 2008, have no fear! There is a recording available for you to watch. Just head over to the Pragmatic Works CMS site and create a free account. Not only will you be able to watch my webinar from yesterday, you’ll also be able to check out tons of other great webinars from some really sharp speakers. So head over to the Pragmatic Works CMS site, create a free account, and watch my webinar here.

Free Training, Pragmatic Works, SSAS, SSRS

Free Webinar Tomorrow (7/21) @ 11 am EST: Reporting on a Cube with SSRS 2008

Tomorrow (7/21) I’ll be speaking on reporting on an SSAS cube with SSRS 2008 at 11 a.m. EST. It’s a completely free event hosted by Pragmatic Works.

In this session I’ll give you an introduction to reporting on a cube with SQL Server Reporting Services (SSRS). You’ll learn about some of the advantages of using your cube as a source for your SSRS reports, how to build reports based on your cube with minimal knowledge of MDX, as well as a few tricks you can use to increase the usability of your reports.

It’s completely free to attend and you can do it during your lunch break. I’ll also be taking questions so don’t forget to get registered!

Errors, MDX, SSAS

MDX Aggregate Functions Can Not Be Used on Calculated Members in the Measures Dimension

2 Comments

If you’ve ever tried to use the Aggregate function on a calculated measure, you’ve seen the following error:

Aggregate functions error

This is a problem if you’re trying to calculate something like Year To Date for a calculated measure. Take the following MDX query (which I understand doesn’t make sense and isn’t something you’d do in a real world situation, but just go with it for the sake of the example), which can be run against the Adventure Works cube, for example:

WITH MEMBER [Measures].[YTD Average Price] AS Aggregate ( PeriodsToDate( [Date].[Calendar].[Calendar Year], [Date].[Calendar].CurrentMember) , [Measures].[Internet Average Unit Price] ) Select [Measures].[YTD Average Price] on0, [Date].[Calendar].[Date] on1 From [Adventure Works] Where [Date].[Calendar Year].&[2003]

 

If you execute this query, it will complete successfully with #Error as the results. You have a couple different options. First, you could replace the Aggregate function with the Sum function. If you are unable to use the Sum function, then you’ll have to  adjust the way you calculate Year to Date Average Price. We’ll have to break this up into a couple different calculations.

WITH MEMBER [Measures].[YTD Unit Price] AS Aggregate ( PeriodsToDate( [Date].[Calendar].[Calendar Year], [Date].[Calendar].CurrentMember) , [Measures].[Internet Unit Price] ) MEMBER [Measures].[YTD Transaction Count] AS Aggregate ( PeriodsToDate( [Date].[Calendar].[Calendar Year], [Date].[Calendar].CurrentMember) , [Measures].[Internet Transaction Count] ) MEMBER [Measures].[YTD Average Price] AS [Measures].[YTD Unit Price]/[Measures].[YTD Transaction Count], format=“Currency“Select [Measures].[YTD Average Price] on0, [Date].[Calendar].[Date] on1 From [Adventure Works] Where [Date].[Calendar Year].&[2003]

 

Doing our calculation this way, we are able to work around our inability to use the Aggregate function on a calculated member. I hope this gives someone an idea of how they can work around this annoying issue.

Pragmatic Works, SSAS

SSAS Workshop in Phoenix, AZ 6/21 – 6/22

Capture

Coming up next week starting June 21 through June 22, I’ll have the really cool opportunity to teach the SSAS Workshop with Brian Knight of Pragmatic Works during the Expedition Denali Workshops tour in Phoenix, Arizona. This will be my first time teaching at the Expedition Denali tour and I’m so excited. It’s going to be awesome! We’re going to be covering everything from dimensional modeling to building a cube to writing MDX and even Data Mining. It’s two full days jam packed with great information that’ll get your BI team ready and able to start developing an AS solution in your environment.

If you’re looking for some top quality training from industry experts, I’d highly suggest you consider registering for this great training opportunity. We’ve still got seats available for the workshop in Phoenix, so just go here to get registered. If Phoenix is just too darn far away for you, we’ve got a bunch other workshops lined up at other locations such as Toronto, Chicago, Dallas, Charlotte, and more. We’ve got seat available but make sure you get registered soon because these awesome workshops fill up fast!

MDX, MDX Puzzle Solution, SSAS

MDX Puzzles: Super 8–Solution

1 Comment

This week’s puzzle was pretty straight forward, but it required you to use the OpeningPeriod function. To show the count of customers on the first day of the year, we’ll create a calculated measure called “First Day Customer Count”.

WITH MEMBER [Measures].[First Day Customer Count] AS 
(OpeningPeriod([Date].[Calendar].[Date],[Date].[Calendar].CURRENTMEMBER),[Measures].[Customer Count])

In the above calculated measure, the first argument of the OpeningPeriod function specifies at which level we want the first period for and in this case we want the first period at the Date level. The second argument allows us to specify for which member we’d like to see the first day of the year.

Secondly, we’ll create a second calculated measure called “First Day”, which is very similar to our first calculated measure except we’ll be using the .NAME property to get the name of the first period of the specified level.

MEMBER [Measures].[First Day] AS
OpeningPeriod([Date].[Calendar].[Date],[Date].[Calendar].CURRENTMEMBER).NAME

Then finally we’ll reference these to calculated measures in our final select statement.

WITH 
MEMBER [Measures].[First Day Customer Count] AS 
(OpeningPeriod([Date].[Calendar].[Date],[Date].[Calendar].CURRENTMEMBER),[Measures].[Customer Count])

MEMBER [Measures].[First Day] AS 
OpeningPeriod([Date].[Calendar].[Date],[Date].[Calendar].CURRENTMEMBER).NAME

SELECT {[Measures].[First Day],[Measures].[First Day Customer Count]} ON 0,
[Date].[Calendar].[Calendar Year].MEMBERS ON 1

FROM [Adventure Works]

I’d like to say thanks to everyone who participated in this MDX puzzle. We had several great submissions but I can only pick one winner. Super 8’s puzzle winner is Jason Thomas (@de_unparagoned)! Congrats, Jason! I’ll be sending you one cool Kick’n SSAS t shirt in the mail as soon as I get off the road and back home next week.

Stay tuned for the next round of MDX Puzzles!

MDX, SSAS

MDX Puzzles: Super 8

1 Comment

If you’ve followed my blog over on BIDN.com, you’ve probably come across my series of MDX puzzles. If you’re not familiar with the MDX Puzzles series of post, check this out. As always, these MDX queries were written against the Adventure Works DW. Here are the requirements:

Rows:

  • Calendar Year

Columns:

  • Customer Count on the first day of the year (may not be Jan 1 if Jan 1 does not exist)
  • First Date in Year

Hints:

  • Take a gander at this

And here’s a sneak peak at my results:

QueryResults

If you think you’ve got the correct answer, send your query to

 email me

Of all the correct answers I get, I’ll pick one at the end of next week (April 29) and send out one of these really cool shirts, so good luck, peeps!

KickinSSAStshirts

SSAS

SSAS 2008 Bug: Normal Additive Measures and Semi Additive Measures in the Same Measure Group Don’t Display Properly

Devin Knight and I stumbled onto another weird SSAS bug today. We had a measure group that had two measures. The first measure was semi additive (Last Non Empty) and the second measure was a normal additive type (Sum). The additive measure would not display along side the semi additive measure, whether I browsed the cube in Excel, BIDS, SSMS, or wrote an MDX query.

Well as it turns out this is a known issue that was identified some time ago. SQL Server 2008 SP1 solves this issue: http://support.microsoft.com/kb/969099 .

bug

So if you’re ever on site working in an environment you are unfamiliar with and you experience goofy behavior where semi additive and additive measures in the same measure group do not display properly, check the SQL Server AS version! The version to look out for is 10.0.1600.

Errors, SSAS

SSAS 2008 Error when browsing cube in BIDS or SSMS: “An error 0xE0040200 occurred. No further information was provided.”

While working on an SSAS project today, I was scripting out some MDX calculations in the cube (year to date, month to date, previous year’s month to date, and previous year to date). I wrote the calculations and they looked perfect, but when I tried to browse the cube in BIDS after deploying, I received this very perplexing error:

Error when browsing cube in BIDS or SSMS: “An error 0xE0040200 occurred. No further information was provided.”

After stumbling around for a bit and doing some research, I eventually discovered that if I clicked the “Show Empty Cells” icon (seen below),

Show empty cells icon

my calculations appeared to be working correctly. Apparently this is some kind of bug in the cube browser in SSMS and BIDS. You’ll notice that if you connect to your cube from Excel and browse the cube using a Pivot Table, you are able to successfully browse the cube without any strange errors. I hope this saves someone else the 45 minutes I spent trying to figure out what in the world was going on.