Technical Blog Post
Abstract
Making your life easier: Creating and Maintaining Dimension tables ITM 6.3
Body
In order to create the dimension tables, we must first configure the historical data collection for the following attributes depending on the OS we are setting reports:
Type | Attribute group | Table | Summarize |
---|---|---|---|
Linux | Linux IP address | Linux_IP_Address | Daily |
UNIX | Unix IP address | UNIX_IP_Address | Daily |
Windows | Computer Information | NT_computer_information | Daily |
S&P agent | KSY summarization config | KSY_Summarization_Config_DV | Daily |
IBM i | Miscellaneous | IOS_Miscellaneous | Daily |
For example, for windows we should have:
From ITM 6.3 , there are two ways to create and maintain the dimension tables:
- Using the summarization and pruning agent
- Manually (as previous versions )
In this section we are going to use the S&P agent to do it but if you want to continue to manually update the dimension tables as it had to be done in versions older than ITM v6.3 you can still do it.
Best Practice:
1.Configure the S&P agent to maintain the dimension tables without starting it after configuration.
2.Create the dimension table using the “schema Publication tools” in update mode . Doing that we create both groups of tables:
- Share Dimension tables:
TIME_DIMENSION, MONTH_LOOKUP and WEEKDAY.
- Resource dimension tables:
MANAGEDSYSTEM, MANAGEDSYSTEMLIST and
MANAGEDSYSTEMLISTMEMBERS.
3.Restart the S&P agent.
1. Configuring the S&P agent to maintain the dimension tables:
1.Open the S&P agent env. variable file
Windows: KSYENV
Linux/Unix : sy.ini
2.Configure/add the following env. variables:
- KSY_TRAM_ENABLE=Y (default is N)
- KSY_TRAM_TD_GRANULARITY=10 (default is 5 minutes, it is the number of minutes that the data is inserted into the TIME_DIMENSION table.
- KSY_TRAM_TD_INITIAL_LOAD=12 (default 24 moths, it is the amount of data in months to be loaded into the TIME_DIMENSION.
3.Save without restarting the S&P agent.
Now, the S&P agent has been configured to maintain the “resources dimension tables” .
2.Creating the dimension tables using the Schema publication tool:
1.Open the file:
windows: <home>\TMAITM6\tdwschema.rsp
Linux/unix: <home>/arch/bin/tdwschema.rsp
2.Set these env. variables:
- KSY_PRODUCT_SELECT = updated
- KSY_TABLE_FILTER = TIME_DIMENSION, MONTH_LOOKUP, WEEKDAY_LOOKUP
- KSY_SQL_OUTPUT_FILE_PATH = (optional file path for sql output)
3.Ensure the TEPS is started
4.run: tdwschema -rspfile tdwschema.rsp
IMPORTANT: you have to be a database administrator
5.Run the following scripts in order with an user having admin access to the DW database. If you are using Oracle or MSSQL server you must run the scripts as the TRAM user “IBM_TRAM”.
- db2 connect to WAREHOUS (with db2admin)
- db2 -tvf tdw_schema_table.sql
As we can see the share dimension tables were created:
- db2 -tvf tdw_schema_index.sql
- db2 -tvf tdw_schema_view.sql
- db2 -td# -f tdw_schema_function.sql
- db2 -tvf tdw_schema_insert.sql
Now the table has been filled in:
Note: for Oracle or MSSQL server to check , page 498 for ITM6.3 administration guide.
3.Starting the S&P agent
1.we are going to find how the TIME_DIMENSION table has been populated:
IMPORTANT:
- The MANAGEDSYSTEM table is populated using information from the WAREHOUSETCRCONTROL table. This table, WAREHOUSETCRCONTROL, is created on the first successful scheduled execution of the S&P agent.
- It is the responsibility of each agent to put entries in the WAREHOUSETCRCONTROL table. In this case, the installer for the OS report package will promts us to provide JDBC connection details and credentials for the TDW database. This RegisterPackage script will insert data into WAREHOUSETCRCONTROL table. After this step, the MANAGEDSYSTEM and TIME_DIMENSION tables are kept up to date automatically by the S&P agent.
2.“The Resource dimension” and WAREHOUSETCRCONTROL tables have been created after the first successful scheduled execution of the S&P agent.
Thanks for reading, Fran.
Check out all our other posts and updates:
Academy Blogs: http://ibm.co/1sPj9E8
Academy Videos: http://bit.ly/1wFKveY
Academy Google+: http://bit.ly/1sR5QTV
Academy Twitter Handle: http://bit.ly/1CknfoF
UID
ibm11276600