Incorrect WHERE condition in SQL

[ShawnEvans]

Hello,

I've been having a problem with a number of the tables in my datawarehouse. The warehouse I'm working with has a star structure, which means I have a bunch of dimensions and a single fact table relating them. A few of my dimensions are referenced multiple times in the fact table through the use of FK's. For example, in my warehouse there is a Time table. This table contains a TimeID, and number of different ways to interpret time (year, month, week, day, etc.) In my fact table I have 6 instances of a TimeID: Class_start_date, class_end_date, etc.

The problem I'm experiencing in Transformer is that when I make the source of my level an instance of a Time ID in my fact table, lets say class_Start_date, and then make the lable the calendar_year in my Time table the WHERE condition in my data source viewer will always set the condition as Time_ID = Birth_Date, rather than Time_ID = class_start_date. Has anyone experienced anything like this before? For each instance of a Time ID do I have to create a new entity to relate it to? These symptoms are consistent for any table with multiple instances of an ID in the fact table (but obviously they don't all point to Birth_date). Any input would be appriciated.

-Shawn

 

[JGirl]

Hi,

Are your data sources in the cube IQD files created from Impromptu? If they are, you may need to alter the way you setup the tables in the impromptu catalog.

For the rest of the post, im assuming that you are using IQD data sources generated by Impromptu.

For example, in the catalog, instead of having only one instance of the Time table in the catalog, and having all the fact tables that use the Time table joined to the one table instance, you could add table aliases for the each of the fact table links to the time table (and call them something like 'TIME_ALIAS_FOR_CLASS_START_DATE', 'TIME_ALIAS_FOR_CLASS_END_DATE' etc (You are prompted to enter the alias name). After you have created the aliases, when you have a look at the catalog tables, you will see in brackets behind the name of the alises the name of the table (in this case Time).

Then all you need to do is set up the joins between the Time aliases and the fact tables, create your reports & IQD files and the problems with the incorrect WHERE clauses should be sorted.

Let me know how you go.

Cheers
J

 

[ShawnEvans]

Hello,

Thanks for the reply to my problem. I'm actually not using Impromptu, however I do believe your answer will set me straight. I'm strictly using Architect to create the data models, and then using these models as the base for the Cubes in transformer.

I half suspected I would have to do something like aliase the tables, but I just wanted there to be a slicker way to handle tables that have multiple joins.

I guess my next question is do you know how to aliase a table in Architect? I've been creating "Normalized Entities" out of my DIM_TIME table for each instance of time, which more or less equates to an aliase. The process was nearly the same, create a new entity, give it a name relevant to the instance of time I'm relating, and finally relate the new entity to the fact table as a new relationship. If you know of a better way to do this, i'd be more than willing to listen. Thanks for the help.

-Shawn

 

[JGirl]

Hi,

I haven't used architect before, so I have no idea if there is a better way to relate the tables. Sorry!

Good luck!
J