Best datamodel for a generic table

[fabdulazeez]

I have a generic table with below fields
ID,
TYPE1,
TYPE2,
TYPE3,
TYPE4,
"COUNT"

What will be the best way to put this table in data model where the number of records expected is 9999999

1)Use a generic Load and store all tables created as individual QVD files with a for loop and map it to the actual fact table with ApplyMap. 
Since each TYPE has 2 to 8 distinct values, the number of QVDS could be too many

DATA:
Generic
LOAD
ID,
TYPE1 & '_' & TYPE2 & '_' & TYPE3 & '_' & TYPE4,
"COUNT"
resident GENRICTABLE ;


2) Use if condition for each fields and have association to the other fact table. Number of If condition could be too many.

DATA:
LOAD
ID,TYPE1,
TYPE2,
TYPE3,
TYPE4,
if (match(TYPE1,'J'),if (match(TYPE2,'P'),if (match(TYPE3,'D'),if (match(TYPE4,'C'),COUNT)))) as JPDC,
.
.
.
if (match(TYPE1,'K'),if (match(TYPE2,'P'),if (match(TYPE3,'D'),if (match(TYPE4,'C'),COUNT)))) as KPDC,
resident GENRICTABLE ;

3) Write multiple left joins to actual fact table

Left join(FACT)
Load ID,
COUNT as JPDC from GENRICTABLE where TYPE1='J'
and TYPE2='P' and TYPE3='D' and TYPE4='C';
.
.
.
Left join(FACT)
Load ID,
COUNT as JPDC from GENRICTABLE where TYPE1='K'
and TYPE2='P' and TYPE3='D' and TYPE4='C';

I understand that there are lot of factors like data density for each types ,my final data requirement etc. to be considered. It will be helpful if any one could provide a direction in which I will have to do the thinking.

@sunny_talwar Can you please provide your thoughts 

[marcus_sommer]

I think I would do it differently by creating a mapping table from it by combining the type-fields and/or them also with the id-field or maybe transforming the crosstable type-fields into a data-stream and loading the mapping from it. Further doing all kind of grouping in this stage (not later in any if-loops).

Further I would try to merge everything within a single fact-table to create a star-scheme data-model which is also officially recommended as best compromise in regard of efforts and performance.

- Marcus

[vidyutverma]

Will be good to get some sample data. 

[fabdulazeez]

Yeah I have attached dummy Data. The actual fact table is huge with 100's of fields

[marcus_sommer]

Beside my suggestion from above I'm not sure that I would classify the tables like you did. Your fact-table has an ID + Flag + two dates and no measure and the generic-table has an ID + four type-dimensions + a measure. Maybe this is caused from the simplifying of the example-data but it could lead to a misinterpretation of the real scenario.

However my conclusion remained - try to merge all (relevant) measures into a single fact-table and using a star-scheme.

- Marcus

[fabdulazeez]

As I mentioned, actual Fact table has lot of fields, most of which are dates. The measure we are mostly focused is the count of Ids.

I could use both table as it is in the model and achieve most of the required measures from the generic table using set analysis.

But I was thinking if a star schema could provide a better performance . Also If I build this as a data model , It could be used by other users.

For creating a mapping table directly from the generic table as you mentioned, I think we will need a lot of mapping table built from generic table with different where conditions. So only I thought if I could use the Generic prefix to create all those mapping table.

For vTableNo = 0 to NoOfTables()

Let vTableName = TableName($(vTableNo)) ;

If Subfield(vTableName,'.',1)='DATA' Then

Store [$(vTableName)] into $(QVDPathDataModel)LOAD/$(vTableName).qvd;

// Drop Table [$(vTableName)];

End If

Next vTableNo

[marcus_sommer · Accepted solution]

From your provided information I couldn't comprehend why it should be necessary and/or beneficial to read the table n times with any filter to create n mapping-tables or join-measurements in any loop-approaches. I think it could be done within a single step.

Further you mentioned multiple date-fields in your fact-table. Here I suggest to consider a transforming with a crosstable-logic at least for the association within the data-model. Possible date-differences, flags, any scoring or similar might be made in beforehand or in parallel.

- Marcus