Browsing a Cube and Influencing Aggregations

September 27, 2007

You can browse a cube through the cube browser in Analysis Services or through the one in the Management Studio. I usually use the one in the Management Studio, as it gives your more space to look. In the cube I created in Analysis Services, I added a Budget hierarchy in my Budget dimension. I decided to check the data for the new hierarchy in comparison to the money that was assigned to it in the Budget fact table. And it was problematicโ€ฆI was opening the Budget hierarchy and in the first 3 levels it reacted pretty quickly, but at level 4 (and I have about 9 levels in the hierarchy) it would practically die and bring my computer down with it! I went into the Partitions tab in the Cube in the SSAS Project. I went into the Aggregation Design Wizard and pretty much went through it all! I gave it a performance gain that reaches 100%, I gave it an estimated storage of 100M, I gave it a chance to run aggregation till I clicked stop. BTW โ€“ this is also when I found out that at times, it doesn’t really matter that I don’t click stop. The aggregation wizard can, at times, stop making aggregations on its own, if it doesn’t find it needs any more aggregations. Yes, very smart wizard ๐Ÿ™‚ โ€ฆI also decided to manually click high values in the count the aggregation wizard runs before enabling you to manipulate the aggregations. Even that didn’t work.

 

The only thing that seemed to have the slightest influence was connecting the dimension directly to the fact table (rather than leaving it as a referenced dimension as I did before). That made the cube browser react quickly even at the 4th and 5th level, but I still had 4 more levels to go.

 


In a moment of desperation, I decided to check how it would look to the user in the Panorama Nova View. And it reacted great there! In all of the levels! Needless to say, I was stunned! It was as if all my efforts from before were in vain. Checking what was happening in the background, I understood the reason for the difference in performance. In the cube browser, the back engine creates lots of sql queries to determine what it needs to show. In the Panorama Viewer on the other hand, there is just a single MDX query being generated for each click you make. And that โ€“ makes all the difference!


So my last word of advice is this: when you want to look at your cube โ€“ do it in the proper viewerโ€ฆ

Add comment
facebook linkedin twitter email

Leave a Reply

Your email address will not be published.

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <s> <strike> <strong>

*

3 comments

  1. MarcoOctober 29, 2007 ื‘ 20:19

    Hi,

    I know NovaView since release 2 and the Java WebClient is one of the fastest OLAP Web Browsers available !

    I was wondering what is the speed with your OLAP Cube and 9 level dimension when you use ReportPortal as frontend compared with SSAS CubeBrowser or NovaView.

    Marco

    Reply
  2. Ella MaschiachOctober 31, 2007 ื‘ 10:14

    Hi,

    Well I’m afraid to say I have yet to develop any reports in Reporting Services against my cube. The ones I did develop were against the tables themselves. For that matter, I found I did need an addition of indexes on the tables, else the query would take too long.
    Hopefully, I’ll find myself developing a Report Model based on my DSV for the Report Builder, and then I’ll get to answer your question fullyโ€ฆ

    Reply
  3. Ella MaschiachNovember 25, 2007 ื‘ 9:59

    Marco,

    If you’re interested, I have answered your question in the above comment. Please click the “Ella Maschiach’s BI Blog” for the relevant post. The last paragraph has the answer.

    Reply