DARTS : Flat file to dimension model example

Data can be visualized in Excel, however, there are some limitations in Excel that Power BI can fix. The below is the example when you have a flat file and make it to dimension model to perform better in Power BI.

Background of this project:

The usage of Box is needed to be visualized. Here are some issues that Excel cannot handle easily.

Data is too big (7 GB, 35 millions rows)
Level of the tree (hierarchy) is complicated
need to add classification (faculty/staff/ student)

Goal:

Create star schema data model based on the current flat file.

Steps:

Data set up:

Make sure data is accessible and check the data.

The data is in SharePoint as csv file
Connect data in PowerBI Desktop (since this data will NOT be refreshed, used get data> csv option)
click Transform data
check column quality box
Change date column
1. Since there are "N/A" values, replace them to null.
2. change type from abc → date

Creating dimensions:

Once you figure out which fields can go to dimension table, use reference function to create dimensions.

Create path Dimension
1. right-click on the base table and click "reference"
3. rename the new table (folder)
4. remove columns
5. remove duplicate
6. split column by delimiter
Create File Dimension
1. right-click on the base table and click "reference"
2. rename new table to file name
3. remove columns (file_name & file_id)
4. Remove duplicate
Go back to the base table and remove the columns (path_id, file_name & file_id)
Create Box Data table
1. right-click on the base table and click "reference"
2. rename new table to "Box Data"
Replace slash ( / ) with pipe( | )
1. right-click path_ids column and put values on the boxes
3. Remove the first pipe by using "Extract" function
6. replace with "10" for the last characters
8. delete 10 and type Text.Length( [path_ids ] )
10. = Table.AddColumn(#"Filtered Rows", "First Characters", each Text.Start([path_ids], Text.Length([path_ids]) - 1), type text)
12. extract > text after delimiter with 5
14. change the column name to "path"
Go to Box data table and remove file name & folder name columns
1. remove null date
Go back to "Home" and "Close and Apply"
Create Calender table (moved to Create Date/ Calendar table)
1. File > Options > Data Load > uncheck the box for auto date/time
2. Check the earliest date and latest date by dragging the field on the visualization pane
3. Go to the Modeling tab on the ribbon
4. Go to table pane and right-click the Calendar table, then choose Mark as date table
5. add new column and create "year"
6. Go to modeling pane and create relationships
1. Go back to the visualization pane and create a new measure
3. create some table
  1. Choose Don't summarize
5. this guy shows count=2
7. Copy value > edit query > filter row
10. change "=" to "<>"
12. filter null
14. GO back to relationships
New Measure: file size
2. hide the original column (size) by right-clicking the field
New Measure: number of files (distinct)
New Measure: size (MB)
Go back to visualization, and drag fields to create some visuals
Go to Data view and click folder and create new column: Hierarchy depth
2. data looks like this
4. the earlier steps (changing slash to pipe) were needed because of this
5. New Column: level 1
  2. error; insert text before the delimiter then change path_ids to path
  4. null is the problem
  6. right-click the cell > replae value
Open Chrome and search for DAX formatter(daxformatter.com)
1. copy the DAX from PBI Desktop
2. Paste the code on the website
3. copy the DAX from website and paste it on PBI Desktop
create new columns: Level 2, Level 3, ....Level 23.
2. create a hierarchy by dragging level 2 over level1 and so on...
Change the source (need full data now)
1. Edit query
2. click source
4. add new path
6. error: comma on data breaks the report without double quotes (")
  1. right-click on file_owner data cell > text filter
  3. enable data for box_data partial (2) and folder, and box data
  5. still error
  7. Tried to open the data file with Notepad ++ but the file is too big to open
  8. These five tows are the errors
  10. modify deleted column
  11. still error

Notes:
1. Integer is better than text
2. PBI changes data type based on the top 1000 rows. Change to "based on entire data set" on the bottom left- corner of the Query Editor. Look at the data by sorting the values.
Error:
1. click the error cell
3. data in this row got shifted due to the double quote (") in data
5. Solutions: Type CsvStyle=CsvStyle.QuoteAlways

Resource:

Understand star schema and the importance for Power BI