1

When combining multiple fact tables using shared dimensions you need to use a drill-across query or a multi-pass query to get the correct results.

I'm looking for a BI tool that does this correctly based on recognising which tables are fact tables and which are dimension tables. The tool should preferably generate a postgreSQL query.

For most of the tools that I've been looking into, you need to recognise these situations and write SQL manually to fix this.

Are there any tools that will generate the correct queries for you without the need for writing the multi-pass or drill across yourself?

Bart Jonk
  • 365
  • 3
  • 14
  • 1
    I'm commenting since I do not have a positive answer for you. I have been doing reporting out of databases for almost thirty years. I have yet to encounter a reporting tool that does not need hand-written SQL intervention. – Mike Organek Jul 09 '20 at 11:25
  • Are you looking for the capability of the data source setup when you can conditionally choose what facts tables or JOINs to include into SQL query depending on the dimensions/measures of the report (selected by the end-user)? I know which BI tool supports that. – Vitaliy Fedorchenko Jul 10 '20 at 08:09
  • I'm looking for a BI tool that can correctly combine 2 fact tables that have 1 or more shared dimensions. e.g. fact_sales (all retail sales transactions for shops, on a date, for a product) and fact_purchase (wholesale purchase transactions for the same shops, certain dates, certain products). Creating an overview with both the sales & purchases for all dates and all product per shop cannot be done using a simple join, it will require multipass or drill-across SQL – Bart Jonk Jul 14 '20 at 15:08

0 Answers0