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.
- Building a Dimension Table
- Building a Fact Table
- Creating a Cube and Measure
- Creating a Dimension and Adding it to a Cube
Before we dive into creating our dimensions and measures, it is useful to understand some basic concepts.
What is a Dimension?
Simply put, a dimension is a category that provides a consistent view of data. You can think of a dimension as how you would want to view your numerical data. Dimensions are how we want to see our data, and can come in many different flavors.
For example: sales by customer. Sales represents our numerical data and customer represents our category.
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 Dimensions
Jet 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).
1. Open the Jet Data Manager
2. Open your current project
3. 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.
4. 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.
5. 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 → Deploy and Execute .
A dialog will appear. Click Start . Close the dialog, once finished.
7. 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.
8. Deploy and Execute the table in the data warehouse as we did above.
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
1. 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.
2. Deploy and execute the table.
3. Drag the table from the staging database up to the data warehouse
This will be the start of our fact table.
4. Go ahead and give the table and fields a more meaningful name. In our example, we will name the table Posted Sales Transaction because that is what the table will contain.
5. 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
1. 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.
Navigate to your data source and add the fields. Once your fields are selected, they will appear in the staging database.
2. 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.
3. Expand your newly created lookup field all the way and right click Joins → Add Join
4. 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.
5. 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.
6. Deploy and execute the Sales Cr. Memo Header table first and then the Sales Cr. Memo Line table
7. 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 tab. Useful for easy navigation and manipulation of objects
8. Deploy and execute the fact table.
We have successfully created our dimension table and our fact table. We can now proceed to create our cube and dimension.
Creating a Cube
1. Navigate to the Cubes tab.
2. Expand your OLAP Server can right click Cubes → Add Cube
3. The Add Cube dialog opens. Select your fact table and enter a name for your cube. Click OK .
Creating a Measure
1. Expand the cube you just created and right click Measures → Add Standard Measure
2. The Add Measure dialog opens. 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
1. From the Cubes tab, navigate to the Dimensions node → Add Dimension .
2. The Add Dimension dialog opens. In the Name field, enter a name for your dimension. Click OK .
3. 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.
4. 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 Dimension to the Cube
Once your dimension is created, we can add it to an existing cube.
1. Drag the dimension you want to add to the Dimension node within your cube. This will create a shell dimension.
2. Right click the shell dimension → dimension relations → 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.
3.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 .
4. 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.
For instructions to access the cubes see Connecting to the cubes