Wednesday, November 04, 2009
As I have moved forward with PerformancePoint I have made extensive use of queries for filter building. This approach has a couple of distinct advantages over the other options.
- Allows filters to be dynamic and pick up new data as it becomes available. One good example of this is a date filter. We use it to keep our filters as a dynamic set of n time periods from the current time period back (i.e. lastperiods(52,exists([Date].[Week].[Week].members,[Date].[Current Date].&[1]).item(0)))
- It allows the query to be tuned or tweaked to exclude empty members (there are some pitfalls here, noted later).
If you need to build a tree filter based on a hierarchy, a good query is as follows: HIERARCHIZE([Dimension].[Hierarchy].members).
A couple of things to keep in mind.
- Keep your query results below 500 members. PerformancePoint doesn't seem to handle more than that very well, although I have seen cases where we have gone above that.
- Be careful using MDX funtions that require cell inspection (such as NONEMPTY, filter, etc). This is problematic if you have a large cube/measure group. This can cause a scan of the measure group partitions to evaluate your query. NONEMPTY is especially bad about this and is compounded because you cannot chain filters (i.e. I can't pass a date filter into an item filter).
- Use an additional member filter in the query that causes the query to be more specific (i.e. nonempty items for the current week's sales)
- NONEMPTY(HIERARCHIZE([Dimension].[Hierarchy].members),(exists([Date].[Week].[Week].members,[Date].[Current Date].&[1]).item(0),[Sales Amount]))
- Use a very small measure group (at the query's required granularity) to satisfy the request. This requires cube changes and may not be an option. I don't recommend this as a first option because those measure groups would have little usefulness to other analysis being done in the cube as well as the down stream impact on the ETL process and cube processing.
I am currently sitting in a seminar on BI in the Cloud by John Welch at the PASS Summit 2009. SQL Server Azure looks promising for a variety of applications. Currently it only supports SQL Server relational database services but future plans to include the BI stack of SQL Server.
I have yet to see how loading of data is handled and security is handled but it seems to have a lot of potential for small data sets (< 10 GB).
Couple of points:
- Apparently data manipulation is slower than normal SQL Server.
- Queries are limited to 5 minutes
- Max database size is 10GB
- Bulk Insert not available yet but available in an upcoming CTP
- No control over physical database implementation (files,filegroups,partitions, etc…)
- No Windows auth supported