Spreadsheet Add-Ins: Star Schemas in PowerPivot

A powerful new add-in for Excel 2010, PowerPivot allows users to quickly analyse huge datasets, while staying in the familiar environment of Excel. It’s an easy way to create reports, using pivot tables and pivot charts, and new Excel 2010 features, like Slicers and Sparklines.

On the Gobán Saor (Country Datasmith) blog, Tom Gleeson describes the role that Star Schemas play in PowerPivot, and outlines their advantages. First, he explains a Star Schema in simple terms as, “a central fact table with links back to a “surrounding” set of dimensional tables, hence the star name.” The fact table holds most of the data, and provides the links between all the other tables.

If you’re used to creating pivot tables in Excel, but don’t have a data warehouse background, you might not realize why Star Schemas can add value. Tom explains that Star Schemas add new potential to your data analysis:

“It is true that the same flattened table model as used to backend a PivotTable can be used within PowerPivot. But doing so would limit the potential of the DAX language to construct measures such as average sales spread over potential customers (rather than actual customers that would typically be represented on a flattened table). Also, by creating “conformed dimensions” (single cross-business views of Customer, Product etc.) and using such tables as dimensional sources for multiple fact tables, “virtual cubes” that combine values from multiple fact tables can be built.”

Learn More About PowerPivot

You can explore PowerPivot in the virtual lab, as I did, or download a free copy and try it for yourself, in Excel 2010 (not available for earlier versions).

__________________