Administrator of the application we are working on wants to allow users to do some analysis on their budget data at detailed level, crossing 3 dimensions in rows (the same way they are doing in SAP currently) which are quite big (entities, cost centers and accounts).
I proposed him to have users using quick view to analyze but he estimates that users will not be able to use this and wants that they use rather objects that are kind of pre built.
Then I thought of either cube view or BI Viewer (with CV MD as source) as they want to have totals in the retrieve corresponding to the parents of the dimensions but then either potential number of intersections are too big and do not allow to render the CV or performance is not good.
So wanted to reach out here to see what could be the options to answer above needs (give user a nice to use place to drill in those 3 dimensions to analyze his data while still having totals for the parents of those 3 dimensions) ?
Thanks for your help
Hi MatthieuR: I've got lots of thoughts here so bear with me.
1) "analysis on their budget data at a detailed level crossing 3 dimensions...which are quite big"
Sounds like they are doing a data dump rather than actual analysis. If I were to do a 3 dim expansion (in our system: division, cost center, acccount) I get close to 10k rows of data. You cannot do meaningful analysis on that.
What they probably want is the ability to take some higher level totals and then break it down in various ways. For example: start with PL; select some variance; then break it down by sub-account; then break it down by cost center.
1a) One solution we employ for this type of analysis is to use linked cube views. We have a core set of standard views that provide common analysis starting points. These views all have shared drill-down, Linked Cube Views. These linked cube views also have access to the drill-down Linked Cube Views. Which means you can start anywhere and explore through the dimensions continually drilling into the data as needed.
1b) Similar to the above, you can put these components into a dashboard that explores the data as you click on it. With dashboards you can throw charts into the mix as well (if it would be useful)
2) "potential number of intersections are too big and do not allow to render the CV or performance is not good" If they truly want the data dump route, try turning on suppression + sparse row suppression. My 3 dim expansion has >4.4M possible combinations, but because it is so sparse resolves down to <10k actual rows. With sparse row suppression this returns in just a few seconds.
3) "I proposed him to have users using quick view to analyze but he estimates that users will not be able to use this..." Push back on this. They should absolutely be teaching their users how to use QuickViews. In my mind, this is the other solution you can employ if you don't want to take the time to build out the linked cube views. QuickViews are extremely powerful and so much better than legacy tech (*cough* SmartView). They are ignoring a large piece of functionality if they aren't going to teach folks how to use QuickViews.
Follow-ups welcomed. -DB
Hi, DB's options are all valid. As you know there are different ways to report large amounts of data and it all depends on the actual requirements and the amount of data. Both we do not know (as of yet).
In addition to DB's suggestions, you could also consider a Large Data Pivot Grid. BI Viewer, you already mentioned. Again, the solution must meet the requirements, which you know best, and suit the amount of data.
DB also pointed out one very important point: Push back and nudge the customer into the right direction. They might want certain things because of an experience they had with a legacy solution, but sometimes need (strong) convincing that something else will be a better solution for their requirements. What is the best solution is up to your discretion as you are on the ground.