# Data Model

{% hint style="warning" %}

#### Workshop - Data Model

Data modeling is the foundation of effective business intelligence and analytics. In this comprehensive workshop, you'll master the Data Source Wizard, learning how to transform raw data from CSV files and relational databases into well-structured, analysis-ready data models that power Pentaho's reporting and analysis tools. Whether you're working with simple spreadsheet data or complex star schema databases, you'll gain the skills to create data sources that enable meaningful business insights through Analyzer, Interactive Reports, and Dashboard reports.

In this hands-on workshop, you'll experience the complete data modeling lifecycle, starting with simple CSV file imports and progressing to sophisticated multidimensional models built on relational database tables. You'll learn how to configure data source properties, define staging settings, and design effective data models for both reporting and analysis purposes. As you work through the exercises, you'll master critical concepts including star schema design, dimension hierarchies, measure aggregations, and time dimension properties. You'll also develop the expertise to optimize your data models for performance and usability, ensuring that business users can easily explore and analyze their data with confidence.

**What You'll Accomplish:**

* Launch and navigate the Data Source Wizard interface in Pentaho User Console
* Create data sources from CSV files with proper encoding and delimiter configuration
* Import and configure database tables with appropriate joins and relationships
* Define fact tables and dimension tables for multidimensional analysis
* Configure measures with appropriate aggregations and formatting
* Build dimension hierarchies with geographic and temporal properties
* Create drill-down paths for Territory, Customer, Product, and Time dimensions
* Apply member properties to enrich dimension levels with descriptive attributes
* Configure time dimension properties with proper format patterns for filtering
* Customize data models for both Interactive Reporting and Analyzer tools
* Export and enhance models for advanced business analytics requirements

By the end of this workshop, you'll have created both a simple CSV-based reporting model and a sophisticated star schema analytical model that demonstrates enterprise-grade data modeling best practices. You'll understand how to structure data for optimal query performance, provide intuitive navigation through dimension hierarchies, and deliver the rich analytical capabilities that business users expect from modern business intelligence tools.

**Prerequisites:** Pentaho Business Analytics Server with sample database connection (hsqldb\_sampledata) configured\
**Estimated Time:** 25 minutes
{% endhint %}

<figure><img src="https://content.gitbook.com/content/WEU7pAxrVpThcB2tykVX/blobs/HDM6klkhYP0MgXeGjYwv/image.png" alt=""><figcaption><p>Data Source Wizard</p></figcaption></figure>

{% hint style="info" %}
When you first open the model editor, the centre pane of the dialog box displays the active model. This model is organized into categories and fields that represent the tables and columns in your data.

* Creating a data source from a CSV file treats all columns as if they are in a single table, with no limitations on where measures come from or which fields can be grouped in a hierarchy.
* Creating a data source from a database table for Reporting and Analysis requires you to specify a fact table. Only numeric columns from the fact table can be used as measures. Also, all levels in a single hierarchy must come from the same dimension table.
* If you add or edit annotations on the data model in Analyzer, these annotations will not be visible when viewing the model in the Data Source Wizard. For example, if you add a calculated measure to the model in Analyzer, it will not display in the list of measures when viewing the model in DSW. Saving the model in DSW will overwrite any annotations which were added to the model in Analyzer.
  {% endhint %}

***

**Launch the Data Source Wizard**

1. Log into the Pentaho User Console:

&#x20;      User: Admin

&#x20;      Password: password

2. Click Create New > Data Source or access from Manage Data Sources.

<figure><img src="https://content.gitbook.com/content/WEU7pAxrVpThcB2tykVX/blobs/pTr2xCWQRYMD7uBTZe0x/image.png" alt=""><figcaption><p>New Data Source</p></figcaption></figure>

***

Select the model Data Source:

{% tabs %}
{% tab title="CSV" %}
{% hint style="info" %}

#### CSV File

{% endhint %}

{% embed url="<https://www.loom.com/share/3f5c56291b0d48e38adf5edb8dc4a8a3?hideEmbedTopBar=true&hide_owner=true&hide_share=true&hide_title=true>" %}
Model from CSV file
{% endembed %}

1. Download the Checkbook.csv file:

{% file src="<https://content.gitbook.com/content/WEU7pAxrVpThcB2tykVX/blobs/PnGQ8ovm5ibCYCUfFylq/Checkbook_data.csv>" %}

2. Enter Data Source Name: Checkbook

{% hint style="info" %}
Provide a descriptive name (avoid special characters: / \ : \* ? " < > |)
{% endhint %}

3. Select CSV File as Source Type.
4. Click Import and browse to select your CSV file.

<figure><img src="https://content.gitbook.com/content/WEU7pAxrVpThcB2tykVX/blobs/fZp1OoiFPBgwVmYev4GN/image.png" alt=""><figcaption><p>DSW - Import Checkbook_data.csv</p></figcaption></figure>

5. Configure the following CSV properties:

| Property         | Value                          |
| ---------------- | ------------------------------ |
| Data Source Name | Checkbook                      |
| Source Type      | CSV File                       |
| File             | /Downloads/Checkbook\_data.csv |
| Encoding         | ISO-8859-1                     |
| Delimiter        | Comma                          |
| Enclosure        | Double Quote                   |

<figure><img src="https://content.gitbook.com/content/WEU7pAxrVpThcB2tykVX/blobs/5mZxxorxvgGEmq9PFK3N/image.png" alt=""><figcaption><p>DSW - Configure Data Source</p></figcaption></figure>

6. Preview and Verify.
7. Click Next to Continue.

{% hint style="info" %}
The Staging Settings screen displays a list of columns from your CSV source file. All columns are enabled.
{% endhint %}

<figure><img src="https://content.gitbook.com/content/WEU7pAxrVpThcB2tykVX/blobs/Fe5Uvu9gpe7a9qddnee1/image.png" alt=""><figcaption><p>DSW - Staging</p></figcaption></figure>

8. Edit the following properties:

| Name          | Type    | Source Format | Length | Precision |
| ------------- | ------- | ------------- | ------ | --------- |
| Date          | STRING  |               | 15     | 0         |
| Description   | STRING  |               | 50     | 0         |
| Category Desc | STRING  |               | 50     | 0         |
| Category      | STRING  |               | 5      | 0         |
| Amount        | NUMERIC | #,##0.00      | 10     | 2         |

9. Click: Show File Contents.

<figure><img src="https://content.gitbook.com/content/WEU7pAxrVpThcB2tykVX/blobs/5YjHh9ZaCuDRvv5xowMs/image.png" alt=""><figcaption><p>File Preview</p></figcaption></figure>

10. Click Finish.
11. Choose Model Option: Customize model now

{% hint style="info" %}
Keep default model - Uses the data source as-is

Customize model now - Opens Data Source Model Editor for refinement
{% endhint %}

<figure><img src="https://content.gitbook.com/content/WEU7pAxrVpThcB2tykVX/blobs/p3eOvcVcerYbKceIeQc5/image.png" alt=""><figcaption></figcaption></figure>

***

Select a reporting option:

{% embed url="<https://www.loom.com/share/5887962291734f3180c45450fd8dbd23?hideEmbedTopBar=true&hide_owner=true&hide_share=true&hide_title=true>" %}
Configure Model for Interactive & Analyzer reports
{% endembed %}

{% tabs %}
{% tab title="Interactive Reporting" %}
{% hint style="info" %}

#### Interactive Reporting

Notice that the Wizard puts all the columns in one category. You can use the toolbar buttons to easily create additional categories and move the columns up and down to organize the columns into categories.
{% endhint %}

1. On the Reporting tab, click the Check book category.
2. In the Properties pane, click in the Category Name field.
3. Rename: Checkbook Category.

<figure><img src="https://content.gitbook.com/content/WEU7pAxrVpThcB2tykVX/blobs/LeJEHUDumr3Mn1IgVsPz/image.png" alt=""><figcaption><p>DSW - Edit Display Name</p></figcaption></figure>

4. Format the Amount: #.#

<figure><img src="https://content.gitbook.com/content/WEU7pAxrVpThcB2tykVX/blobs/e1ZTfnxvNr1wUiFA20eJ/image.png" alt=""><figcaption></figcaption></figure>

5. Click OK.

***

1. From the User Console Home Perspective, click Create New > Interactive Report.
2. In the Select Data Source window, click Checkbook, and then click OK.

<figure><img src="https://content.gitbook.com/content/WEU7pAxrVpThcB2tykVX/blobs/0JZxO2Ttdw26rf6mXplH/image.png" alt=""><figcaption><p>Data Source - Checkbook</p></figcaption></figure>

3. On the Data tab, notice the Checkbook Category name.

<figure><img src="https://content.gitbook.com/content/WEU7pAxrVpThcB2tykVX/blobs/f1YUEqqCu1saNgmTXt4t/image.png" alt=""><figcaption><p>Checkbook Report</p></figcaption></figure>

{% hint style="info" %}
If this is the first time you are using Interactive Reporting, you may need to click the Get Started button, and then click to Hide Tips.
{% endhint %}

4. Add Category Desc, on the Data tab, double-click Category Desc.
5. Add Date, on the Data tab, double-click Date.
6. Add Amount, on the Data tab, double-click Amount.
   {% endtab %}

{% tab title="Analyzer" %}
{% hint style="info" %}

#### Analyzer

{% endhint %}

1. Edit the Checkbook model in the Data Source Wizard - From the Cog wheel drop-down menu option, select: Edit.

<figure><img src="https://content.gitbook.com/content/WEU7pAxrVpThcB2tykVX/blobs/nWmmqMK7kjR8oKkZ88Xr/image.png" alt=""><figcaption><p>Edit Checkbook model</p></figcaption></figure>

2. Expand the Measures Dimensions.

<figure><img src="https://content.gitbook.com/content/WEU7pAxrVpThcB2tykVX/blobs/mt4MXkOYY4QC6kXj3ldW/image.png" alt=""><figcaption><p>Examine Analysis model</p></figcaption></figure>

{% hint style="info" %}
Notice that the metric 'Amount' is defined as a Measure & Dimension. Also each Dimension has a Hierarchy & a Level. Let's tidy up the model ..
{% endhint %}

3. Delete the Amount Dimension - its a metric..! Click on the X in the top toolbar.

<figure><img src="https://content.gitbook.com/content/WEU7pAxrVpThcB2tykVX/blobs/Cck2hbPeYRPyNhhOPcvI/image.png" alt=""><figcaption><p>Remove Amount Dimension</p></figcaption></figure>

4. Drag \&drop the Category Desc Level to below the Category Level.

<figure><img src="https://content.gitbook.com/content/WEU7pAxrVpThcB2tykVX/blobs/TY3xtNHk5T7rOfTnv0ii/image.png" alt=""><figcaption><p>Move Category Desc Level</p></figcaption></figure>

5. Delete the redundant Category Desc Dimension.

<figure><img src="https://content.gitbook.com/content/WEU7pAxrVpThcB2tykVX/blobs/FrB5vaOZShL6BnFg3vFF/image.png" alt=""><figcaption><p>Delete the Category Desc Dimension</p></figcaption></figure>

{% hint style="info" %}
Notice: the warning triangle.
{% endhint %}

5. Flip the Measures to below the Dimensions.

<figure><img src="https://content.gitbook.com/content/WEU7pAxrVpThcB2tykVX/blobs/ZDb66OHoMSMVp9ENvQKe/image.png" alt=""><figcaption><p>Flip Measures &#x26; Dimensions</p></figcaption></figure>

6. Click: OK

***

1. From the User Console Home Perspective, click Create New > Analyzer.
2. In the Select Data Source window, click Checkbook, and then click OK.

<figure><img src="https://content.gitbook.com/content/WEU7pAxrVpThcB2tykVX/blobs/NSERvlNTZwRAKgcnFOIq/image.png" alt=""><figcaption><p>Checkbook Data Source</p></figcaption></figure>

3. Add the following Levels and Measure to define the Analyzer report.

<figure><img src="https://content.gitbook.com/content/WEU7pAxrVpThcB2tykVX/blobs/cUCKdiLPWWUkIJnqLDOt/image.png" alt=""><figcaption><p>Spreadsheet of Expenses..!</p></figcaption></figure>

{% hint style="info" %}
The following tasks refine your relational and multidimensional data models with the Data Source Model Editor:

* Assign Geographic Properties
* Assign Time Dimension Properties
* Use Ordinal Columns
* Properties of Time Dimension Levels
  {% endhint %}
  {% endtab %}
  {% endtabs %}
  {% endtab %}

{% tab title="SQL Queries" %}

{% endtab %}

{% tab title="Database Table(s)" %}
{% hint style="info" %}

#### Database Table

When you create a Database Table data source, you are presented with two options: Reporting Only or Reporting and Analysis. The choice you make depends on the structure of the database tables you are accessing and the User Console tools being used.

Before creating the data source, let's understand the database structure. This is a classic star schema with one fact table and three dimension tables.
{% endhint %}

<table><thead><tr><th width="106">Table</th><th>Purpose</th><th>Primary Key</th></tr></thead><tbody><tr><td>FACT</td><td>Stores transactional sales data with numeric measures</td><td></td></tr><tr><td></td><td></td><td></td></tr><tr><td></td><td></td><td></td></tr></tbody></table>

{% hint style="info" %}

#### Prerequisites:

* Database connection named 'hsqldb\_sampledata' is configured and working
* You have SELECT permissions on all four tables
* Foreign key relationships exist in the database (or you'll create joins manually)
* Tables contain sample data for testing
  {% endhint %}

***

Complete the following steps to define your Sales\_Analysis model and Data Source:

{% tabs %}
{% tab title="1. Data Source" %}
{% hint style="info" %}

#### Data Source

{% endhint %}

1. Enter Data Source Name: Sales\_Analysis
2. Select Database Table as Source Type.

{% hint style="info" %}
Provide a descriptive name (avoid special characters: / \ : \* ? " < > |)
{% endhint %}

<figure><img src="https://content.gitbook.com/content/WEU7pAxrVpThcB2tykVX/blobs/MYrdVhb0xOoJWsI1KQJa/image.png" alt=""><figcaption><p>Configure Sales_Analysis</p></figcaption></figure>

3. Select: hsqldb\_sampledata from the list of available connections.
4. Select: Reporting and Analysis (Requires a Star Schema)
5. Click: Next
   {% endtab %}

{% tab title="2. Tables" %}
{% hint style="info" %}

#### Add Tables

{% endhint %}

1. Click the Schema dropdown menu
2. Select your schema: PUBLIC (e.g., 'sales\_schema' or 'public' depending on your database)
3. Hold down the Ctrl key and select the following tables:

<figure><img src="https://content.gitbook.com/content/WEU7pAxrVpThcB2tykVX/blobs/ZBNNz5nHPOGT7ZjDS5kz/image.png" alt=""><figcaption><p>Add Tables</p></figcaption></figure>

4. Add the Tables an set the Fact Table.

<figure><img src="https://content.gitbook.com/content/WEU7pAxrVpThcB2tykVX/blobs/DUafbygcJxbYuTFjdJnq/image.png" alt=""><figcaption><p>Set Fact Table</p></figcaption></figure>

5. Click: Next.
   {% endtab %}

{% tab title="3. Joins" %}
{% hint style="info" %}

#### Joins

Joins connect the Fact table to Dimension tables.
{% endhint %}

1. Define the following Joins:

<table data-full-width="true"><thead><tr><th width="138">Left Table</th><th width="233">Key Field</th><th width="183">Right Table</th><th width="232">Key Field</th></tr></thead><tbody><tr><td>ORDERFACT</td><td>TIME_ID</td><td>DIM_TIME</td><td>TIME_ID</td></tr><tr><td>ORDERFACT</td><td>CUSTOMERNUMBER</td><td>CUSTOMER_W_TER</td><td>CUSTOMERNUMBER</td></tr><tr><td>ORDERFACT</td><td>ORDERNUMBER</td><td>ORDERS</td><td>ORDERNUMBER</td></tr><tr><td>ORDERFACT</td><td>PRODUCTCODE</td><td>PRODUCT</td><td>PRODUCTCODE</td></tr></tbody></table>

<figure><img src="https://content.gitbook.com/content/WEU7pAxrVpThcB2tykVX/blobs/UDPhd8fppBYBPv0cpnvk/image.png" alt=""><figcaption><p>Joins</p></figcaption></figure>

2. Click: Finish.

{% hint style="warning" %}
Finally .. you'll need to qualify each measure and dimension ..
{% endhint %}
{% endtab %}

{% tab title="4. Measures" %}
{% hint style="info" %}

#### Measures

Measures are the numeric values you want to analyze. They come from the Fact table and can be aggregated (summed, averaged, counted).
{% endhint %}

1. Expand the Measures category.
2. Delete the following dimensions:

* CUSTOMERNUMBER
* MONTH ID
* ORDERLINENUMBER
* ORDERNUMBER
* QTR ID
* YEAR ID

<figure><img src="https://content.gitbook.com/content/WEU7pAxrVpThcB2tykVX/blobs/Mgu0qyIKzXqrS5PI78Iw/image.png" alt=""><figcaption><p>Remove unwanted Dimensions</p></figcaption></figure>

3. Format the remaining Measures:

<table data-full-width="true"><thead><tr><th width="187">Measure</th><th width="166">Display Name</th><th width="198">Default Aggregation</th><th width="100">Format</th></tr></thead><tbody><tr><td>PRICEEACH</td><td>Unit Price</td><td>Average</td><td>0.00</td></tr><tr><td>QUANTITYORDERED</td><td>Quantity Ordered</td><td>DISTINCT_COUNT</td><td>#</td></tr><tr><td>TOTALPRICE</td><td>Total Sales</td><td>SUM</td><td>0.00</td></tr></tbody></table>

{% hint style="warning" %}
Only Measures in the Fact table can be defined.&#x20;
{% endhint %}
{% endtab %}

{% tab title="5. Dimensions" %}
{% hint style="info" %}

#### Dimensions

{% endhint %}

Follow the steps outlined below to configure the Dimensions in your Model:

{% tabs %}
{% tab title="1. MARKETS" %}
{% hint style="info" %}

#### MARKETS

{% endhint %}

1. Highlight & expand the Geography Dimension.
2. Rename the Dimension: MARKETS.
3. Add a Hierarchy: Markets.

<figure><img src="https://content.gitbook.com/content/WEU7pAxrVpThcB2tykVX/blobs/evcBWcZ5ByDFew1sEN7r/image.png" alt=""><figcaption><p>Markets Hierarchy</p></figcaption></figure>

***

<figure><img src="https://content.gitbook.com/content/WEU7pAxrVpThcB2tykVX/blobs/SP3lDvPYaXPs6gEA10zE/image.png" alt=""><figcaption><p>Drill-down on Markets</p></figcaption></figure>

Expand the Markets Hierarchy and follow the steps below to configure the Levels - delete POSTALCODE:

{% tabs %}
{% tab title="1. TERRITORY" %}

1. Highlight the TERRITORY Level & configure with the following Properties.

<figure><img src="https://content.gitbook.com/content/WEU7pAxrVpThcB2tykVX/blobs/wt82AbNv6NfhnhCHB8BG/image.png" alt=""><figcaption><p>Level - Territory</p></figcaption></figure>

{% hint style="info" %}

* Click on the pencil icon to select TERRITORY Source Column. Scroll down to the bottom of the list and Select: OK to add the column.
* Check: Contains only unique members
* Geography Type: Territory
  {% endhint %}
  {% endtab %}

{% tab title="2. COUNTRY" %}

1. Highlight the COUNTRY Level & configure with the following Properties.

<figure><img src="https://content.gitbook.com/content/WEU7pAxrVpThcB2tykVX/blobs/eWY9pDTfJJefbW8NPIHD/image.png" alt=""><figcaption><p>Level - Country</p></figcaption></figure>

{% hint style="info" %}

* Click on the pencil icon to select COUNTRY Source Column. Scroll down to the bottom of the list and Select: OK to add the column.
* Check: Contains only unique members
* Geography Type: Country
  {% endhint %}
  {% endtab %}

{% tab title="3. STATE" %}

1. Highlight the STATE Level & configure with the following Properties.

<figure><img src="https://content.gitbook.com/content/WEU7pAxrVpThcB2tykVX/blobs/wCQ6VTLV3TNOHwtNhgRS/image.png" alt=""><figcaption><p>Level - State</p></figcaption></figure>

{% hint style="info" %}

* Click on the pencil icon to select STATE Source Column. Scroll down to the bottom of the list and Select: OK to add the column.
* Check: Contains only unique members
* Geography Type: State
  {% endhint %}
  {% endtab %}

{% tab title="4. CITY" %}

1. Highlight the CITY Level & configure with the following Properties.

<figure><img src="https://content.gitbook.com/content/WEU7pAxrVpThcB2tykVX/blobs/G9u59vN6gjQG1twRF4tg/image.png" alt=""><figcaption><p>Level - City</p></figcaption></figure>

{% hint style="info" %}

* Click on the pencil icon to select CITY Source Column. Scroll down to the bottom of the list and Select: OK to add the column.
* Do not check: Contains only unique members
* Geography Type: City
  {% endhint %}
  {% endtab %}
  {% endtabs %}
  {% endtab %}

{% tab title="2. CUSTOMERS" %}
{% hint style="info" %}

#### CUSTOMERS

{% endhint %}

1. Highlight & expand the CUSTOMER W TER Dimension.

<figure><img src="https://content.gitbook.com/content/WEU7pAxrVpThcB2tykVX/blobs/PX4B3tkV7k03C2EmSk1x/image.png" alt=""><figcaption><p>CUSTOMER W TER</p></figcaption></figure>

2. It will be easier to start with a 'clean' dimension, delete everything..!
3. Rename the CUSTOMER W TER Dimension: CUSTOMER.

<figure><img src="https://content.gitbook.com/content/WEU7pAxrVpThcB2tykVX/blobs/NPuIKiBbK9BFViQxBrYL/image.png" alt=""><figcaption><p>Rename Dimension</p></figcaption></figure>

2. Add a Hierarchy & enter: Customer.

<figure><img src="https://content.gitbook.com/content/WEU7pAxrVpThcB2tykVX/blobs/bPcz5tKSsR1fwQPwBYdn/image.png" alt=""><figcaption><p>Hierarchy - Customer</p></figcaption></figure>

3. Highlight Customer Hierarchy & add Level: Customer Name.

<figure><img src="https://content.gitbook.com/content/WEU7pAxrVpThcB2tykVX/blobs/A3p1t7p4HyhCEYgm5ZgK/image.png" alt=""><figcaption><p>Level - Customer Name</p></figcaption></figure>

4. Click: OK.
5. Configure the Customer Name properties as illustrated below:

<figure><img src="https://content.gitbook.com/content/WEU7pAxrVpThcB2tykVX/blobs/JxbDbX2JPmI2QWfzLl2s/image.png" alt=""><figcaption><p>Customer Name Properties</p></figcaption></figure>

{% hint style="info" %}

* Click on the pencil icon to select CUSTOMERNAME Source Column. Scroll down to the bottom of the list and Select: OK to add the column.
* Do not Check: Contains only unique members
  {% endhint %}

***

<figure><img src="https://content.gitbook.com/content/WEU7pAxrVpThcB2tykVX/blobs/hfvireQjdIAxXlzi9UrU/image.png" alt=""><figcaption><p>Customer Name  -  Members</p></figcaption></figure>

Follow the steps below to add the Customer Name members:

{% tabs %}
{% tab title="1. Customer Number" %}

1. Highlight the Customer Name Level > Add Member Property: Customer Number.

<figure><img src="https://content.gitbook.com/content/WEU7pAxrVpThcB2tykVX/blobs/jQFigzjQ1gJ8AlMd6XSF/image.png" alt=""><figcaption><p>Member - Customer Number</p></figcaption></figure>

2. Click: OK.
3. Configure the Customer Number properties as illustrated below:

<figure><img src="https://content.gitbook.com/content/WEU7pAxrVpThcB2tykVX/blobs/ZvJh8PCphrCa6n6cei8g/image.png" alt=""><figcaption><p>Customer Number Properties</p></figcaption></figure>

{% hint style="info" %}

* Click on the pencil icon to select CUSTOMERNUMBER Source Column. Scroll down to the bottom of the list and Select: OK to add the column.
* Enter a description
  {% endhint %}
  {% endtab %}

{% tab title="2. Contact First Name" %}

1. Highlight the Customer Name Level > Add Member Property: Contact First Name.

<figure><img src="https://content.gitbook.com/content/WEU7pAxrVpThcB2tykVX/blobs/F01qwDnWuU5WGktG5QJi/image.png" alt=""><figcaption><p>Member - Contact First Name</p></figcaption></figure>

2. Click: OK.
3. Configure the Contact First Name properties as illustrated below:

<figure><img src="https://content.gitbook.com/content/WEU7pAxrVpThcB2tykVX/blobs/0MfrVbt05VESnNXexKK9/image.png" alt=""><figcaption><p>Contact First Name Properties</p></figcaption></figure>

{% hint style="info" %}

* Click on the pencil icon to select CONTACTFIRSTNAME Source Column. Scroll down to the bottom of the list and Select: OK to add the column.
* Enter a description
  {% endhint %}
  {% endtab %}

{% tab title="3. Contact Last Name" %}

1. Highlight the Customer Name Level > Add Member Property: Contact Last Name.

<figure><img src="https://content.gitbook.com/content/WEU7pAxrVpThcB2tykVX/blobs/uhOZeS8TwMZhIfjfko9T/image.png" alt=""><figcaption><p>Member - Contact Last Name</p></figcaption></figure>

2. Click: OK.
3. Configure the Contact Last Name properties as illustrated below:

<figure><img src="https://content.gitbook.com/content/WEU7pAxrVpThcB2tykVX/blobs/Zj48zDVXOWtkbFBvrTxh/image.png" alt=""><figcaption><p>Contact Last Name Properties</p></figcaption></figure>

{% hint style="info" %}

* Click on the pencil icon to select CONTACTLASTNAME Source Column. Scroll down to the bottom of the list and Select: OK to add the column.
* Enter a description
  {% endhint %}
  {% endtab %}

{% tab title="4. Phone" %}

1. Highlight the Customer Name Level > Add Member Property: Phone Number.

<figure><img src="https://content.gitbook.com/content/WEU7pAxrVpThcB2tykVX/blobs/vFjJzOOWyznOUsZdLGXB/image.png" alt=""><figcaption><p>Member - Phone Number</p></figcaption></figure>

2. Click: OK.
3. Configure the Phone Number properties as illustrated below:

<figure><img src="https://content.gitbook.com/content/WEU7pAxrVpThcB2tykVX/blobs/tB5CupmAmXQtg0c7PLyi/image.png" alt=""><figcaption><p>Phone Properties</p></figcaption></figure>

{% hint style="info" %}

* Click on the pencil icon to select PHONE Source Column. Scroll down to the bottom of the list and Select: OK to add the column.
* Enter a description
  {% endhint %}
  {% endtab %}
  {% endtabs %}
  {% endtab %}

{% tab title="3. PRODUCTS" %}
{% hint style="info" %}

#### PRODUCTS

{% endhint %}

1. Highlight & expand the DIM TIME Dimension.
2. Again It will be easier to start with a 'clean' dimension, delete everything..!
3. Add a Hierarchy & enter: Products

<figure><img src="https://content.gitbook.com/content/WEU7pAxrVpThcB2tykVX/blobs/6gP6tJ58FYoBiLVrs9Qb/image.png" alt=""><figcaption></figcaption></figure>

***

Follow the steps below to configure the Products Levels:

{% tabs %}
{% tab title="1. Product Line" %}

1. Under the Products Hierarchy > Add a Level: Product Line

<figure><img src="https://content.gitbook.com/content/WEU7pAxrVpThcB2tykVX/blobs/lnEmWcQylmOpWVK3x4P4/image.png" alt=""><figcaption><p>Level - Product Line</p></figcaption></figure>

2. Click: OK.
3. Configure the Product Line properties as illustrated below:

<figure><img src="https://content.gitbook.com/content/WEU7pAxrVpThcB2tykVX/blobs/JQC6HU0UkBaTuE4scDhh/image.png" alt=""><figcaption><p>Product Line Properties</p></figcaption></figure>

{% hint style="info" %}

* Click on the pencil icon to select PRODUCTLINE Source Column. Scroll down to the bottom of the list and Select: OK to add the column.
* Check: Contains only unique members
  {% endhint %}
  {% endtab %}

{% tab title="2. Product Name" %}

1. Under the Products Hierarchy > Add a Level: Product Name

<figure><img src="https://content.gitbook.com/content/WEU7pAxrVpThcB2tykVX/blobs/lzIaTELhWJc9Q5XjVV5W/image.png" alt=""><figcaption><p>Level - Product Name</p></figcaption></figure>

2. Click: OK.
3. Configure the Product Name properties as illustrated below:

<figure><img src="https://content.gitbook.com/content/WEU7pAxrVpThcB2tykVX/blobs/FDabVQqMXQkJ1CqjNzH2/image.png" alt=""><figcaption><p>Product Name Properties</p></figcaption></figure>

{% hint style="info" %}

* Click on the pencil icon to select PRODUCTNAME Source Column. Scroll down to the bottom of the list and Select: OK to add the column.
* Check: Contains only unique members
  {% endhint %}
  {% endtab %}
  {% endtabs %}
  {% endtab %}

{% tab title="4. ORDERS" %}
{% hint style="info" %}

#### ORDERS

{% endhint %}

1. Highlight & expand the Geography Dimension.
2. Add a Hierarchy: Orders.

<figure><img src="https://content.gitbook.com/content/WEU7pAxrVpThcB2tykVX/blobs/evcBWcZ5ByDFew1sEN7r/image.png" alt=""><figcaption><p>Markets Hierarchy</p></figcaption></figure>

***

<figure><img src="https://content.gitbook.com/content/WEU7pAxrVpThcB2tykVX/blobs/SP3lDvPYaXPs6gEA10zE/image.png" alt=""><figcaption><p>Drill-down on Markets</p></figcaption></figure>

Expand the Markets Hierarchy and follow the steps below to configure the Levels - delete POSTALCODE:
{% endtab %}

{% tab title="5. TIME" %}
{% hint style="info" %}

#### TIME

Typically, you might sort your data by year or month. However, by designating a time dimension in the Data Source Model Editor, you can filter on a variety of date ranges and relative date options, such as previous periods, before \[a user-defined period], after \[a user-defined period], current period, or next period.

Analyzer supports many types of relative date filters. In order to apply them to a level of a time hierarchy, you need to define the time-specific properties for that level. This is because each data warehouse implementation may have a different date format and set of time hierarchy levels.
{% endhint %}

1. Highlight & expand the DIM TIME Dimension.
2. It will be easier to start with a 'clean' dimension, delete everything..!
3. Add a Hierarchy & enter: Time

<figure><img src="https://content.gitbook.com/content/WEU7pAxrVpThcB2tykVX/blobs/YVoPQBiLgi7TfxLF1D1m/image.png" alt=""><figcaption></figcaption></figure>

***

Follow the steps below to configure the Time Levels:

{% tabs %}
{% tab title="1. Years" %}

1. Under the Time Hierarchy > Add a Level: Years

<figure><img src="https://content.gitbook.com/content/WEU7pAxrVpThcB2tykVX/blobs/Sn4fBJGCS1F0pup8lXZ5/image.png" alt=""><figcaption><p>Level - Years</p></figcaption></figure>

2. Click: OK.
3. Configure the Year properties as illustrated below - refer to Time Dimension Properties:

<figure><img src="https://content.gitbook.com/content/WEU7pAxrVpThcB2tykVX/blobs/xDFdJQzy52eKUvKwVINp/image.png" alt=""><figcaption><p>Year Properties</p></figcaption></figure>

{% hint style="info" %}

* Click on the pencil icon to select YEAR ID Source Column. Scroll down to the bottom of the list and Select: OK to add the column.
* Check: Contains only unique members
* Time Level Type: Years
* Format: yyyy
  {% endhint %}
  {% endtab %}

{% tab title="2. Quarters" %}

1. Under the Time Hierarchy > Add a Level: Quarters
2. Configure the Quarters properties as illustrated below - refer to Time Dimension Properties:

<figure><img src="https://content.gitbook.com/content/WEU7pAxrVpThcB2tykVX/blobs/PsbiePfl2rwXTMKuXJm5/image.png" alt=""><figcaption><p>Quarters Properties</p></figcaption></figure>

{% hint style="info" %}

* Click on the pencil icon to select QTR NAME Source Column. Scroll down to the bottom of the list and Select: OK to add the column.
* Check: Contains only unique members
* Time Level Type: Quarters
* Format: Q
  {% endhint %}
  {% endtab %}

{% tab title="3. Months" %}

1. Under the Time Hierarchy > Add a Level: Months
2. Configure the Months properties as illustrated below - refer to Time Dimension Properties:

<figure><img src="https://content.gitbook.com/content/WEU7pAxrVpThcB2tykVX/blobs/uzmd6B0FBg1MXK9Gb44R/image.png" alt=""><figcaption></figcaption></figure>

{% hint style="info" %}

* Click on the pencil icon to select MONTH NAME Source Column. Scroll down to the bottom of the list and Select: OK to add the column.
* Check: Contains only unique members
* Time Level Type: Months
* Format: M
  {% endhint %}
  {% endtab %}
  {% endtabs %}

***

{% hint style="info" %}

#### Time Dimension Properties

The Data Source Model Editor has two key settings for configuring time dimensions:&#x20;

**Time Level Type**

This setting specifies what type of date increment a level represents (Years, Quarters, Months, Days, etc.). Analyzer uses this information to enable time-period functions like "previous quarter" or "year to date."

**Source Column Format**

This describes how your data is actually formatted in the database. It tells Analyzer how to read and interpret the time values. Important: This setting does NOT change how data displays—it only describes the existing format so Analyzer can filter correctly.

These settings enable Analyzer to properly filter and analyze time-based data.
{% endhint %}

**Common Format Patterns**

| Time Increment | Format Codes                          | Examples Values               |
| -------------- | ------------------------------------- | ----------------------------- |
| Years          | <p>yy </p><p>yyyy</p>                 | <p>25 </p><p>2025</p>         |
| Quarters       | <p>Q </p><p>QQ </p><p>QQQ</p>         | <p>1 </p><p>01 </p><p>Q1</p>  |
| Months         | <p>M </p><p>MM </p><p>MMM</p>         | <p>3 </p><p>03 </p><p>Mar</p> |
| Weeks          | <p>w </p><p>ww </p><p>W</p>           | <p>5 </p><p>05</p>            |
| Days           | <p>d </p><p>dd</p>                    | <p>7 </p><p>07</p>            |
| Hours          | <p>H </p><p>HH </p><p>k </p><p>kk</p> |                               |
| Minutes        | <p>m </p><p>mm</p>                    | <p>5 </p><p>05</p>            |
| Seconds        | <p>s </p><p>ss</p>                    | <p>8 </p><p>08</p>            |

{% hint style="info" %}
**Quarter Format Examples**

Quarters can be stored in different ways. Here's how to configure the Source Column Format for each:
{% endhint %}

<table><thead><tr><th width="161">Data in Database</th><th width="170">Source Column Format</th><th>Explanation</th></tr></thead><tbody><tr><td>1, 2, 3, 4</td><td>Q</td><td>Simple numeric quarter</td></tr><tr><td>Q1, Q2, Q3, Q4</td><td>'Q'Q</td><td>'Q' is literal text, Q is the number</td></tr><tr><td>2001-Q1, 2001-Q2</td><td>yyyy-'Q'Q</td><td>Year, - literal Q, quarter number</td></tr></tbody></table>

{% hint style="info" %}
**Custom Formats**

If the dropdown options don't match your data format, you can type directly into the Source Column Format field. Your format must follow the ICU Simple Date Format specification. Use single quotes ('') around any literal text characters.

**Critical Rule**

The Source Column Format must exactly match how your data is stored in the database. Mismatched formats will cause filtering errors in Analyzer. Always check your actual database values before configuring these settings.
{% endhint %}
{% endtab %}
{% endtabs %}
{% endtab %}
{% endtabs %}
{% endtab %}
{% endtabs %}