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