Data Modeling with DAX Tutorial
Data Modeling with DAX Resources
Selected Reading
- Rate Variance & Volume Variance
- Cost Per Headcount Measures & Analysis
- Variance Headcount Measures
- YoY Headcount Measures & Analysis
- Total Headcount Measures
- Average Headcount Measures
- Ending Headcount Measures
- Count of Months Measures
- Forecast Measures & Analysis
- Budget Measures & Analysis
- Quarter-to-Date Measures & Analysis
- Year-to-Date Measures & Analysis
- Variance Measures & Analysis
- YoY Finance Measures & Analysis
- Base Finance Measures & Analysis
- Extending the Data Model
- Understanding Data Tables
- Defining Data Types in the Data Model
- Loading Data into the Data Model
- Data Modeling Using Excel Power Pivot
- Data Modeling with DAX - Concepts
- Data Modeling with DAX - Overview
- Home
Data Modeling with DAX Resources
Selected Reading
- Who is Who
- Computer Glossary
- HR Interview Questions
- Effective Resume Writing
- Questions and Answers
- UPSC IAS Exams Notes
Defining Data Types in the Data Model
Defining Data Types in the Data Model
In the Power Pivot data model, the entire data in a column must be of the same data type. To accomppsh accurate calculations, you need to ensure that the data type of each column in each table in the data model is as per requirement.
Tables in the Data Model
In the data model created in the previous chapter, there are 3 tables −
Accounts
Geography Locn
Finance Data
Ensuring Appropriate Data Types
To ensure that the columns in the tables are as required, you need to check their data types in the Power Pivot window.
Cpck a column in a table.
Note the data type of the column as displayed on the Ribbon in the Formatting group.
If the data type of the selected column is not appropriate, change the data type as follows.
Cpck the down arrow next to the data type in the Formatting group.
Cpck the appropriate data type in the dropdown pst.
Repeat for every column in all the tables in the data model.
Columns in the Accounts Table
In the Accounts table, you have the following columns −
| Sr.No | Column & Description |
|---|---|
| 1 | Account
Contains one account number for each row. The column has unique values and is used in defining the relationship with the Finance Data table. |
| 2 | Class
The class associated with each account. Example - Expenses, Net Revenue, etc. |
| 3 | Sub Class
Describes the type of expense or revenue. Example – People. |
All the columns in the Accounts table are of descriptive in nature and hence are of Text data type.
Columns in the Geography Locn Table
The Geography Locn table contains data about each Profit Center.
The column Profit Center contains one profit center identity for each row. This column has unique values and is used in defining the relationship with the Finance Data table.
Columns in the Finance Data Table
In the Finance Data table, you have the following columns −
| Column | Description | Data type |
|---|---|---|
| Fiscal Month | Month and Year | Text |
| Profit Center | Profit Center identity | Text |
| Account | Account number. Each account can have multiple Profit Centers. |
Text |
| Budget | Monthly budget amounts for each Profit Center. | Currency |
| Actual | Monthly actual amounts for each Profit Center. | Currency |
| Forecast | Monthly forecast amounts for each profit center. | Currency |
| Actual People | Month end actual number of employees for each Profit Center of each people Account. | Whole Number |
| Budget People | Month end budget number of employees for each Profit Center of each people Account. | Whole Number |
| Forecast People | Month end forecast number of employees for each Profit Center of each people Account. | Whole Number |
Types of Tables in the Data Model
Both Accounts and Geography Locn tables are the dimensional tables, also called as lookup tables.
Finance Data table is the fact table, also known as the data table. Finance Data table contains the data required for the profit and analysis calculations. You will also create metadata in the form of measures and calculated columns in this Finance Data table, so as to model the data for various types of profit and loss calculations, as you proceed with this tutorial.
Advertisements