- Data Modeling with DAX Tutorial
- Home
- Data Modeling with DAX - Overview
- Data Modeling with DAX - Concepts
- Data Modeling Using Excel Power Pivot
- Loading Data into the Data Model
- Defining Data Types in the Data Model
- Understanding Data Tables
- Extending the Data Model
- Base Finance Measures & Analysis
- YoY Finance Measures & Analysis
- Variance Measures & Analysis
- Year-to-Date Measures & Analysis
- Quarter-to-Date Measures & Analysis
- Budget Measures & Analysis
- Forecast Measures & Analysis
- Count of Months Measures
- Ending Headcount Measures
- Average Headcount Measures
- Total Headcount Measures
- YoY Headcount Measures & Analysis
- Variance Headcount Measures
- Cost Per Headcount Measures & Analysis
- Rate Variance & Volume Variance
- Data Modeling with DAX Resources
- Quick Guide
- Useful Resources
- Discussion
Data Modeling with DAX - Concepts
Business Intelligence (BI) is gaining importance in several fields and organizations. Decision making and forecasting based on historical data have become crucial in the evergrowing competitive world. There is huge amount of data available both internally and externally from diversified sources for any type of data analysis.
However, the challenge is to extract the relevant data from the available big data as per the current requirements, and to store it in a way that is amicable for projecting different insights from the data. A data model thus obtained with the usage of key business terms is a valuable communication tool. The data model also needs to provide a quick way of generating reports on an as needed basis.
Data modeling for BI systems enables you to meet many of the data challenges.
Prerequisites for a Data Model for BI
A data model for BI should meet the requirements of the business for which data analysis is being done. Following are the minimum basics that any data model has to meet −
The data model needs to be Business Specific
A data model that is suitable for one line of business might not be suitable for a different line of business. Hence, the data model must be developed based on the specific business, the business terms used, the data types, and their relationships. It should be based on the objectives and the type of decisions made in the organization.
The data model needs to have built-in Intelligence
The data model should include built-in intelligence through metadata, hierarchies, and inheritances that facilitate efficient and effective Business Intelligence process. With this, you will be able to provide a common platform for different users, eliminating repetition of the process.
The data model needs to be Robust
The data model should precisely present the data specific to the business. It should enable effective disk and memory storage so as to facilitate quick processing and reporting.
The data model needs to be Scalable
The data model should be able to accommodate the changing business scenarios in a quick and efficient way. New data or new data types might have to be included. Data refreshes might have to be handled effectively.
Data Modeling for BI
Data modeling for BI consists of the following steps −
- Shaping the data
- Loading the data
- Defining the relationships between the tables
- Defining data types
- Creating new data insights
Shaping the Data
The data required to build a data model can be from various sources and can be in different formats. You need to determine which portion of the data from each of these data sources is required for specific data analysis. This is called Shaping the Data.
For example, if you are retrieving the data of all the employees in an organization, you need to decide what details of each employee are relevant to the current context. In other words, you need to determine which columns of the employee table are required to be imported. This is because, the lesser the number of columns in a table in the data model, the faster will be the calculations on the table.
Loading the Data
You need to load the identified data – the data tables with the chosen columns in each of the tables.
Defining the Relationships Between Tables
Next, you need to define the logical relationships between the various tables that facilitate combining data from those tables, i.e. if you have a table – Products - containing data about the products and a table - Sales - with the various sales transactions of the products, by defining a relationship between the two tables, you can summarize the sales, product wise.
Defining Data Types
Identifying the appropriate data types for the data in the data model is crucial for the accuracy of calculations. For each column in each table that you have imported, you need to define the data type. For example, text data type, real number data type, integer data type, etc.
Creating New Data Insights
This is a crucial step in date modeling for BI. The data model that is built might have to be shared with several people who need to understand data trends and make the required decisions in a very short time. Hence, creating new data insights from the source data will be effective, avoiding rework on the analysis.
The new data insights can be in the form of metadata that can be easily understood and used by specific business people.
Data Analysis
Once the data model is ready, the data can be analyzed as per the requirement. Presenting the analysis results is also an important step because the decisions will be made based on the reports.