OBIEE 12c. Empty measure value

Frog Toad

Hi!

How fix empty measure-value, when it measure unrelated with dimension?

Example:

I have 2 views:

V_STORE:

Column name
STORE_ID
<..MANY_OTHER_FIELDS..>
TRADING_AREA
REGION_ID

V_CALENDAR

Column name
CDATE
MONTH
YEAR
<..MANY_OTHER_FIELDS..>

These two tables are not related the physical level:

Table "STORE" is used as dimension, and as measure by field "TRADING_AREA" (default aggregation rule: sum). It is 2 difference logical table in Business-level in repository. This two logical tables are related in business level.

Table "CALENDAR"  is used only as dimension.

Business model diagram:

Now, if i make analysis, where i choose fields from "Store", example REGION_ID (from store-dimesnion) and TRADING_AREA (from store-measure), i get result:

But if i drag field from table Calendar (example "Year"), then value of TRADING_AREA will be empty:

How to make the value "TRADING_AREA" was displayed for every calendar item?

Example (without calendar):

REGION_ID	TRADING_AREA
1	1000
2	5000

And with year

YEAR	REGION_ID	TRADING_AREA
2000	1	1000
2000	2	5000
2010	1	1000
2010	2	5000
2016	1	1000
2016	2	5000

Gianni Ceresa

Hi,

You don't have a single physical join or logical join and you are surprised that OBIEE can't know how to join your tables together?

I guess you forgot the key element of the RPD: join things together so that OBIEE can know how to generate queries to find answers to the analysis you build in the front-end.

You must join your 2 physical sources and you must join it also in the business model, after that you will probably have better results.

There must be a relation between a calendar and your store view... You trying to make a report by year, so there must be a way for you to join these 2 things together.

Frog Toad

Gianni Ceresa

You must join your 2 physical sources and you must join it also in the business model, after that you will probably have better results.

Yes, agree, it's work when object have relations with tables-dimension by specially fields.

Example - in this subject area i have other table - "Indicator"

INDICATOR
CDATE
STORE
INDICATOR_ID
VALUE_INDICATORINDICATOR

This table have relations with tables "STORE" and "CALENDAR" in physical-level and business-level. And yes, when i choose some fields of dimension "STORE" or "DATE", measure "VALUE_INDICATOR" in OBI is displayed correctly.

But it's bad, that need make relations for all tables.

For example in SSAS, if i don't do relations between table-measure and table-dimension, then SSAS automatically will display the value for each element of the dimension-table

Example (this example maked in SSAS):

(table-measure "element" haven't relation with table-dimension "calendar")

1. If i display only measure "CountOFElements", i get only total-value

2 If i draged any fields of the table "calendar", this total value will be displayed with any calendar-item. Now i drag field "Year"

Now - "Date"

I thought that in OBI, i can do something similar

Gianni Ceresa

Well ...

It can works for dimensions values only, but as soon as you have a fact OBIEE requires joins. A logical model in OBIEE must be a star schema, and OBIEE uses that when having measures.

OBIEE doesn't work like SSAS, it's power is really to generate queries based on the business model using the physical model to know how to link things together. So no model = no data.

Frog Toad

Solution was simple

So, table "STORE" not related with table "CALENDAR":

In Business level, i did next:

- i made hierarhy by dimension "CALENDAR" - "Year"-"Month"-"Date"

- i moved measure "TOTAL_AREA" and "TRADING_AREA" in "TOTAL"-level in hierarhy

Now, to each element of the dimension-table "CALENDAR" will be displayed total value of the fields "TOTAL_AREA" and "TRADING_AREA":

I hope that it helps someone