DMR query debug

[michalb]

Hi guys,
I am building DMR model agains SAP HANA database and getting error with basically no details (just contact admin).
This happens when I create regular dimension on top of 2 joined tables.
Everything works if I create dimension on top of 1 single table only.
I tried to define join directly in SQL but also via relationship in FM.
Both failed.

While I have limited access to SAP HANA logs, how to get SQL that cognos generates in order to get relational data into DMR? In test query there is just MDX that does not make much sense, but at the end of the day it must comunicate with database via SQL.

Thanks!

[cognostechie]

Hi

DMR should be created from a start schema design database so essentially, all attributes of a dimension should be in one table. If not, define the join in the database layer ad then create a Model Query Subject in the Business Layer. This Model Query Subject will have all attributes related to that dimension and then use this to feed the DMR. This definitely will be a bad practice because you will be joining two Master/Dimension tables in the database layer and the joins should only be between Dimension and Fact tables. However, this might work.

The other thing you could do is hand write a SQL which will have all columns from the two tables which have attributes and then use this SQL to create a Database Query Subject in the Database Layer. Create a MQS in the Business Layer and then use that MQS to feed the dimension of the DMR