Data files do not always come in tabular shape; more often than not they are cross-tabbed with some attributes on columns and others on rows. A very typical scenario is a worksheet with a time dimension in the columns headers (months, years…) like this one: http://xls.apphb.com/Ch13X2.xlsx, which represents the total expenditure per capita on health in different countries from 1995 to 2010. As you can see there are country names on rows and years on columns. The metric name is actually in cell A1.
MicroStrategy Express has the option to uncross this kind of files, pivoting the year on rows, and converting it to an attribute.
From now on, I will use the Chrome browser instead of IE, the main reason is that Chrome has better support of HTML5, which we'll need in the next recipes. So, if you don't have it already, please download and install it from http://at5.us/Ch13U5. In Chrome (as in other browsers), you can use the F11 key to toggle between full screen and windowed mode.
Open Express in Google Chrome and optionally switch to full screen:
- From the main page, select New Dashboard….
- Under Select Your Data Source, click on Use File from URL, and type
http://xls.apphb.com/Ch13X2.xlsxinto the Enter a URL for Excel/CSV File textbox and click on Continue. - When the data preview appears, in Select a data format: check the radio button labeled Crosstabbed and enable the checkbox labeled No Metric Headers.
- The data grid is now surrounded by a selection rectangle with small black squares on the edges. Move the cursor to the top-left black square.
- Click-and-drag the cursor until only the values are selected (see the following screen capture):
- The blue background indicates that both the country names and the years are considered attributes, click on Continue.
- Inspect the data: first two columns are attributes and the third is a metric. Rename the columns using their context menu:
- First column:
Country - Second column:
Year - Third column:
Per Capita Total Expenditure on Health
- First column:
- Click on Continue. After a few seconds you will see the Select a Visualization dialog, click on the group Area and then Vertical Area - Stacked.
- Show the Filters pane by clicking on the Show | Filters toolbar menu.
- Drag the Country attribute from My Data to the Filters. A new textbox appears under the Country header, type
Belgiumand click on the small gray tag that pops up labeled Belgium. - The graph automatically reflects the selection. Now under the Graph | GRAPH | Vertical axis pane, open the context menu of the Per Capita Total Expenditure on Health metric and rename it
Health Expenditure. - Lastly, add some more countries using the Filters pane. For example,
France,UnitedKingdom, andGermany, to see which spends less per capita on health. - When you're done hide My Data and Graph panes clicking on their respective X icons next to the title (tool tip: Close). Rename the Vertical Area - Stacked header to
Health Expenditure per capita, and change the Layout 1 tab title toStacked Area Graph. - Click on Save & Close and name it
Health Expenditure.
By pivoting columns to rows, we are able to use cross-tabbed Excel files that would otherwise need some ETL massaging, which is time consuming and—of course—prone to errors.