This article is positioned to be an introduction that will demonstrate how to create a basic dimension/measure and how to add it to a cube. Our aim is to take a detailed approach that explains the logic behind our actions. This will hopefully provide foundation onto which you can create your own dimensions and measures that fit your specific needs.
The building of dimensions and measures can often become a complex subject matter. In this article, we will only touch upon some of its components. For a more thorough understanding, we recommend further training be obtained.
Before we dive into creating our dimensions and measures, it is useful to understand some basic concepts:
What is a Measure?
Simply put, a measure is a numeric value that is of interest.
For example: sales is an important numerical metric that we wish to measure and perform analysis on.
What is a Dimension Table?
A dimension table is an object in our data warehouse that contains our master data.
For example: if I want to see total sales by employee, I would want to store the employee data into a dimension table. Data that I might want to capture could include the employees Number, Name, Group, etc. This dimension table will share a relationship with our fact table.
What is a Fact Table?
A fact table is an object in our data warehouse that contains transactional data.
For example, if I want to see quantity sold by item, I would want to store the quantity data in my fact table. This fact table shares a relationship with our dimension table.
Defining Dimensions and Measures
Every industry has things it measures and wants to analyze. These things we wish to measure can be very simple or very complex. The main take away is to fully understand what you would like to measure and how you would like to view that data. A strategy that is often used is to look at certain pain points within your organization. The following will depict a pain point from a fictional company called Jet Corp.
Example: Define Measures and DimensionsJet Corp is a small retail company. The CEO of Jet Corp wants to reward its best salesperson (the salesperson who has had the most sales). The CEO has a pain point. He wants to measure the performance of his salespeople, but the current system doesn't track sales by salesperson. In this example, the Salesperson is our dimension (how we want to view the data) and Sales is our measure (our numerical value).
Building a Dimension Table
In this example, we are connecting to a NAV database, but the logic is the same regardless of your data source. We are also starting with a blank project to better demonstrate the logic.
Open the Jet Data Manager
Open your current project
Once opened, navigate to the Data Sources node and select the data source.
All data is fed into your project via a source database. We must locate the data from our source database and place it in our staging database.
Select the table that contains your data. In this example, our salesperson data resides in the Salesperson/Purchaser table. I am interested in knowing the salesperson's name, so I will select the Name field. I will also grab the Code field because I will need it to link to a fact table that we will create later. Also notice that the table has been added to my staging database.
It is a best practice to only add fields that you need. Adding unneeded fields will add to processing time.
You can preview a table by right clicking and selecting preview. This allows you to see what data is being selected.
You may notice that when you select any field in a table, a field called DW_Account is also selected (NAV and GP only). This is used to differentiate between companies when working in a multi-company environment. DW_Account must be selected if present.
Once the table is added to the staging database, you will need to deploy and execute that table. To do this, right click the table and select Deploy and Execute.
A dialog will appear. Click Start.
You must deploy and execute the table any time a structural change is made. For example, when you add another field, or you place a rule on the table.
Drag the dimension table from the staging database to the Tables node in the data warehouse.
Expand the table in the data warehouse. This will reveal the fields in the table. Rename the field names to something more meaningful. In this example, we changed DW_Account to Company as to better represent the contents of the field.
To change a table/field name, right click the object and select Edit.
It is a best practice to remove all special characters from table and field names.
Deploy and Execute the table in the data warehouse as we did above.
Building a Fact Table
Jet Corp wants to track the sales amounts for each of its salespeople, but the CEO is only concerned with the sale of items. This means we can exclude charge items, fixed assets, etc. We will, however, want to account for any credit memos to display a more accurate sales amount.
We know our fact table will contain our numerical data and a field that will link to our dimension table. Currently, we are only interested in looking at sales that have been posted. In our NAV database we will want to pull the sales amount from the following tables.
- Sales Cr.Memo Header
- Sales Cr. Memo Line
- Value Entry
We want to pull the data from all 3 tables because we want to take into account all facets regarding the posted invoice, such as credit memos.
Adding the Value Entry Table
Navigate to your data source and select the table that contains your numeric data. In our example, we have data in multiple tables. One of these tables is the Value Entry table. We want to grab the Sales Amount (Actual) field and the Salespers./Purch.Code field. Salespers./Purch.Code will link our dimension table to our fact table.
Deploy and Execute the table
Drag the table from the staging database up to the data warehouse
Go ahead and give the table and fields more meaningful names. In our example, we will name the table Posted Sales Transaction because that is what the table will contain.
Expand the field to view the data movements. We can tell that the data is being pulled from the Value Entry table and the associated fields in this table.
Adding the Sales Credit Memo Header and Line Tables
We have two tables that contain the data we need
- Sales Cr. Memo Header
- Sales Cr. Memo Line
The header contains the Salesperson Code field which we need to properly link our fact table to our dimension table. We also want to grab the No. field as this will be used to link the Header and Line tables.
The Line contains numerical values about our transactions. We want to grab the Amount field and the Document No. .Document No. will be used to link the header table to the line table.
(the No. field from the header = the Document No. field on the line)
Navigate to your data source and add the fields. Once your fields are selected, they will appear in the staging database.
We want our fact table to pull data from only the line and not the header because the line contains our numerical data. The problem is that the header also contains data that we need. To get data from one table to another, we can create a lookup field. To do this, we drag the Salesperson Code field from the header to the line (drag the field that contains the data you want from table A on top of table B). This will create a lookup field that displays in red.
We now have to tell the Jet Data Manager how these two tables relate to each other. This is done by creating a table join.
Expand your newly created lookup field all the way and right click and select Joins and then Add Join
In the Lookup field drop down, select the No. field from the header table. In the Field drop down select Document No. Click OK.
We have now logically joined the two tables together.
Go ahead and create another join. This time join DW_Account to DW_Account. This will ensure that there is no overlap if working in a multi-company environment.
Deploy and execute the Sales Cr. Memo Header table first and then the Sales Cr. Memo Line tableAs a best practice it is always advisable to preview the contents of a table before adding it to the data warehouse. This will allow you to view the content of the table and make changes as necessary. To preview a table, make sure it is fully deployed and executed. Then right click the table and select preview.
Window the Sales Cr. Memo Line table and drag the fields from the staging database to the corresponding field in fact table in the data warehouse.
Keyboard shortcut: Ctrl + W
Allows you to window any object(s) on the Data and Cubes tabs. Useful for easy navigation and manipulation of objects.
Deploy and execute the fact table.
Creating a Cube and Measure
We have successfully created our dimension table and our fact table. We can now proceed to create our cube and dimension.
Creating a Cube
Navigate to the Cubes tab.
Expand your OLAP Server, right click on Cubes, and select Add Cube
The Add Cube dialog will open. Select your fact table and enter a name for your cube. Click OK.
Creating a Measure
Expand the cube you just created, right click on Measures and select Add Standard Measure
The Add Measure dialog will open. Enter the appropriate information and click OK.
- Name: Assign a name for your measure
- Fact table: Select the fact table that contains your data
- Field: Select the field that contains your numerical data
- Type: Select the operator. In this example we want to sum the data
- Format string: Select how you want your measure formatted
There are many different format strings that can be applied to measure. For a list of possible formatting options, please consult the Jet Data Manager user manual
Creating a Dimension and Adding it to a Cube
From the Cubes tab, navigate to the Dimensions node, right-click on it, and select Add Dimension.
The Add Dimension dialog opens. In the Name field, enter a name for your dimension. Click OK.
The Dimension Level dialog opens. Enter the appropriate information → Click OK .
- Name: Enter the name of the dimension level as you would want it displayed to the end user. In this example, we will name it Salesperson.
- Key Table: Select the dimension table that contains your data
- Key Column: Select the field in your dimension table that relates to your fact table. In our example, the code field in the dimension table equals the Salesperson code in the fact table.
- Lay-Out: Select how you want your field to appear to the end user. In this example, we have a field called Name in our dimension table. This field contains the names of all our sales people and this is the value we want to display to end users.
- Name Table: Select the table that contains your data. This is usually the same table as the Key Table.
- Name Column: Select the field in your dimension table that contains the data you wish to display
- Sort By: Select how your results should be sorted. In this example we will sort by Name.
Make sure that your dimension has a key level. This is the level that we will use to link our dimension to our cube. In this example, our key level is our Salesperson level, which we stated above as the key column in our dimension level.
Adding a Dimension to the Cube
Once our dimension is created, we can add it to an existing cube.
Drag the dimension you want to add to the Dimension node within your cube. This will create a shell dimension.
Right click the shell dimension, select Dimension Relations, and then select the fact table you wish to link it to.
In this example, we want to link our dimension to our posted transactions fact table.
Under your fact table, select the field that links your dimension table to your fact table.
In this example, Salesperson Code in the fact table equals Code in our dimension table. Click OK.
The final step is to deploy and execute the OLAP Server.
When we look at our results via a pivot table, we can see the measure we created and the dimension. We can see our sales people and the total amount each has sold. It looks like Peter Saddow has the highest Sales Amount.