In today’s self-serve Bl world, it’s common to need to analyze or mash up data from a huge variety of sources. Sometimes data arrives in a nice, clean and structured form, but it may also be delivered in a very basic flat file extract or single database tables. Power Query (also known as Get and Transform in Excel 2016) is a great way to shape data into a clear and more convenient form for analysis.
You may have to take a flat file data extract, perhaps pulled from some source database or a public data set, and shape it into a small star composed of a fact table surrounded by related dimension tables. In a Demo Day video, I’ve taken a slice of Microsoft’s new World Wide Importers SQL Server 2016 sample data and made a mock data extract, stored in a single CSV file, to emulate this scenario.
Making Dimensions and Facts
- Import your original data and identify columns to make into dimensions.
- For each of those dimensions, make a copy of the table that isolates the distinct values from the column(s), and numbers them with Add Index.
- Join the original data to the dimensions using the text columns for matching.
- Expand the matching ID values, and remove the corresponding text columns, to make a neat, compact fact table.
For more information or to learn more about analyzing data, contact 3Cloud today!