April 25, 2011 at 9:59 pm
Is there a way to view the details (the grain) in an OLAP cube in excel? I know excel can already show the measures that are aggregrated, but the users want to see the details... ie the individual grain. So, in my case, the fact table contains order information (order number, the dollar amount, etc). The measures are all aggregrated, but once the user sees an aggregration, they want to be able to see the details of that aggregration. Anyway to do this?
April 26, 2011 at 10:18 am
A way to get the to the detail level of a cube is to have a dimension in that cube that has a 1-1 relationship with the fact data. In your case, if there is one fact row per order, then add an order dimension that has one row per order. This way when you are browsing in Excel clicking on the order dimension will show each order with it's corresponding fact data such as sale amount, etc.
LinkedIn - http://www.linkedin.com/in/carlosbossy
Blog - http://www.carlosbossy.com
Follow me - @carlosbossy
April 26, 2011 at 1:25 pm
Awesome - but my table is a accumulating snapshot fact table, and so I have multiple rows with the same order. Can I still do this then?
April 26, 2011 at 5:39 pm
An accumulating snapshot fact table would still have only one fact per order if the grain of the fact table is a single order. An accumulating snapshot would allow that order fact to be updated as new data became available, such as a shipping date. If you have more than one order per fact, then the grain of your table is not an order, but something different.
Are you adding a new fact row for the an order when the order is changed? Is this how you have more than one row per order? If so, then it will be difficult to make the fact additive, since adding all the orders for a particular dimension will not yield the correct answer. However, even with that design you can still have an Order dimension with every order number represented, but you will have one or more facts associated to each row in the order dimension. This will allow you to see the detail for each order by order number.
LinkedIn - http://www.linkedin.com/in/carlosbossy
Blog - http://www.carlosbossy.com
Follow me - @carlosbossy
May 5, 2011 at 11:54 am
I agree with Carlos that this may be a viable solution for you. My team has built something similar for invoice numbers. However, we strongly advised our client against this approach given the sheer volume of transactions and number of measure in our cube. The number of intersections is gigantic and we warned them that the performance would not be good at all. However they insisted that it must be this way and so now they have an InvoiceNumber dimension that is used by no one because in-deed, it performs so poorly.
Have you looked into "Drillthrough"? http://msdn.microsoft.com/en-us/library/ms151799(SQL.90).aspx. Are you exposing physical measures? Drillthrough does not work on calculated members.
Chris Umbaugh
Data Warehouse / Business Intelligence Consultant
twitter @ToledoSQL
May 6, 2011 at 8:52 am
Good news: There is a way of doing this.
Bad news: Setting up takes quite some time (and some expertise) and there are a few prerequisites.
In a nutshell here is what I have seen done for a fully functional and dynamic "drill-to-detail":
1. your database needs to be in a clean star-schema (actually snow-flake to be exact)
2. you need to generate an XML schema of the cube structure (DSV) that you can easily access
3. You need an SSAS assembly that can reverse engineer any cell action click from its MDX coordinates into the XML from step 2 to produce the corresponding SQL.
4. You create a rowset action that triggers step 3 above and then fires off the SQL against the target DB and returns rows
5. Lastly, you need a client that can both fire off the rowset action and also return and show you the results (which is unfortunately a very short list of candidates)
Sounds like a major headache. It is. But it does work and it can be super elegant and super smart - self resolving perfectly regardless of the query.
The good news is that I have worked with 2 companies that can provide you with the pieces to the above:
- Iris business intelligence can provide you with the solution to steps 2 and 3.
- Pyramid Analytics can resolve steps 4 and 5.
snap 😎
May 9, 2011 at 8:47 am
Thanks for the suggestions guys - it seems that when you use Excel as the provider, any aggregration cell can be double clicked on, and when double-clicked, it shows all the rows used for that aggregration (similar to a pivot table). That is basically all I need... Thanks 🙂
note: you defnitely need the 1-1 relationship in the data itself...
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply