Documentation on TTMP tables in Compose

[jtompkins]

I was wondering if someone could explain the purpose of each TTMP table used in the data warehouse and data mart processing. There are several with esoteric table/column names and the "Description" in the generated instructions are as vague as "Populating TTMP table". It would be helpful to know how these tables are processed when troubleshooting reasons why a record is not making it into the target table, for example. 

Data Warehouse
TTMP_1733_Ranks
TTMP_1733_1_DTS_ROWS
TTMP_1733_1_ADM_ROWS_APP_S
TTMP_1733_1_BND_SNGL_ROWS
TTMP_1733_1_ADM_ROWS_NEW_M
TTMP_1733_1_BND_MULT_ROWS

Data Mart
TTMP_1748_DIM_MUT
TTMP_1748_DIM_FHO
TTMP_1748_DIM_BTM
TTMP_1748_DIM_BND

[jfgiudicelli]

Hi , did you have any return ? i have an another point we saw on Compose for datawarehouse the hub and satellite, I wonder in which table are stored the relations

Thanks 

[jtompkins]

No, I have yet to receive a response, and at this point, don't expect to. 

[jfgiudicelli]

Thanks i try by an another way if i have a return i give you the information 

[robertcur]

Hi Jtompkins,

I'm no expert, but I will give this question of yours a go.  I've been using Qlik Compose for about 18 months, and with a lot of struggle (blood, sweat and tears), kind off understands the machine and dynamics.  TTMP tables are exactly that.  They are temporary tables used in the process to update your warehouse. 

• The TTMP_ is to indicate that it is temporary and will be cleared every time it is used.
• 1733 / 1748 / etc.  These point to the internal ID of the Entity (or tables) which can be seen in the Compose database.
• The rest indicate what the purpose of the temp table is (note my explanation below is my own deductions and might be wrong). 
  • For example, MSNG will be to find missing references, i.e. when a fact is read in pointing to a dimension, but the dimension is not yet in the warehouse.  It will keep a placeholder and update the dimension when it is received into the warehouse.
  • DTS_ROWS would be to rows of a specific table to determine if anything on that row has changed, and if that entry (row) should be updated in the warehouse.
  • And so on.  You can kind off determine the use of each type looking at the Task Statements.

Sorry, did not answer directly to what each type is for, but a nifty way to see what is in there, is to change the compose setting "Do not drop temporary tables", and view them for yourself.

Hope this helps a little.

[robertcur]

Jfgiudicelli,

Relations in the warehouse is not really explicit as you may think.  Again, my view not the books speaking.  You will see that there is an ID column in the HUB and an ID column in the S01/S02/S*.  These ID's are related.  So if you like to get a specific entry and your primary key (from source) is in the HUB, you can do something like this:

SELECT * FROM TDWH_My_Table_S01 sat

INNER JOIN TDWH_My_Table_HUB hub

ON sat.ID = hub.ID

WHERE hub.MyPrimKey = 1234;

This is the "relation" between these two tables.  

Looking at the relationship between two different tables, as defined in the entities, you can find this on either the HUB (if that relationship is type 1) or S01 (S02/S*) if that is type 2 in the entity.

What you will see, is the name of the field (normally with a prefix defined in the entity) which points to the ID field of the related table.  Example: In MyTab1 (left of the relation picture) there is a relationship (type 2) to MyTab2 (more to the right on the relation picture).

SELECT * FROM MyTab1 t1

INNER JOIN MyTab2 t2

ON t1."TheFieldThatLinkToOtherTable" = t2.ID

AND t1.TD > t2.FD AND t1.TD <= t1.TD;

(and please remember the DATE as FD&TD will create multiple lines if not correctly queries)

Hope this helps