In the Model Pro, the ETL is built, then the data model is configured, and lastly security preferences are set and the data flow and/ or data model are processed.
The ETL may be built using a single data source, or multiple data sources. Once the data source is selected, the user chooses the tables that should be used in the data model, before performing any required data preparation and manipulation.
Some data sources can be directly queried, meaning the user doesn't need to configure an ETL or copy any data to Pyramid.
Refer to the examples below to review this workflow.
Example 1: Single Data Source
This example outlines the steps to build a data model with a single data source.
STEP 1. DATA FLOW
Build the data flow on the canvas. First, add a data source and select the tables to include. Then, add any relevant functions to your data flow. Lastly, connect the target to the data flow. See Data Flow Functions for more information.
STEP 2. DATA MODEL
Once the data flow is complete, move to the data model tab. Start with Configuration, then edit relationships in Tables, edit the columns where necessary, and build any user hierarchies to enable drill down later on when building queries. For more details, refer to Data Model Functions.
STEP 3. SECURITY
Set read and write permissions for the database and the data model. Refer to Security for more information.
STEP 4. SAVE AND PROCESS
Save your data model to the CMS by clicking the save icon. To save your data model and open it in Data Discovery immediately, click the Pyramid icon in the top left corner. Choose your processing options. Click here to learn about scheduling data flows and data models.
Example 2: Multiple Data Sources
Users can build data models based on multiple data sources.
STEP 1. DATA FLOW
Add the required server nodes to the data flow. For each server, select the appropriate server and database from the Properties panel. From Table Selection, select the tables that should be used in the Data Model. Add any other nodes required for data manipulation. In this example, a join was added to join Return Reason and Return Detail into a single table. Combined columns was added to combine the First Name and Last Name columns in the Customers table. And time Intelligence was added to the Data table.
Finally, add the target node. Select the target server and create a new database or select an existing one.
STEP 2. DATA MODEL
Move to Data Model to configure the data model's tables, columns, and hierarchies.
STEP 3. SECURITY
From Security, assign read and write permissions on the database and data model to user roles.
STEP 4. SAVE AND PROCESS
Click the save icon to save the model definition file to the CMS. Then click the Pyramid icon in the top left corner to open the Processing Options dialog and process the data model.
Example 3: Direct Querying
Some data sources can be natively queried by Pyramid with direct queries, meaning that there's no need to pre-process the data. Click here for a list of data sources that can be directly queried.
STEP 1. DATA FLOW
Add the required data source node to the query. From the Properties panel, select the server and database you want to connect to, then select 'Direct Query Data Source.' Under Table Selection, select the tables you want to be able to query.
STEP 2. DATA MODEL
Open the Data Model tab. From the Configuration window, select the model target: either Virtual, Tabular, or In Memory. Then configure tables, columns, and hierarchies.
STEP 3. SECURITY
Open the Security tab and assign roles read and write permissions to the data model.
STEP 4. SAVE AND PROCESS
Click the save icon to save the model definition file to the CMS. Then click the Pyramid icon in the top left corner to open the Processing Options dialog and process the data model.
