Report with fields from Multiple Fact Tables

Question

Hi Team,

I have a requirement where I need to create a report from 3 different fact tables.

All the 3 tables have same dimension conforming.

They work independently without any issue.

But when I combine fields from different fact tables together then I am getting error -

Error Codes: OPR4ONWY:U9IM8TAC:OI2DL65P

State: HY000. Code: 10058. [NQODBC] [SQL_STATE: HY000] [nQSError: 10058] A general error has occurred. [nQSError: 43113] Message returned from OBIS. [nQSError: 14025] No fact table exists at the requested level of detail

Can someone help me on this?

Its the same dimensions and still this is not working.

Do I need to create join between 2 fact tables as well?

The only issue is that not both table have data for every combination of dimensions. For example: - I have got tables CUTSOMER SHIPMENTS and CUSTOMER ORDERS.

Now during given week not every Customer - Item combination will have both orders and shipments.

Thanks

Gianni Ceresa · Answer

Are you saying in your fact tables you have attributes without aggregation rule on them?

Well ... of course that can't work.

A text isn't a measure and has nothing to do in a fact table.

Look at degenerated dimensions, it's how to correctly model attributes coming from the physical table being the source for a fact table.

Adi Md · Answer

I did !!!

I even tried changing from Logical Level to Columns and still have same issue.

I guess the issue is due to some details cannot be aggregated.

For example: - In CUSTOMER_SHIPMENTS table the column 'WAYBILL' is VARCHAR that stores a text. Now this cannot be aggregated, right?

Can you confirm if this is the issue and resolution if any?

[Deleted User] · Answer

+1 to Gianni. It's all in the content levels