Count on dimension attributes not linked to facts — Oracle Analytics

Oracle Analytics Cloud and Server

Home› Oracle Analytics› Oracle Analytics Forums› Oracle Analytics Cloud and Server› Oracle Analytics Cloud and Server

Categories

Welcome to the Oracle Analytics Community: Please complete your User Profile and upload your Profile Picture

Count on dimension attributes not linked to facts

Received Response
11
Views
8
Comments
2981486
2981486 Rank 3 - Community Apprentice
edited Aug 13, 2024 7:06PM in Oracle Analytics Cloud and Server

Hello,

I am trying to create a report that shows me the sales amount by country and store (both belong to the same Location Dimension).

My Location Dimension:

CountryStore
USAStore 1
USAStore 2
USAStore 3

When I add the fact measure Sales, notice that there are no sales for Store 3:

CountryStore
Sales Amnt
USAStore 11000$
USAStore 22000$

Now I need to count the number of stores by country. If I add a count (Store), I get 2 because it counts only the stores that had sales associated to them.

CountryStorecount (store)Sales Amnt
USAStore 121000$
USAStore 222000$

How can I also include Store 3 in my count?? I need the count (store) column to display 3 instead of 2.

Thank you,

Joao

Answers

  • choracy69
    choracy69 Rank 6 - Analytics Lead

    Hi,

    I think that you could display null value and then count should be working ok.

  • Thomas Dodds
    Thomas Dodds Rank 8 - Analytics Strategist

    create a new logical table as a fact with the dimensional table as the source ... join it to your dimension (set it's content level to the base of the dimensional hierarchy) and aggregate the PK (count distinct) and name it Store Count ... now you can relate the sales facts with the count facts through the conformed dimension.  The Physical SQL will look like a self join:   IE:  Select from dim, fact, dim as fact ...

  • 2981486
    2981486 Rank 3 - Community Apprentice

    Thank you Thomas, it worked. But I soon as I add another dimension to my analysis (ex Time), the Store Count is null because I get "None of the fact tables are compatible with the query request Store Count".

    In other words, my new Fact "Location" is not joined to Dim Time. Does it make sense to join it in the logical layer?

  • Thomas Dodds
    Thomas Dodds Rank 8 - Analytics Strategist

    If you need counts by/over time, then you'll need to build a fact table that records the counts that way ... so it's an intersection of store key and time key ... facts can only be correlated across conformed dimensions.

    For now .. make sure you have the content levels set correctly ... you might need to set the rest on this new fact LTS for total levels (all time, all products, all dimension of your choice, etc)

  • 2981486
    2981486 Rank 3 - Community Apprentice

    Hello, thanks for answering.

    Nope I don't need a count over time. I just need to filter my sales for a specific year in the same analysis.

    As soon as I add the Year=2016 filter, my Store Count measure disappears

    I have the same problem as obiee_kid in this old thread: OBIEE - How to get count of members for a level?

    Even if I add the other Dimensions to Total in my Fact Location, it still doesn't work.

  • Thomas Dodds
    Thomas Dodds Rank 8 - Analytics Strategist

    You have done what is described in this blog?  http://gerardnico.com/wiki/dat/obiee/dimension_measure

  • Thomas Dodds
    Thomas Dodds Rank 8 - Analytics Strategist

    Also ... take a look here for tips on how to model where/when not every dimension joins to every fact (and vise versa)...

    What To Do When OBIEE Fact Tables Do Not Join to All Dimension Tables?

  • 2981486
    2981486 Rank 3 - Community Apprentice

    Thank you! It worked after setting the Levels tab for my Store Count measure to Total in all dimensions except Dim Location.