Reporting a non conformed dimension

2657149

Hello Friends,

I have the below scenario:

We have few conformed dimensions and 1 non-conformed dimension.

As of now, I have only displayed 1 conformed dimension(i.e, Project Number)

We have 2 Facts: Revenue and Cost

      

Date	Project	Gross Revenue	Net Revenue	Cost	Event
1-Mar-16	31250	0	0	300
2-Mar-16	31250	1000	500	500	A
3-Mar-16	31250	2000	500	1500	B
3-Mar-16	31250	2000	500	1500	C

  

Event	View
A	Operational
B	Operational
C	Fiscal

We have a prompt of Event View on the dashboard

Event has a relation with Revenue fact only.

Currently, we have our revenue measures being calculated by population of session variable from the Event View prompt.

The issue, is whenever I display the Event from the dimension, I loose the record of the cost on the report.

There can be transactions associated with cost and without revenue.

Hence, I cannot have Event at All level for the cost. We would end up in missing the cost records when the report is generated for Mar 1 2016 which has no revenue and only cost.

Now, the requirement is to display Event on the report.

As the Event from the dimension has the above issue, I tried populating the event into the revenue fact, and I use the event from the revenue fact on the report,

I am still not able to report the event .

The OBI is not firing the query with the Cost fact.

Can anyone please suggest on how to handle this scenario?

Thanks

Shravan

[Deleted User]

2657149 wrote:

The issue, is whenever I display the Event from the dimension, I loose the record of the cost on the report.

The title plus that one line is all one needs to know: Logical Table Source content levels. Conformed dimensions must be set to their respective - appropriate level - and non-conformed dimensions must be set to the grand total level.

2657149

Hi Christian,

Thank you for the response.

I have implemented the below steps:

Created a new physical alias of the dimension

Configured a physical join between the revenue fact and the new alias on the key column

Created a new logical dimension table with the help of the new physical alias

Configured logical joins between both the facts and the new dimension

Create a new dimension hierarchy and configured an grand total level

Assigned all the LTSs of the Cost fact to grand total level of the new dimension

Copied the new event column to Presentation layer

With the base measures alone, I see 3 records, 1 with revenue and no cost and 2 from cost without revenue

The minute I include the new dimension presentation column, I loose the 2 cost records.

I am not sure where else I am going wrong. When I checked at the log, I do not see Cost fact being used at all.

Can you please correct me if I am doing wrong anywhere?

Appreciate your suggestions on this!!

Thanks

Shravan

2657149

Hi Christian,

Thanks , It now worked for me..

The issue it did not work last time was the logical column had multiple mappings.

It set the content level on both and was able to get that to work now.

Thanks

Shravan