Construct a Data Mart
From Conceptual Model → logical model → physical model.
A dimensional scheme consists of a set of fact schemes. The components of fact schemes are facts, measures, dimensions and hierarchies. In the following an intuitive description of these concepts is given.
- A fact is a focus of interest for the decision-making process; typically, it models an event occurring in the enterprise world (e.g., sales and shipments).
- Measures are continuously valued (typically numerical) attributes which describe the fact from different points of view; for instance, each sale is measured by its revenue.
- Dimensions are discrete attributes which determine the minimum granularity adopted to represent facts; typical dimensions for the sale fact are product number, store name or date.
- Hierarchies are made up of dimension attributes linked by -to-one relationships, and determine how facts may be analysed for the decision-making process. The dimension in which a hierarchy is rooted defines its finest aggregation granularity; the other dimension attributes define progressively coarser granularities. A hierarchy on the product dimension will probably include the dimension attributes product type, category, department, department manager. Hierarchies may also include non-dimension attributes. A nondimension attribute contains additional information about a dimension attribute of the hierarchy, and is connected by a -to-one relationship (e.g., the department address).
Create a Multidimensional Model
We will now create the multidimensional model with Dimensional Fact Model (DFM)
Import the BIM template
- Download the template file and unzip it.
- Start the BI Modeler and open the template with File → Open
Create Dimensions (DFM: Hierarchies)
From the menu, select Dimensional Fact Model → New Hierarchy.
Create the following dimensions:
Employee (key attribute: Employee Number)
- An employee is identified by Employee Number.
- An employee has an Name
- An employee has a position
Product (Key attribute: Material Number)
Store (Key attribute: Store Name)
Time (Key attribute: Calendar Month ISO)
Campaign (Key attribute: Campaign Name)
Cost Record (Key : Cost Category)
Create the FactSchema
Create the fact schema for sales
Name it Monthly Sales.
Add the following measures to the fact schema:
- Revenue
- Discount
- Sales Amount
- Transfer Price
- Gross Profit
Connect the dimensions (hierarchies) to your fact schema.
- Employee
- Time
- Product
- Campaign
- Store
Create the fact schema Costs
Name it Monthly Costs.
- Cost Value
Connect the dimensions (hierarchies) with your fact schema.
- Time
- Cost Record
- Store
Create the fact schema Marketing
Name it Monthly Marketing.
- Marketing Costs
Connect the dimensions (hierarchies) with your fact schema.
- Time
- Campaign
- Store
Create the fact schema for stock
Name it Monthly Stock
- Stock Amount
Connect the dimensions (hierarchies) with your fact schema.
- Time
- Product
- Store
Create a Logical Data Model
Switch to the Relational Model.
Create a new physical model. Name it SalesDataMart. Choose Microsoft SQLServer as your target database.
Now, create the logical model.
Under Relational Model → Forward Engineering, under Domains, map the domain to a (database) data types.
- Amount → NUMERIC
- Longtext → NVARCHAR(200)
- Shortext → NVARCHAR(50)
- Money → MONEY
- Date → DATE
- Number → NUMERIC
- Float Number → FLOAT
The next step is really important!
In the column “P(rimary) K(ey) Type”, all keys data types are set to NUMERIC, because a numerical data type is the fastest data type.
Please change the data type of the D_TIME dimension to DATE.
BUG 
If you click Apply or OK, the data type will not change.
Move the cursor out of the field and click in any other cell.
Then, click OK
Ready! Click OK.
Check, if the object ower is dbo.
DataMart Sales
Create a new subject area. Name it DataMartSales and drag-and-drop the following 6 database objects into the subject area.
- F_MONTHLY_SALES
- D_STORE
- D_PRODUCT
- D_TIME
- D_EMPLOYEE
- D_CAMPAIGN
DataMart Marketing
Create a new subject area. Name it DataMartMarketing and drag-and-drop the following 4 database objects into the subject area.
- F_MONTHLY_MARKETING
- D_STORE
- D_TIME
- D_CAMPAIGN
DataMart Costs
Create a new subject area. Name it DataMartCosts and drag-and-drop the following 4 database objects into the subject area.
- F_MONTHLY_COSTS
- D_STORE
- D_TIME
- D_COST_RECORD
DataMart Stock
Create a new subject area. Name it DataMartStock and drag-and-drop the following 4 database objects into the subject area.
- F_MONTHLY_STOCK
- D_STORE
- D_TIME
- D_PRODUCT
Create a Physical Model
Click on the new Physical Model for SQL Server (SalesDataMart) and generate the DDL (Data Definition Language).
Click on dbo and on the green + icon. Click OK.
A window with SQL DLL code pops up.
Copy the SQL code.
Open SQL Server Data Tools from the windows start menu, View → Server Explorer. Create a new connection to
- Server: NO RDB
- Use SQLServer Authentification
- Username: NO SID
- Password: NO PAS
- Database: NO SID_DATAMART
Click on your database and open a new query window, in order to run an SQL script.
Copy the SQL DLL-Script into the query window and execute the SQL code.
Check, it the database table exists.
Quality gate: Check your relational model
- Download the SQL check file
- in Server Explorer, open a New Query window and copy-paste the sql-script.
- Execute it and check, if you don't get any message.
Summary
What have you done in this unit?
- your created 4 conceptual fact schemata:
- Monthly Sales
- Monthly Costs
- Monthly Marketing
- Monthly Stock
- you created 4 relational subject areas:
- DataMartSales
- DataMartCosts
- DataMartMarketing
- DataMartStock
- you deployed the subject areas into your data mart database on server NO EDB
You are now able to connect to the database from Excel or any other data query tool.
