Join 2 Fact tables where no conformed dimension is present

[bbrooksux]

I'm trying to figure out how to link 2 rather large fact tables (WFItem and Document) with no discernable conformed dimension.  There is only a primary key that would link the 2 tables normally, however since they are both large tables the performance in querying these joined tables is insane.  And when building star schemas based on both fact tables and then trying to link the 2, I'm having difficulty figuring out how to do this without having that all important Conformed Dimension.  Any tips or tricks you guys use?

Currently on 8.4, Fix Pack 2

[blom0344]

When you state that no conformed dimension exists, then why the attempt to 'link' them?

[bbrooksux]

We have need to link them because we need to get fact data from both fact tables into one report.  And creating one massive fact table has proven to be extremely burdensome query-wise on our servers.

[jabsey]

Create a "virtual" conformed dimension in Framework Manager with an appropriate type of join query (mandatory relationship => use inner join; optional => use left, right, or full outer join). Let's say you wish to include facts from both Invoices and Invoice Items within single analyses. Between an Invoice table and an Invoice_Item table, with an optional relationship between both, the following type of expression should be specified within a Data Source query subject:

******************************************************************************
******************************************************************************

Select

     Invoice.Invoice_ID, Invoice_Item.Invoice_Item_ID

From

     [<data source>].Invoice Invoice

Left Outer Join

     [<data source>].Invoice_Item Invoice_Item on (Invoice.Invoice_ID = Invoice_Item.Invoice_ID)

******************************************************************************
******************************************************************************

Name this Query Subject something like "Invoice_Invoice_Item_Conformed_Dimension"

Then specify the appropriate relationships, cardinality, and determinants. For example, in this case the relationships would be as follows:

Invoice.Invoice_ID
1..1
1..n
Invoice_Invoice_Item_Conformed_Dimension.Invoice_ID
Invoice_Invoice_Item_Conformed_Dimension.Invoice_Item_ID
1..1
0..1
Invoice_Item.Invoice_Item_ID

Cardinality should be as follows:

Invoice query subject:
=================
Invoice_ID -> Uniquely Identified

Invoice_Invoice_Item_Conformed_Dimension query subject:
=============================================
Invoice_ID -> Group By
Invoice_Item_ID -> Uniquely Identified

Invoice_Item query subject:
======================
Invoice_Item_ID -> Uniquely Identified

This should allow for cross-fact queries between fact tables which have aggregations at different levels of granularity.

-jabsey

[bbrooksux]

Perfect!  Preliminary testing shows that this is working the way I'm needing it to work.  Now to my followup question:
If each of my 2 fact tables holds millions of rows of data, what performance risk is there in utilizing this method in joining the two tables?  And are there any ways to improve performance either on the FM side or on the Report Studio side?

[jabsey]

Performance tuning is a potentially lengthy topic, and takes into account many  variables. At a high level, consider the generated SQL. Security implementation could also be a factor, but usually is not. A basic premise for performance, though, is having keys indexed on the database. -jabsey

[blom0344]

No matter how tables are indexed, if you need to model left/right or full outer joins your performance will take a nosedive. Depending on the RDBMS index based execution paths may turn into full table access paths. It is impossible to give you proper advice here. You need to take the generated SQL to a DBA for further advice.

[napster_gr8]

@jabsey
excellent resource provided... However i have a more critical concern;
1. What about Joining 2 fact tables where conformed dimension is present?
In that case, how can we avoid loop joins and/or ambiguous query join issue in case a user intends to create an ad-hoc report
I have tried using Shortcuts (used both as 'Reference' and 'Alias') but unable to prevent the loop join.

Help desperately required.