Creating a SQL-based data mart for an advanced report template
You can create a custom data mart for building an advanced report template that contains analyses, models and other customization that you frequently use. This template can be used to generate an advanced report according to your requirement. This topic describes steps for adding a data mart for building an advanced report template in BIRT.
Steps for creating a SQL-based data mart for an advanced report template using the basic options
Before you begin
Access the Data marts page. For more details, see Managing data marts for custom views, reports, and report templates.
To add a data mart for building an advanced report template
On the Data marts list page, select Add > Data mart based on SQL.
The Data marts wizard is displayed.
On the Welcome page, under Purpose of data mart, select Build advanced report template.
Under the Content of data mart, select one of the following options:
- Summary information for a set of entities: Adds a data mart that provides a summary information about the entities returned by the SQL query.
- Time series details for a set of entities: Adds a data mart that provides a time series details about the entities returned by the SQL query. When you select this option, columns returned by SQL query are checked to ensure that one column contains Timestamp set as the Meaning. This is automatically detected when the query is executed or can be manually set for the required column in the Columns page.
set values for the following properties.On the Configuration page,
By default, the Basic properties are displayed on the Configuration page. For viewing the Advanced properties, select the Advanced tab of this table.
Field Description Name
Type the required name for the data mart. Only alpha numeric values are allowed.
Description Type the required description for the data mart. Primary entities
Select any one of the following options:
- System: Ensure that the executed query returns at least one column that has System Identifier as the meaning.
- Business driver: Ensure that the executed query returns at least one column that has Workload Identifier as the meaning.
- Domain: Ensure that the executed query returns at least one column that has Application Identifier as the meaning.
Type SQL query to retrieve the required data by selecting the required columns of a table. You cannot use a query that contains macros.
, click Advanced. You do not need to set or modify this property unless you want to modify the list of data marts on which this data mart is dependent. This property is for advanced users and scenarios only.To view or configure the Advanced properties
Field Description Dependencies Select the required dependencies from the Data marts table, and add them to the Selection table using the >> button. In the Selection table, select the required dependencies, and add them to the Source table by using the << button.
On the Materializer page, specify the following details:
Materialization option Description
Always see fresh data
Select this option to load fresh data every time you access the advanced report template.
Note: Every time the SQL query runs, it fetches data directly from the database. Hence, a complex query might require more time to run and the performance might be impacted.
Create materialized table
Select this option to load materialized data in the associated advanced report template.
This option is useful when you have a complex SQL query and you want to see or use the fetched data quickly. The data mart is materialized and the data is stored in a new table, which is shown whenever you choose to Preview or view the data.
Specify the following settings:
From the Materializer task list, select the task to be used for materializing the data mart. You can select from the available out-of-the-box tasks or create a new one. For more information about creating a materializer task, see Configuring the Data Mart Materializer task.
Tablespace Enter the name of the tablespace where the generated table is materialized. If no value is specified, the default tablespace of the product is used. Materialize validity
Enter a number and select hour, day, or week to specify the time validity of the materialized data mart.
The data mart is not re-materialized if you run the associated materializer task again while the time specified in this field is still valid.
For example: Consider data mart A with Materialize validity of 6 hours. The associated materializer task for this data mart runs daily at 5 A.M.
If the associated materializer task runs again at 10 A.M., the data mart A is not re-materialized because the time specified in the Materializer validity field is still valid.
Select the number of blocks of indexes that you need. You can choose maximum 4 blocks.
For each index block, in the corresponding Index # columns field, select the indexes from the Source list and move them to the Selection list by using thebutton.
And, enter the Index # tablespace name.
(Advanced only): To specify access permissions to DB users and DB roles to the materialized table in the database, configure the following advanced settings:
Field Description Grant to DB users Select the database users from the Source table, and add them to the Selection table using thebutton. Grant to DB roles Select the required database roles from the Source table, and add them to the Selection table using thebutton. Grant to user specified roles Type the required user specified (defined) roles separated by semi-colon to give them permissions to use this data mart.
The new data mart is added in the Data marts list page and can be used to build an advanced report template.