About

It’s been a long time since I rock and rolled

Just kidding… I was head banging on the way into work today….

Well it’s been about 7 months now since I last blogged. Figured I should catch everyone up

Well I worked at a global retail bakery company for the last two years as the manager of business intelligence for north america. Catchy title huh? I had a blast though. Got to travel the world (ok, just to Amsterdam), I gave birth to a brand new data warehouse (yes it hurt) and made some good friends. But today that data warehouse is standing on its own feet and it has not slammed it’s fingers in the door…yet…that I know of….

Recently though I decided to move back into the world of consulting. So I am now a brand spanking new solutions architect for Valorem Consulting. Great company with some pretty cool (and smart) people. The other nice thing….they are really good friends with MSFT. And since I love their cool-aid, I fit right in.

So over the next few months I am hoping to blog at least once a month on some new, exciting projects that I am working on. I will also finally be taking my certifications. It’s only been 4 years since they have been out and i figure by now they should be pretty stable…. 🙂

Well…until my next post….arrivederci!

MDX – Top count with aggregate

A few times now I have had a request to build a reporting services report where I show the TOP N customers followed by the subtotal and then a total of ALL OTHERS. This query could be broken out into three seperate MDX queries, but I want to use only one tablix in SSRS. Since you cannot point mulitple data sets at a single tablix, I came up a query that would give you a single data set in the correct order.

WITH
SET [Top10Products] AS
Order
(
TopCount
(
[Product].[Product Name].[Product Name]
,10
,[Measures].[Sales Amount]
)
,[Measures].[Sales Amount]
,BDESC
)
MEMBER [Product].[Product Name].[SumTop10Products] AS Aggregate([Top10Products])
MEMBER [Product].[Product Name].[All Other Products] AS Aggregate(Except([Product].[Product Name].[Product Name],[Top10Products]))

SELECT
[Measures].[Sales Amount] ON COLUMNS,
[Top10Products] +
[Product].[Product Name].[SumTop10Products] +
[Product].[Product Name].[All Other Products] ON ROWS
FROM Sales
WHERE [Date].[Calendar YQMD].[Calendar Month].&[200903]

The set Top10Products is pretty self explanatory. The next custom member is an aggregate of my Top10Products set (my subtotal). The next custom member is All other products EXCEPT the Top10Products.

In the select statement, I am using the + sign to UNION the members together in the order I want. Some poeple may want to use UNION instead, but I like the + sign.

What you end up with is

*this query was written against the Contoso database

Are you starting a Business Intelligence project?

If you are, I strongly suggest you have a look at this

http://businessintelligence.iu.edu/index.shtml

This is the Indiana University website for their BI Initiative. The plan looks well thought out and they do an excellent job at identifying all of the key area’s that will help them be successful. If you have a few minutes, I highly recommend taking a look!

p.s. Ralph Kimball and Joy Mundy actually point to these guys in their MS DW Toolkit book as a resource for naming standards in data warehouse design. In my mind that says alot about the EDSS team at IU.