Star models from Excel files or text files

Creating star models from Excel files or text files is simple, I will explain it to you step by step.

Many times our data source is an Excel file, a text file, a CSV file, a flat file or any other format consisting of a large number of rows and columns. What we commonly know as “Excel sheets”.

Why do we usually use these “Excel sheets” and not with Star Models?

Because this is the ideal format to load them into Excel and work with pivot tables and dynamic charts.

Because we have been working with Excel for many years.

Because it is an effective way of working with Excel, given the limitations of pivot tables, which can only have a range of cells as the data source and this forces us to have all those rows and columns next to each other on the same Excel sheet.

In short: “out of ignorance”.

But it is not efficient or recommended in Power BI. Plus, it will cause problems that you won’t be able to solve…until you redesign and convert the set of rows and columns you have in a single table to a star model.

Why use Star Models instead of a single table?

Because it is the most efficient template for Power BI.

Power BI is an analytical database optimized to work with Star Models.

And it needs a set of tables containing the characteristics by which we need to filter, segment and describe our indicators. Those tables are the dimension tables, the peaks of the star.

You also need some tables that store the history of what happened, fact tables.

But of course, when you open Power BI you don’t find anywhere any property, feature or functionality where you tell tables whether they are Facts or Dimensions. So it’s one of the big unknowns.

And, in turn, that’s the biggest mistake you can make: not modeling like a star.

How to create star models from Excel files or flat files

I’ll tell you more about it in this video, with a practical step-by-step example, where I start from an Excel file, with a single table, made up of many columns and thousands of rows.

And I’m going to turn this into a star model, with its own fact tables and its own dimension tables.

???? In this video I show you, using an example, how to generate a star data model, optimal for Power BI, composed of 3 tables from a single source file.

And remember! ☝️Never settle for loading data as it originally is, be it files, databases or anything else. Design a good model and use Power Query to make the necessary transformations and create an optimal “Star” model for Power BI

0:00 Start of the video
0:13 From the “Excel sheet” to the star model
0:55 Practical example
1:15 Create a sales table
1:43 Delete columns
2:29 What do I need to do to connect the columns?
4:33 Primary key and duplicate removal
5:45 Extract product data
6:25 Organize the columns
7:14 Remove duplicates
7:45 Product table name
8:09 Close and apply
9:06 Delete relationships
9:16 Create a star with 3 tables
9.31 Conclusion

You now have a star model from flat files

Everything is ready to carry out the analyzes you need.

Thanks, not only, to Power Query, which allowed us to carry out all the necessary transformations. But because we were able to design the optimal star model for the analysis based on the data source we have.

If you want to work with Power BI like we professionals do and avoid many unnecessary problems that block you and prevent you from finding the solution you need, you need to train in Data Modeling in Estrella.

Here is a link to my course “Data Modeling in Estrella – essential”

You will totally change the way you analyze your data thanks to Star Models.