Flat files are unwanted in a BI process because they increase the processing load on the Power BI and therefore do not support quick analysis. This is particularly true for large data sets. The Processing engine loads the complete data set every time a query is run. This slows the visual loading process as the data set grows
But the good news !
A flat file can easily be converted into a data model. It just takes 4 simple steps to break down your flat file into a data model. Yes you got it right JUST FOUR STEPS.
Lets walk through each of them !
Flat files consist of a single table containing many columns. For this blog we will be using the Sales Analysis flat file shown in the image below.
A flat file consists of single table containing all the columns needed for analysis. The complete process can be broken down into 4 phases:
- Duplicating Query.
- Removing Columns.
- Removing Duplicates.
- Building Relationships.
Let’s classify each column as either a fact measure or dimension attribute.
- Let’s start the Process
Duplicating Queries
Duplicating a query is important for a flat file to be converted into a data model because initially a single query contains the entire data set.
To Duplicate Queries:
1. Right Click on Sales Analysis.
2. Click on
3. Right click on Sales Analysis again and click on
4. Rename Sales Analysis to Fact.
5. Rename Sales Analysis to Employees.
6. Rename Sales Analysis to Office.
Removing Columns
For a flat file to take the shape of data model, each query must contain only the required data. Therefore, extra columns must be removed.
To remove columns:
1.Click on the Fact query in the Queries section
2.From the Ribbon Menu, click on Home Tab
3.From the Column section, click on Choose columns.
A pop-up appears
4.Uncheck select all columns.
5.Now only select the check boxes to the left of OrderDate, SalesAmount, Employee ID and Office ID.
6.Click OK.
Fact table must only contain facts and keys that can are linked to other dimensions.
11.Click on the Employee.
12. Query in the Queries Section Pane.
13. From the ribbon, click on Home > Choose Columns.
Uncheck all the selected columns except Employee ID, Email Ids, Names, Office Id and Reports to.
14. Click OK.
4.Click on Office Query in the Queries section
5.Right click on Office ID, click on Remove duplicates.
6.Click on Close & Apply, in the close section.
A white line appears indicating a valid relationship. Power BI automatically detects the cardinality and primary and foreign keys present in the tables.
3. Hold the cursor and drag it from Office to Fact.
A Simple data model with one fact and two dimension tables has been created.
Conclusion:
A flat file can be converted into a simple data model by just following 4 simple steps. The first step is to duplicate your query in the query editor. The next step is to remove the extra columns from each query. For this step you must know which of the columns belong to the fact table and which belong to the dimension table. The next step is to remove the duplicates in the dimension tables. This fulfils the condition of distinct keys in the dimension table. The last and final step is to build relationship between your tables and you’re good to go !
If you liked our blog, drop your email in the comment section and get our blog updates!
Are you a data analyst and want to learn more about Power BI?
Why not sign up for Power BI training in Australia. We provide our services in the following regions:
· Power BI Training in Melbourne
· Power BI Training in Brisbane
· Power BI Training in Canberra
