As an update to my previous post, SQL Server 2005 SP2 is now officially available. The SP2 build is 9.00.3042. A SP2 landing page is available too that includes links to the SP2 release, KB articles, marketing information about the benefits of SP2.
As a personal contributor to Service Pack 2 (mainly in the areas of Reporting Services SharePoint integration and Analysis Services), I hope you enjoy it!
Now that I’ve read the Analysis Services Performance Guide (or shall we call it a mini-book) which I announced in my previous blog, I found it to be a great read. I’d recommend it to anyone who would like to get more insights not only about performance tuning UDM but also about the inner workings of the server.
Some caveats… The guide doesn’t answer the perennial and fundamental question about partitioning a large UDM. As I explained in my UDM Data Islands blog, Microsoft scaled down from the initial “super cube” approach and now advocates splitting a large cube into smaller subcubes (which Jamie McClellan referred to as “data islands”) for performance reasons. What I was hoping to find is some performance guidelines and metrics about at what data loads should this split occur. Since this is so important from a performance standpoint, I failed to understand also why there is no reference to linked measure groups and dimensions whatsoever.
Finally, the guide is a bit light from a capacity planning and load-balancing standpoint. Hopefully, there will soon a refresh of the “Creating Large-Scale, Highly Available OLAP Sites: A Step-by-Step Guide” whitepaper.
The highly-anticipated Analysis Services 2005 performance whitepaper is finally here. I found a few things intresting glancing at it:
Something to sure keep me busy when the winter storm hits Atlanta tomorrow…
Its UDM support limitations withstanding (a partial list here), the Report Builder should definitely be on your list if your requirements call for ad-hoc reporting from an SSAS 2005 cube. Here are some Report Builder features that I particularly like:
Of course, the Report Builder-UDM integration will improve in future releases (believe me, I am banging hard on the SSRS-SSAS integration door) to make it even more compelling choice for UDM reporting.
In UDM, the cube space is defined by attribute hierarchies. Dimensions are just logical containers of attribute hierarchies. It will be great if the next release of SQL Server (Katmai) could expand further on the attribute nature of UDM and solve some nagging issues that modelers currently face.
Let’s consider an example. Say, you have Geography and Customer dimension tables (see the AdventureWorksDW database). It is logical to expect that end users may be willing to browse data by the Geography-Customer hierarchy. As common this requirement is, modelers today need to make some tradeoffs to meet this requirement.
To start with, you could embrace the star schema and build two UDM dimensions (Geography and Customer) on top of the corresponding dimension tables. Then, the end users can drop these two dimensions side-by-side in their favorite OLAP browser to slice the data by Geography and Customer. Behind the scenes, the server will cross-join these two dimensions . Although cross-joing dimensions may meet the business need, there are a couple of well-known issues with this approach. First, you won’t be able to define easily useful hierarchy-related calculations, such as ratios of children to parent because there are no user-defined hierarchies. Second, cross-joining large dimensions may impact performance.
Alternatively, you could decide to create a user-defined hierarchy (aka multi-level hierarchy) that spans Geography and Customer attributes. This is where the things get trickier. Assuming a star schema, both dimension tables would join the fact table. To build a Customer dimension spanning both tables, you need to bridge them via the fact table – a definite no-no situation. The other solution is to duplicate the Geography columns to the Customer dimension table but this leads to duplication of database and UDM storage and will complicate the ETL processes.
At this point, ditching the star schema and showflaking the Geography table off the Customer table starts to look appealing. Now, you could build the Customer dimension on top of the Geography and Customer dimension tables – the approach that the sample AdventureWorks UDM takes. The first tradeoff is that you may need to fight an uphill battle with fans of the “classic” dimensional modeling that swear by star schemas. Second, this approach still results in duplication of UDM storage. That’s because, when the Geography and Customer dimensions are processed, the server will still build all dimension attribute hierarchies irrespective of the fact that some of them of identical. For example, if you have a City attribute (from the Geography table) in the Customer dimension, the server will build two City attribute hierarchies – one when building the Geography dimension and another one when building the Customer dimension.
It will be nice if a future SSAS version supports linking attributes from one dimension to another. With this feature, the database schema (star or snowflake) could become irrelevant. The City attribute from the Geography dimension could be linked to the Customer dimension. Of course, the cube schema will still be constrained by the underlying database schema and table relationships. However, cross-dimension attribute relationships will certainly enable more flexible scenarios.
One of the major UDM enhancements that transcends the boundaries of traditional OLAP is flexible dimension relationships, including many-to-many, referenced, and fact relationships. Marco Russo, who helped me tremendously with my Analysis Services book, ha s just published a very comprehensive whitepaper (shall we call it a mini book?) about many-to-many dimension relationships. I had the pleasure to be one of the reviewers.
For those who are not familiar with his work, Marco is one of the few people on this planet that has deep understanding and real world experience with Analysis Services since its early stages. Thus, this whitepaper is a valuable resource that discusses practical implications of the UDM many-to-many relationships. The real-life scenarios presented in the whitepaper unlocks the mysteries of this revolutionary OLAP concept.
Don't forget to check Marco's blog and his SQLBI.EU website for more UDM insights. Great work, Marco, and looking forward to a book from you! [:O]
Thanks for the Geoff’s feedback on the discussion list, today I was able to demystify one of the SSRS-SSAS integration “gotchas” that has been pestering me for quite some time.
Sometimes a report may need the Jump to URL navigational feature to open a parameterized OLAP report. Since the UDM member unique names contain & (to designate the key), I have been unable to find a way to construct a Javascript link that correctly escapes & in the report parameters, e.g.:
="javascript:void(window.open('http://localhost/ReportServer?/OLAP/Daily Product Sales&DateTimeIndex=[Date].[Time Index].&[2003]&SalesTerritoryGroup=[Sales Territory].[Group].&[North America]&rs:Command=Render’))"
Here, the Daily Product Sales report takes two parameters (DateTimeIndex and SalesTerritoryGroup). As I mentioned in Chapter 8 of my book, even if you use the escape code of ampersand %26 (or the escape Javascript function), the browser will “helpfully” unescape the value back to & and the Report Server will choke. The trick is to use %2526 instead of just %26, as the next example shows:
="javascript:void(window.open('http://localhost/ReportServer?/OLAP/Daily Product Sales&DateTimeIndex=[Date].[Time Index].&2526[2003]&SalesTerritoryGroup=[Sales Territory].[Group].&2526[North America]&rs:Command=Render’))"
%2526 is needed in a javascript call with Jump to URL because it gets processed by the browser twice. %2526 goes to %26 which goes to &.
If you are on a lookout for a web-based smart chart that can browse SSAS 2005 cubes, don’t look further. Enter Dundas Chart for OLAP Services! Despite the name (what’s Dundas anyway?), I really fell in love with this control after playing it with its demos for a while. The chart can connect to both server and local cubes. The attached image shows the Dundas Chart connected to the Adventure Works DW cube.
The beauty of the Dundas Chart is that it’s more than a chart. It is a web-based OLAP browser. And it’s AJAX-enabled so the page doesn’t re-post as result of user actions! From an end-user perspective, authoring a chart is a matter of dragging and dropping dimensions and measures. The same experience as creating an OLAP-based pivot or chart report in Excel.
Given the void left by OWC and the lack of Microsoft OLAP browser controls, the Dundas Chart for OLAP Services is definitely something to consider when planning for multi-dimensional web reports.
Recently I got an interesting question about querying UDM many-to-many relationships. For example, in my book I demonstrated how you can implement an UDM model (Bank cube) that has a many-to-many relationship between bank customers and their accounts. That’s because a customer could have more than one account and an account can belong to more than one customer (a joint account).
But what if you want to find only the joint accounts that are owned by any two customers? The following query returns the accounts owned by Bob and Alice:
SELECT
NON EMPTY {[Measures].[Balance]} ON COLUMNS,
Intersect
(
Exists([Account].[Account Number].[Account Number],
[Customer].[Full Name].&[Bob], “Customer Account”),
Exists([Account].[Account Number].[Account Number], [Customer].[Full Name].&[
) ON ROWS
FROM Bank
The trick is to evaluate the sets over the hybrid “Customer Account” measure group which is actually a dimension table but plays a role of a measure group. The Exists function returns the accounts that the given customer owns. The Intersect function returns the subset of the customer sets.
Just learned from the David Gainer’s post, that Microsoft has made a decision to retire Office Web Components (OWC) in favor of Excel Services. This is definitely sad news for me. While I agree that Excel Services could fulfill some of the integration scenarios mentioned in the comments for that post, I can think of at least two scenarios where this won’t be the case:
IMO, it could have been great if the OWC were replaced with a web and Windows Forms OLAP viewers similar to the VS.NET report viewers for SSRS 2005. This would have given developers a light-weight alternative for UDM application integration. Now, we have to wait for third-party vendors to fill in the void. Sigh…
