BOB: Business Objects Board
Not endorsed by or affiliated with SAP

Register | Login 

Follow BOB on Twitter! 
Follow BOB on Twitter! (Opens a new window)  

General Notice: BOB is going to retire...please see details here.
General Notice: No events within the next 45 days.

Dims with no facts


 
Search this topic... | Search Semantic Layer / Universe Designer... | Search Box
Register or Login to Post    Forum Index -> Semantic Layer / Universe Designer  Previous TopicPrint TopicNext Topic
Author Message
frank35
Principal Member
Principal Member



Joined: 25 Aug 2005

Posts: 401
Location: London


flag
PostPosted: 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
Senior Member



Joined: 14 Jul 2006

Posts: 85



PostPosted: 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
Forum Fanatic



Joined: 29 Aug 2002

Posts: 6632
Location: Connecticut, USA


flag
PostPosted: 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
Forum Devotee



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


flag
PostPosted: 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
Forum Member



Joined: 07 Oct 2016

Posts: 15



PostPosted: 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
Forum Devotee



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


flag
PostPosted: 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
Forum Addict



Joined: 01 Mar 2005

Posts: 2781
Location: Cheshire, England


flag
PostPosted: 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
Forum Associate



Joined: 28 Sep 2006

Posts: 567


flag
PostPosted: 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
Display posts from previous:   
Register or Login to Post    Forum Index -> Semantic Layer / Universe Designer  Previous TopicPrint TopicNext Topic
Page 1 of 1 All times are GMT - 5 Hours
 
Jump to:  

Index | About | FAQ | RAG | Privacy | Search |  Register |  Login 

Get community updates via Twitter:

Not endorsed by or affiliated with SAP
Powered by phpBB © phpBB Group
Generated in 0.0289 seconds using 17 queries. (SQL 0.0029 Parse 0.0009 Other 0.0251)
CCBot/2.0 (https://commoncrawl.org/faq/)
Hosted by ForumTopics.com | Terms of Service
phpBB Customizations by the phpBBDoctor.com
Shameless plug for MomentsOfLight.com Moments of Light Logo