User Tools

Site Tools


bicn01:game_dfm

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

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

Employee Dimension

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 ModelForward 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 Hierarchies tab, the strategy of mapping a hierarchy from the dimensional model to a dimension table in the relational model is defined. We use the “Use Surrogate Key” strategy. This means, that an artificial key will be generated to be the primary key of the dimension table.

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.

One important exception are time dimension! You can of cause use a numeric data type as a key, but a “good” practice is to use a DATE data type. Why? A DATE data type has many unusual side effects. A professional database systems includes powerful functions which allows to add, subtract days or months from a given date. Let us compare the last 6 months from this year to the corresponding months from last year. This is a big challenge, if you use a NUMERIC data type as key, but a finger-snip, if you use a DATE data type as a key. Still unconfidenced? Asume, you store your current date, say, 20.07.2016 as 20160720. Which date is in 60 days? 20160780???

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.

This is something which is related to the default Microsoft SQL Server installation. The default name of a database ower is called DBO. The DBO is just a role. A role – here: DBO – has some privileges, which allows us to create or delete database objects. Any other name – for example DIMENSIONAL_MODEL – is unknown to SQL Server and the following steps will be blocked by the Database Management System.

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

  1. Download the SQL check file
  2. in Server Explorer, open a New Query window and copy-paste the sql-script.
  3. Execute it and check, if you don't get any message.

Summary

What have you done in this unit?

  1. your created 4 conceptual fact schemata:
    1. Monthly Sales
    2. Monthly Costs
    3. Monthly Marketing
    4. Monthly Stock
  2. you created 4 relational subject areas:
    1. DataMartSales
    2. DataMartCosts
    3. DataMartMarketing
    4. DataMartStock
  3. 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.

bicn01/game_dfm.txt · Last modified: 2019/11/11 08:49 by peter