PowerBI Workshop
===
## Day1(1.5hours)
:::success
Agenda
1. Short introduction about BI tools trend
- architecture among tableau,powerbi,superset
2. Dataload
- load from csv file
- load from database
- difference between import mode and direct mode

3. Build Dashboard(power desktop)
- data modeling(join table)
- Visualise as simple table or bar chart

4. Publish Dashboard(power service)
- publish dashboard to powerbi service
- how to share dashboard to other people

:::
### Short introduction about BI tools trend
- Famous
- commercial: Powerbi, tableau, etc
- opensource: Superset, etc

- Trend
- Lot of company switch to powerbi cause ework(ms365)
- Comparison

| Product | Architecture | Feature | OS |
| -------- | ------------- | -------------------- | -------------------- |
| Tableau | Client+WebServer | No1 bi tool | LINUX AND WINDOWS |
| PowerBI | Client+WebServer | Improve lot this few years| ONLY WINDOWS |
| Superset | Web-based | friendly to engineer |LINUX AND WINDOWS|

### Dataload(power desktop)
- dataset download(superstore)
- download below excel first
- https://community.tableau.com/s/question/0D54T00000CWeX8SAL/sample-superstore-sales-excelxls
- The SuperStore dataset which contains the data about the superstore product orders in the US. It includes various columns like order date, shipping date, the product ordered, state, region, etc.
- load data from csv
- 
- 
- You can see powerbi does not reconize header well sometimes
- 
- No worry, press [use first row as header] to solove this problem
- 
- Transform data
- Change column type
- Copy column
- Split column
- Merge column
- Add new column by using dax(introduce details in day2)
- preview the table
- 
- load data from database
- install odbc driver first
- https://mariadb.com/downloads/connectors/
- 
- 
- 
- 
- 

- Why load from db is better?
- security(data leak)
- automatic data fresh
- performance(big size table)

> Lets migrate kpi analysis from excel to dashboard

### Build Dashboard(power desktop)

- data modeling(join table)
- join different tables
- 2 ways to join table
- drag the same column
- Configure at [Manage relationships]
- 
- Cardinality
- **One-to-many (1:*)**: This means that one row in a table can be related to many rows in another table. For example, one customer can have many orders. This is similar to a **left join** in SQL, where all rows in the left table are included, and matching rows in the right table are included.
- **Many-to-one (*:1)**: This means that many rows in a table can be related to one row in another table. For example, many orders can be associated with one customer. This is similar to a **right join** in SQL, where all rows in the right table are included, and matching rows in the left table are included.
- **One-to-one (1:1)**: This means that one row in a table can be related to only one row in another table, and vice versa. For example, one employee can have only one employee record. This is similar to an **inner join** in SQL, where only matching rows in both tables are included.
- **Many-to-many (:)**: This means that many rows in one table can be related to many rows in another table. For example, many customers can have many orders. This is not directly equivalent to any of the SQL join types, but it is usually implemented using a bridge table that connects the two tables with two one-to-many relationships.(https://learn.microsoft.com/en-us/power-bi/transform-model/desktop-relationships-understand)
:::spoiler
Let's say you have two tables, one for students and one for classes, and each student can enroll in many classes, and each class can have many students. This creates a many-to-many relationship between the two tables.

To model this relationship in Power BI, you would typically create a third "bridge" table that connects the two tables with two one-to-many relationships. This bridge table would contain the foreign keys from both the student and class tables.


In this example, the Enrollment table acts as the bridge table that connects the Student and Class tables. It has two columns, StudentID and ClassID, that serve as foreign keys to the primary keys in the Student and Class tables, respectively.

By creating this many-to-many relationship between the Student and Class tables through the Enrollment table, you can now analyze data across both tables, such as how many students are enrolled in each class, which students are enrolled in which classes, and so on.
:::

- 
- 
- https://powerbidocs.com/2021/02/15/cardinality-of-relationship-in-power-bi/#:~:text=Power%20BI%20The%20cardinality%20of%20the%20relationship%20means,it%20refers%20to%20the%20relationship%20between%20two%20tables.

- Visualise as simple table or bar chart
- Title(text box)
- 
- Card
- 
- 
- Filter
- 
- 

- Bar Chart
- 
- Donut Chart
- 

### Publish Dashboard(power service)

- publish dashboard to powerbi service
- When you press publish button, you will be asked to save your pbxi file first
- 
- Workspace is a space to publish dashboard, Chose project workspace or [my workspace]
- 
- open it by click here
- 
- how to share dashboard to other people
- 

---
## Day2(1hours)
:::success
- Visualization
- Area charts(summarize profit by state)
- Pie chart/Shape map(summarize profit by subcatagory)
- decomposition tree(decomposite profit by city,catagory,subcatagory)
- How to search graph contributed by company/individule over the world
- Create calculated columns
- column
- measure

- Summary the difference about below keywords
- DAX
- PowerQuery
:::
### Visualization
- Area charts(summarize profit by state)
- Pie chart/Shape map(summarize profit by subcatagory)

- decomposition tree(decomposite profit by city,catagory,subcatagory)
- https://learn.microsoft.com/en-us/power-bi/visuals/power-bi-visualization-decomposition-tree
- 
- Lets check sum profit accross the city,catagory,subcatagory
- 
- 
- Others
### Create columns/measure in dax
:::danger
- Data Analysis Expressions
- Dax is necessary when create column/measure
- [DAX Reference](https://learn.microsoft.com/en-us/dax/)
- [Learn DAX basics](https://learn.microsoft.com/en-us/power-bi/transform-model/desktop-quickstart-learn-dax-basics)
:::

- Columns
- Change dollor to japanese yen in column
- formulas
- japanses_yen_column = Orders[Profit]*136
- https://learn.microsoft.com/en-us/power-bi/transform-model/desktop-calculated-columns
- 
- 
- 
- 

:::danger
Feature(Columns)
- New column added in table
- manipulation/calculation will be apply for each row
- Dimension is not necessary
- We can reuse it in other calculation like a column
:::

- Measures
- Change dollor to japanese yen in measure
- formulas
- japanese_yen_measure = SUM('Orders'[Profit])*136
- https://learn.microsoft.com/en-us/power-bi/transform-model/desktop-measures
- 
- 

:::danger
Feature(Measures)
- No column added in table
- manipulation/calculation will be apply for whole columns
- Dimension is necessary
- We do not reuse it in other calculation(visulize it directly)
:::

- Transform data
- we can not found the column we created in transform data page
- it shows up in data view

### Summary the difference

- Layer
1. SourceData(load from csv/db)
2. PowerQuery(Transform data) ⇇　M language(excel)
3. Data view(column/measure) ⇇　Dax(data analysis language)

- Advice
- new column could be created in both Transform data(m language) and Data view(dax), choose the language you are familiar in.
- if you only want to do calculation and visulize it, its better to use measure rather than column(new column will make the dataset more heavey)

- Calculate Gross_profit_margin in measture
- Gross_profit_margin = DIVIDE(SUM('Orders'[Profit]),SUM('Orders'[Sales]))
- matrixtable
- Click profit ratio and change to percentage
- 

---
## Day3(1hour)
:::success
Hands-on time

:::
### Data transformation part
- [x] Load data from csv
- [x] [download link](https://community.tableau.com/s/question/0D54T00000CWeX8SAL/sample-superstore-sales-excelxls)
- [x] Set frist row as header
- [x] join tables(sales,return,people)
### Dashboard part
- [x] Create title
- [x] Create total sales
- [x] Create total orders(distinct)
- [x] Filter(by catagory)
- [x] Bar chart(sum sales by city)
- [x] Area charts(summarize profit by state)
- [x] Shape map(summarize profit by subcatagory)
- [x] decomposition tree(decomposite profit by city,catagory,subcatagory)
- [x] Measure(Gross_profit_margin)
- [x] Gross_profit_margin = DIVIDE(SUM(Orders[Profit]),SUM(Orders[Sales]))
- [x] Display it in Matrix table(Using order date)
### Publish part
- [x] Save and naming the file by your name
- [x] Publish it on workspace