Create a database table source
Archived. Content merged into "Choose a data source type".
This page is archived. See the current steps in Choose a data source type.
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 you begin working with Database Table data sources, there are a few key terms that you should know.
Fact table
Records measurements or metrics for a specific event, and usually consists of numbers.
Star schema
Has one or more fact tables that reference any number of dimension tables. Star schemas are good for simpler queries.
Snowflake schema
Has centralized fact tables that are connected to multiple dimensions. Snowflake schemas are good for more complex queries.
Reporting Only
Reporting and Analysis
Summary
Choose the Reporting Onlyoption if you are accessing data in a relational database that is operational or transactional in nature, or if you need to show detailed rows and then sort and filter the details.When you choose the Reporting Only option, a relational model is automatically generated and can be refined in the Data Source Model Editor or further extended using Pentaho Metadata Editor.
Relational models on large databases are sometimes slow and benefit from a powerful database.
Choose the Reporting and Analysis option if you are accessing a multidimensional database that contains tables arranged in a star schema (tables are separated into dimensions related to a single fact table, such as a data warehouse), or if you need summaries and details.
When you choose the Reporting and Analysis option, a multidimensional model is automatically generated and can be refined in the Data Source Model Editor or further extended using Pentaho Schema Workbench.
Additionally, you can create a Reporting and Analysis data source using a single table where the table acts as the single fact table and also contains dimensional data.
Expertise
Knowledge of relational database structures.
Knowledge of multidimensional database structures.
Time
Approximately 15 minutes.
Approximately 30 minutes.
Recommendation
Select this if you want to use for Report Designer, Interactive Reports, and Metadata Editor.
Select this if you want to use for Report Designer, Analyzer (PUC), Dashboard Designer, Schema Workbench, and Aggregation Designer.
Create a database table data source (Reporting Only)
Your database must be running before you begin.
Sign in to the User Console.
Select Create New, then select Data Source.
Select New Data Source.
The Data Source Wizard opens.
In Data Source Name, enter a descriptive name.
These characters are not allowed:
%/:[]*|\t\r\n
Table Data Source Reporting Only From Source Type, select Database Table(s).
Under Connection, select a database connection.
Select Reporting Only, then select Next.
From Schema, select a schema.
A list of Available Tables appears.
Table Data Source Reporting Only Available Tables From Available Tables, select one or more tables.
Use the right arrow to move the tables to Selected Tables.
To remove a table, select it in Selected Tables, then use the left arrow.
To select multiple tables, hold Ctrl while selecting.
Select Next.
The Define Joins page appears.
From Left Table, select the fact table.
From Right Table, select the table to join.
Select Create Join to create the join.
Create join conditions for each join.
The join relationship appears in the lower section.
To delete a join, select the join, then select Delete Join.
Select Finish.
The Data Source Created window appears.
Select Keep default model or Customize model now, then select OK.
Table Data Source Reporting Only Data Source Created
Create a database table data source (Reporting and Analysis)
Your database must be running before you begin.
Sign in to the User Console.
Select Create New, then select Data Source.
Select New Data Source.
The Data Source Wizard opens.
In Data Source Name, enter a descriptive name.
These characters are not allowed:
Table Data Source Reporting and Analysis From Source Type, select Database Table(s).
Under Connection, select a database connection.
Select Reporting and Analysis, then select Next.
From Schema, select a schema.
A list of Available Tables appears.
Table Data Source Reporting and Analysis Available Tables From Available Tables, select one or more tables.
Use the right arrow to move the tables to Selected Tables.
To remove a table, select it in Selected Tables, then use the left arrow.
To select multiple tables, hold Ctrl while selecting.
From Fact Table, select a fact table, then select Next.
The join page appears.
Table Data Source Reporting and Analysis Define Joins From Left Table, select the fact table.
From Right Table, select the table to join.
Select Create Join to create the join.
To delete a join, select the join, then select Delete Join.
Create join conditions for each join.
Select Finish.
The Data Source Created window appears.
Select Keep default model or Customize model now, then select OK.
Table Data Source Reporting and Analysis Data Source Created
A multidimensional model is generated for use in Analyzer, Interactive Reports, and Dashboard Designer, or the Data Source Model Editor opens.
Before you access the new data source in the PDI client (Spoon), clear shared objects:
Select Tools > Repository > Clear Shared Object.
Last updated
Was this helpful?