Monday, December 19, 2005

From detail to summary in two clicks

My heart leaps up when I behold a crosstab... (to mis-quote Wordsworth). Imagine being able to tell your users that you can take about a quarter million cells of data, and summarize them into nine cells! Of course the assumption being that your data is amenable to that kind of extreme summarization. A picture being worth a thousand words I shall demonstrate, and illustrate, with screenshots. (I have never really tried to test that theory out.... is it really worth only a thousand words? Do you need a really large picture? Is there a formula that you can use to convert pixels into words? You get the drift; this can meander into a really exciting debate, one that I am not too inclined to enter, just not yet.)

Take the screenshot below. It is a table. It shows Profit, Sales, and Unit Sales data at the detail level. I.e. it shows data at the transactional level. I have included only the 'Region' level of the geography dimension. There are only three regions in my data set: East, Central, and West. Which is why these members repeat many, many times. But each row represents a transaction. And I have more than 87,000 transactions. This is a lot of data. You can imagine the excitement if this were to be a few million, billion rows. How do you make sense of this data? Or more precisely, how do you start to make sense of this data?
One way to do is to summarize this data. And if you are using Discoverer, you can summarize this data in about three clicks . How? Simple. From your menu click the arrow to the right of the 'Add Worksheet' icon (that would be the sixth icon in the Discoverer toolbar).
Or you could use the menu and select 'Edit' >> 'Duplicate Worksheet' >> 'As Crosstab'.

Up pops a screen where you can adjust the layout of your crosstab.
Click the 'OK' button and there you go! Nine cells of data. Profit, Sales, and Unit Sales have been summarized for each of the three regions.
Simple, yet powerful (or is it powerful, yet simple?). Discoverer has had this capability for many, many releases now. Yet, as I read and scout the web for news and views, I hear that there are vendors that are aiming to bring out similar functionality only next year, or the year after next. How original.