Another installment in the ““Dear aBI” gives advice on Business Intelligence” series of posts where the BOC-related SAP Mentors take a question from a Community Member, take a stab at answering, and open it up for discussion. Being a BOC-related SAP Mentor (check out some of my other work http://www.sdn.sap.com/irj/scn/weblogs?blog=/pub/u/252006856 [original link is broken] [original link is broken] [original link is broken] [original link is broken]), I thought I’d take a stab at this entry’s question (which, much like the final exam in “Back to School“, is in 23 parts) comes to us from Jack in San Francisco:
I want to know the best approach before building the Dashboard, and we would like for our Dashboard to look similar to a portal.
Here is the layout of the Dashboard:
A.User will log into the SAP Portal and click the Dashboard button.
B.It will show 6 to 8 buttons.Eg:-Srvc Contracts,Confirmations,Rejections etc.
C.Each of these buttons will lead to a tab and will have their own KPI’s(15-20) inside with a value or a trending icon and when the user clicks on any KPI a related Dashboard will open in the same page on the right handside and KPI’s will be on left.
D.Similar the user can go to any tab (button) and click on any KPI and the dashboard should display more detail about that KPI
Here are the questions (and my answers).
1. Should the upper tabs (buttons) — e.g. Srvc contracts ,Confirmations, rejections– be built in the portal or Xcelsius?
Sort of depends on what all you want to show on those buttons. If the buttons are just names and a true launching off point, the portal is fine. If however, you want to sexy it up a little bit, (and who doesn’t?) I’d recommend putting those buttons in an Xcelsius model, and making them alerts. You could have a button and ghost a sparkline trend on it for the key measure, or just make the buttons that need attending to Red or something. Then, if you decide to kick out to a separate dashboard (***SPOILER ALERT*** – another question about that is below) you can just use an OpenDoc call to do that. Yeah, executives like sexy.
2.Should I create each dashboard for a KPI seperately and load it when the KPI is clicked or use one dashboard for all?
If at all possible, I would recommend doing it all within one dashboard. It’s just easier to keep all of the plates spinning when you only have one plate.
3.When the user clicks on Srvc contracts and it shows (15-20) KPIs at a time, should I do the calculations in Bex query or in Cube side?
I don’t understand the words you are saying: I’ve never even seen Bex. Hopefully someone can help with the specifics in the comments. In general, in any environment, you want to fetch data in exactly the format you want it to display in, in the fastest time possible. So, if you are going to consume the data in a chart, and that chart can most easily consume the monthly date out to the right (like a crosstab) instead of down (like a standard result set), monkey with your query so it comes back looking like a crosstab. If you are going to summarize things at the region level, query the data so it comes back at the regional level rather than some lower level and summing that up.
4.Is it possible to pass the filter values to KPI’s and related Dashboards (below the Srvc contracts,Confirmation we will have Area filter,Dept filter etc and when the user selects a filter the KPI values in the tab and on the related dashboard should be updated)?
Also, if I might be so humble as to throw in a suggestion for handling those filters, I’d recommend this brilliant Don’t go chasing waterfalls (Or how to use “Insert Filtered Rows” effectively).
5.We’r expecting to use around 5,000 rows of data under each tab(button)to support detail on the hierarchies. Does Xcelsius hold this much of data?
Xcelsius can hold as much data as you can throw at it. Its just a dog if you use too much. I would say that you should really concentrate on not needing that many rows. If you are bringing in any data you aren’t planning on consuming – don’t. If you can aggregate it in your query so you really only need 50 rows (or better yet 1 row) do that. If you really have 5,000 detail rows you need, you can’t see them all at once anyway, so why not call them 25 rows at a time (paginating them). Stephanie Lyon did a great presentation on this at the SAP BusinessObjects conference a few years ago, but I don’t see it on her site right now, and I’ve used the technique to great effect. Contact one of us for more info.
6.What should be take into count for Performance considerations?
Everything. You’ll want to do everything in your power to make this thing fast, because if it isn’t people won’t use it, and they’ll be asking you why they spent a ton of money on a product that no one will use. Yeah, executives like firing people for wasting money (themselves excluded, natch).
In the end, Xcelsius is a great tool once you get used to it. My best advice: don’t be afraid to throw some work away, because at some point in every project you’ll realize you did something painfully inefficiently and it will cause everything downstream to suffer. Just go fix it.
My second best advice: don’t cut your own bangs.
My third best advice: go submit a question to the ““Dear aBI” gives advice on Business Intelligence” series for even more fun and insight.