Publishing Intuitive Top B’s for Every A for Excel users

One of the most common reasons why we would go to the trouble of modelling our data in a cube is performance.  However self service access to the data is one of the other huge benefits that allows us to enrich the user experience and free up our workloads.

When a list of dimension members heads into the hundreds or thousands, users naturally seek out the Top N members, or more specifically, give me the Top 10 B’s for every A.

In the following example we want to see what the Top 10 Selling Products are for Every Supplier.   If we attempt to create a dynamic set called “Top N Suppliers”, and then browse the model in Excel, we will find the cross join occurs between only 10 suppliers and our product members.

Not only is this not intuitive for the end user but does not meet the requirement adequately either.

Creating a more intuitive set called “Set1 – Top 10 Suppliers for every Product – Sales $” allows us to publish this is workable self service solution.


WITH

SET [Set0] as [Product].[Product Description].Children

SET [Set1] as generate([Set0], crossjoin([Set0].Current, TopCount(NonEmpty([Supplier].[Supplier Description].Children, [Measures].[Sales $]), 10, [Measures].[Sales $])))

SELECT

[Measures].[Sales $] on columns,

NON EMPTY [Set1] on rows

FROM [Product Sales Data]

How about the Top 5 Suppliers for the Top 5 Selling Products ?

SET [Set0] as Head(ORDER(NonEmpty([Product].[Product Description].Children, Measures.[Sales $]),Measures.[Sales $],BDESC),5)

SET [Set1] As generate([Set0], crossjoin([Set0].Current, TopCount(NonEmpty([Supplier].[Supplier Description].Children, Measures.[Sales $]), 5, Measures.[Sales $])))

We can make this even more intuitive by adding the Time Period, in this case, Last 5 years.   A set called “Set2 – Top 10 Suppliers for every Product – Sales $ (Top 2 Performing Years)” provides instant access to the top time periods as well.  This could work equally well for months, weeks or days.


WITH

SET [Set0] as [Product].[Product Description].Children

SET [Set1] as generate([Set0], crossjoin([Set0].Current, TopCount(NonEmpty([Supplier].[Supplier Description].Children, [Measures].[Sales $]), 10, [Measures].[Sales $])))

SET [Set2] as generate([Set1], crossjoin([Set1].Current, TopCount([Time].[Year].Children, 5, [Measures].[Sales $])))

SELECT

[Measures].[Sales $] on columns,

NON EMPTY [Set2] on rows

FROM [Product Sales Data]

Richard Lees also has another good angle on this using Generate and order.

Thanks to Chris at Stackoverflow.

About AussieBICG

Connect with me here https://au.linkedin.com/in/aussiebicg

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s