Dims with no facts

[frank35]

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

[dtolley]

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.

[joepeters]

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.

[Mark P]

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.

[dan4280]

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.

[Mark P]

[Debbie]

I use outer joins all the time, but I'm mostly querying relational data.

Debbie

[RikDeclercq]

We also use outer joins. We used to avoid outer joins and used surrogate keys. But because of frequent reloads this became unmanageable.