Is It Possible to create Multiple Dimensional Tables?

[jgarciaf106]

Hi,

Is It possible to create Multiple Dimensional Tables?

Say For Example I have 3 Data Tables, within those Tables There is Common Dimension Such Month, Country and Region,

I would Like to create a Dimensional table that Contains all Country, another Dimensional table with Month and another with Region.

I Tried the following Script but after loading the script it create a loop:

[Dimensional link table 1]:

Load Country,

Country As %Country from Table1;

Load Country,

Country As %Country from Table2;

Load Country,

Country As %Country from Table3;

[Dimensional link table 2]:

Load Month,

Month As %Month from Table1;

Load Month,

Month As %Month from Table2;

Load Month,

Month As %Month from Table3;

[Dimensional link table 3]:

Load Region,

Region As %Region from Table1:

Load Region,

Region As %Region from Table2:

Load Region,

Region As %Region from Table3:

Any Suggestion.

[Peter_Cammaert]

The issue you are facing is probably not with your global dimension tables, but with your three "Facts" tables. Have a look at your data model using the Table Viewer (Ctrl-T in QV Desktop).

If you keep them separate, you'll have to create a link table with composite keys consisting of existing value combinations of Country, Month and Region. Store the Dimension details in the link table as well and then drop the Dimension tables altogether. That way, you'll create an inside-out data model without synthetic keys or circular references.

A better approach (but not always practical or possible)  is to concatenate all Facts tables together, mark each data set with a special tag to indicate which source table they came from, and connect the Dimension tables from your OP back to the single Facts table. Performance may improve considerably.

[nehasaxena]

Hi,

As Peter said, You need to use link table concept to get a common dimension table.

For understanding link table please see the attached document.

Regards,

Neha