OBIEE 12c. Empty measure value

Question

Hi! How fix empty measure-value, when it measure unrelated with dimension? Example: I have 2 views: V_STORE: Column nameSTORE_ID<..MANY_OTHER_FIELDS..>TRADING_AREAREGION_ID V_CALENDAR Column nameCDATEMONTHYEAR<..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_IDTRADING_AREA1100025000 And with year YEARREGION_IDTRADING_AREA 200011000200025000201011000201025000201611000201625000

Frog Toad · Answer

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

Gianni Ceresa · Answer

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 · Answer

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"

INDICATORCDATESTOREINDICATOR_IDVALUE_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