Relationships / Cross join

[Guest_imported]

Hi,

How does Microstrategy join tables if the column names are different? I have a master Calendar table with Calendar_Dt as the primary key. I also have two tables Invoice and Payment with Invoice_Dt and Payment_Dt. Now when I join Invoice with Calendar for a report it is doing a cross join between the tables but actually it should join Invoice_Dt with Calendar_Dt. Same is the case with Payment.

Also it is possible to drill from a graph.

Thanks for any help in this regard.

Greggie

 

[Guest_imported]

Usually Attributes are linked (joined) by a Fact Table.
If you are creating a report that does NOT use any metrics from a table that include Invoice_DT and Payment_Dt, you will get a cross join. I'm not sure why you have a master calendar table....What attributes are associated with the calendar table. Theorectically, you should have one lookup table per attribute. Therefore, it may be easier to use the master calendar table w/ one of the Date attribute (Invoice_Dt) and then use a table alias of the calendar table with Payment attribute.


You may also what to check each attribute and make sure you have included Calendar_Dt as an ID for the Invoice and Payment attributes. MSTR supports heterogeneous column naming.

Drilling from a graph is possible in 7.2.

 

[Guest_imported]

bi_consultant,

Thanks for your reply.

Calendar table contains Quarter_Of_Year, Month_Of_Year, Week_Of_Month, Year as attributes so that i can do reports based on those attributes. Most of the reports needs to use Collections based on Month, Week etc. So I have a table which contains those mappings. Also the model is not Dimensional but it is ER Model(3rd Norm). Also can you explain how I can specify link Payment_Dt with Calendar_Dt if i use any of those attributes.

Many a thanks

Greg

 

[Guest_imported]

How do i make Calendar_Dt as ID for Payment_Dt and Invoice_Dt. If I open Payment_Dt attribute and add id it displays only the attributes from Payment table. Same is the case with Invoice.

I want Microstrategy to join Calendar_Dt with Payment_Dt or Invoice_Dt depending on the situation

thanks

Greg

 

[Guest_imported]

When you create the attribute forms, you should notice a button that says 'Modify, Delete, New.' First, highlight the ID of the attribute in attribute editor and select 'Modify.'
Then select 'NEW.' You want to ADD an additional column as the ID. You will be able to select any column from any table in the warehouse catalog. MSTR 7.2 allows for heterogeneous column mapping.
You need to do this for each of the Attributes.

let me know if you need further assistance.

 

[Guest_imported]

When you create the attribute forms, you should notice a button that says 'Modify, Delete, New.' First, highlight the ID of the attribute in attribute editor and select 'Modify.'
Then select 'NEW.' You want to ADD an additional column as the ID. You will be able to select any column from any table in the warehouse catalog. MSTR 7.2 allows for heterogeneous column mapping.
You need to do this for each of the Attributes.

let me know if you need further assistance.