InfoSol We're passionate about inspired business intelligence
In addition to the Xcelsius Dashboard Maintenance – Best Practices – Using Labels in Excel I wanted to share other important aspects of making Xcelsius dashboards easy to maintain and for other developers to understand. One such practice is having some kind of conformity in your approach to how you lay out the data for your Xcelsius project.
In this blog I will discuss 2 methods, both of which are valid and are useful ways to organize dashboard data. Deciding as an organization which approach you adopt can help with the process of taking up someone else’s project.
Some Xcelsius developers like to use a method of having all the data that is being used in the dashboard on one (or more) tab/s of the Excel spreadsheet, and then as you need to use data for display, using a series of vlookups or match & index functions for displaying data on charts, which they layout on other tabs of the spreadsheet.
My preferred method is to spread the layout in such a way that the names of the Excel tabs generally correspond to the functional area on the Xcelsius dashboard, perhaps even the same name of the Xcelsius tab (Either the Tab Set Container or the Label based menu, is what I’m thinking of), so that all the data being expressed on that “page” of the dashboard is on one tab in the Excel spreadsheet behind the scenes.
I like to have the data insert & any manipulations I have to do for expressing that data nearby the loaded data, so that it is all in one place & I can find it easily. The only difficulty with this is when you have many components on one “page” or you have to create several aggregate levels to display. Spreading it out & spacing it can become an issue, and this is where again, labeling the spreadsheet is so important. (But aren’t we glad that if we move data around on the spreadsheet that the links follow, for those of you who have been using Xcelsius since the 4.5 days!).
This approach, of course, assumes you have the aggregated data to work with! Which leads back to my previous blog regarding data connectivity options. If I have to use QaaWS, I end up having to do a lot of manipulation in the universe to get the data to look or be ordered the way I want to use it in Xcelsius. I usually have to end up making many of what I call, “dimensionalized measures” when I want to end up with a crosstab result. So I have a measure for Jan Sales, Feb Sales, etc. Very tedious. Sorting is another issue that is often easier addressed in the universe, but also can be labor intensive.
Live Office gives me the option to create crosstabs and sort the data in any way I wish and I can also have multiple tabs to aggregate the data at different levels (YTD, MTD, past 30 days, etc). The problem that I so often bump into with this approach is the amount of data when we get down to the detail level. We have all experienced poor performance any time we go over the 512 row default and horrible performance when we approach 1000 rows.
All of these are reasons why I enjoy using the InfoBurst XDS or XDM option, as it addresses all these issues, plus gives me many more options. The Intelligent Cache syntax of this tool is by far the most exciting feature, in my opinion. Using their syntax, I can pass parameters to the XML url call and only return the rows of data I wish to display. For example, within my xml url call, I can add syntax that returns …. YEAR=2009;DIVISION=HPD;DEPT=23. This ability to extract just the rows of data I want to display helps keep my dashboards very responsive, since I am not loading it down with too much data. It also gives me the ability to go to a much more detailed level than I would have been able to with QaaWS or LiveOffice.
XDS or XDM also has syntax to return unique values of a particular column from an xml data set which I can use for my selector. My selector will always have accurate values, as it is loaded from the current xml cache, rather than from a hard-coded list in my Excel spreadsheet which may not be up-to-date.
And, I have the flexibility to create these large XML data sets from a Web Intelligence document or by writing a query, directly to the database, or even from an Excel spreadsheet, and any combination of all three!
Post Updated 2012-05-21: XDM links fixed.
