Author |
Message |
frank35 Principal Member


Joined: 25 Aug 2005
              Posts: 401 Location: London

|
Posted: Wed May 22, 2019 12:44 pm Post subject: Dims with no facts |
|
|
I have 2 dimensions Client and Supplier. These are both joined to a fact table, finance. Some of my clients have no financials and are therefore excluded from the output. I need to report on these clients alongside those that do have financials. What is the best way to model this in IDT? Thanks.
BOE 4.1 |
|
Back to top |
|
 |
dtolley Senior Member


Joined: 14 Jul 2006
             Posts: 85

|
Posted: Wed May 22, 2019 5:07 pm Post subject: Re: Dims with no facts |
|
|
You would either need to address at the report level and write separate queries pulling all clients/suppliers and a second client/supplier/financial query and merge them in the report.
Or at the universe level change the joins to outer joins, but that could impact other reports. |
|
Back to top |
|
 |
joepeters Forum Fanatic


Joined: 29 Aug 2002
                 Posts: 6632 Location: Connecticut, USA

|
Posted: Thu May 23, 2019 10:06 am Post subject: Re: Dims with no facts |
|
|
The most proper way to do this would be to add new rows to the fact table. Your customer key would be populated but all measures would be zero or null. Then no additional work is required in the universe or reports.
There are two universe-only solutions I can think of:
1) Do a left join from the client table to the fact table. This is very likely going to cause other problems, and would also require left joins to other tables. Not recommended but might work in this specific scenario
2) Replace the fact table with a derived table that UNIONs the fact table with another query that plugs in the clients that do not have financials. This is a little more stable than the option above, since you can retain inner joins between the tables, but it could cause poor performance due to the additional logic. |
|
Back to top |
|
 |
Mark P Forum Devotee


Joined: 03 Feb 2003
                 Posts: 11455 Location: Manchester, UK

|
Posted: Mon Jun 10, 2019 8:03 am Post subject: Re: Dims with no facts |
|
|
I respectfully disagree Joe. I don't completely disagree, just that it's not the most proper way of doing it. That very much depends on volumes of data - turning your transactional sales fact table into a full-coverage sales fact table. If you have, say, 3,000 customers but only sell to 20 on average each day, that's a massive increase in disk space requirement and a potentially hideous performance decrease.
However, extending on that, you could have both the full coverage table and transactional tables, using aggregate awareness functionality to determine which table is used to satisfy different reporting requirements. In this case, you'd be able to create a predefined condition to force the use of the coverage table. _________________ Current version I'm using: 4.1 SP3 over Oracle. Well versed in SQL Server too, including SSIS
______________________________________
Prior versions used: BO3,4,5,6.5,XIr2,XI3, 4.1 - yes, I have been using BO since 1996! |
|
Back to top |
|
 |
dan4280 Forum Member


Joined: 07 Oct 2016
   Posts: 15

|
Posted: Wed Jul 03, 2019 4:35 am Post subject: Re: Dims with no facts |
|
|
Do people try and avoid outer joins altogether? I use them quite a lot, particularly on universes using tables in my organisation's ERP system where you cannot alter the data. Reporting performance is fine and it has not caused any issues on the ERP either. |
|
Back to top |
|
 |
Mark P Forum Devotee


Joined: 03 Feb 2003
                 Posts: 11455 Location: Manchester, UK

|
Posted: Wed Jul 03, 2019 7:59 am Post subject: Re: Dims with no facts |
|
|
dan4280 wrote: | Do people try and avoid outer joins altogether? I use them quite a lot, particularly on universes using tables in my organisation's ERP system where you cannot alter the data. Reporting performance is fine and it has not caused any issues on the ERP either. |
In dimensional modelling, you should have no need for an outer join; dummy surrogate keys prevent the outer join.
In OLTP systems, an outer join is far more common as you are querying a system that is designed for data input and general usage rather than to be reported against. Large data volumes and excessive outer joins can cause significant slow downs on certain systems. _________________ Current version I'm using: 4.1 SP3 over Oracle. Well versed in SQL Server too, including SSIS
______________________________________
Prior versions used: BO3,4,5,6.5,XIr2,XI3, 4.1 - yes, I have been using BO since 1996! |
|
Back to top |
|
 |
Debbie Forum Addict


Joined: 01 Mar 2005
              Posts: 2781 Location: Cheshire, England

|
Posted: Wed Jul 03, 2019 8:24 am Post subject: Re: Dims with no facts |
|
|
I use outer joins all the time, but I'm mostly querying relational data.
Debbie |
|
Back to top |
|
 |
RikDeclercq Forum Associate


Joined: 28 Sep 2006
             Posts: 567

|
Posted: Fri Jul 05, 2019 2:52 am Post subject: Re: Dims with no facts |
|
|
We also use outer joins. We used to avoid outer joins and used surrogate keys. But because of frequent reloads this became unmanageable. |
|
Back to top |
|
 |
|