Data Model Editor, explained

In this article we describe how to:

1. Setup a connection to a relational database in TARGIT Management
2. Use the data model editor to create a Data Model on some tables from the Relational Database
3. Finish up by creating a table in TARGIT based on data from the Database.

Setting up the connection

The Data Model Editor allows you to build a Data Model, including dimensions, measures, and calculations for a Relational Database or TARGIT InMemory database.

Before using the Data Model editor, you must establish a connection to your Database. 

Below is shown an example of connecting to a Relational Database - please refer to this article for connecting to a TARGIT InMemory Database:
https://community.targit.com/hc/en-us/articles/360017204498-Creating-a-Data-Model 

Open TARGIT Management:

Pick the Connection tab  - right-click in the blank area and choose Add...

Choose Relational Database

Below is a list of the supported database platforms - for this example Microsoft SQL Server 2012-2019 is selected:

Fill in Connection Name and Descripton (optional) and click Next

Fill in the Server name and pick your Database from the Database drop-down (with Windows Authentication ticked, your permissions to access certain Databases relies on the permissions of the TARGIT Service Account)

Click Create, and you have created a connection - now you are ready to build a Data Model for this connection.

Creating the Data Model

Right-Click on the name of your Connection in TARGIT Management and choose Edit data model

Now the Data Model Editor opens

In the left-hand side panel - the tab Schema is active and shows a list of all the tables included in the Database you have connected to.

Now we scroll in the table list and drag 2 tables to the canvas (dCompany and fSalesinvoiceline)

The 2 tables show invoice information with amounts, quantities etc. (fSalesinvoiceline) and additional information on the Companies involved in the Invoices (dCompany).

Doing a preview (clicking the table Icon of each table) show the content of these 2 tables:

dCompany:

fSalesinvoiceline (not all columns shown):

dCompany is a Dimension Table with a unique column (Company ID), while fSalesinvoiceline is a Fact table where dCompany ID is repeated for every invoiceline that involves this Company ID.

The definition of a Dimension table:

• A table that has a unique identifier (primary key), and holds extra information that enriches the information on the numbers from the Fact Table.
• A dimension table has NO measures.
• All dimensions in a data model should be based on a Dimension table.

The definition of a Fact table:

• A table that holds numbers (measures) we wish to analyze 
• A table that often has no unique identifiers (primary key) - but only references (foreign keys) to dimension tables 
• It is not best practice to base dimensions on fact tables  - dimensions should only be based on dimension tables

Relations (many-to-one)

The dCompany_ID from fSalesinvoiceline is related to the ID from dCompany.

We drag the relation from the dCompany_ID in our Fact table to the ID in Dimension table (from many-to-one).

Notice the direction of the relation (from the fact table to the dimension table).

Click New cube from the toolbar

A cube in the data modeler always has one and only one fact table. 

Now we choose which table is the fact table in this cube (fSalesInvoiceline), name the cube and click OK.

Now we can see our cube on the left-hand side by picking the cube tab (next to the schema tab).

And we can expand it to see the folders for measures, dimensions etc.

Notice there are 2 folders called Shared dimensions.

Dimensions can exist independently from a cube (which is represented by the lowest Shared dimensions folder) - or directly placed inside the cube (which is represented by the upper Shared dimensions folder).

Best practice: Add the dimensions to the lower Shared dimensions folder, and then later include which dimensions from the Shared folder you wish to include into your Cube.

Now we drag the Company column from dCompany to the lower Shared dimensions folder - so now it looks like this:

Also we drag a few columns from fSalesinvoiceline to the Measures folder: Amount and Quantity

Finally we right-click the upper Shared dimensions folder and choose Add

There's currently just one dimension that can be added to our cube - so we tick that one and click OK.

If we expand our upper Shared dimensions folder - we can see the full content of the Invoices cube:

Now we click Save - open the TARGIT Windows client and are able to build a x-tab based on this data model.