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: CountryStoreUSAStore 1USAStore 2USAStore 3 When I add the fact measure Sales, notice that there are no sales for Store 3: CountryStore Sales AmntUSAStore 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 AmntUSAStore 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

nav[aria-label="Primary Navigation"] { padding: 0; & ul { list-style: none; width: 100%; display: flex; flex-direction: row; justify-content: start; align-items: start; gap: 30px; padding: 0; & li { margin: 0; } & ul li { list-style: none; } } }

Oracle Communities

Oracle Analytics and AI

Child Item

Count on dimension attributes not linked to facts

2981486

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:

Country	Store
USA	Store 1
USA	Store 2
USA	Store 3

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

Country	Store	Sales Amnt
USA	Store 1	1000$
USA	Store 2	2000$

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.

Country	Store	count (store)	Sales Amnt
USA	Store 1	2	1000$
USA	Store 2	2	2000$

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

Find more posts tagged with

Accepted answers

All comments

choracy69

Hi,

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

Thomas Dodds

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

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

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

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

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

Thomas Dodds

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

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