URGENT - Fact Extension versus "Total" in Page -by Attribute

[ArunKKK]

Hi,

I have a problem and would sincerely appreciate any earliest solution or workaround.

I have a report with the following specs -
Row Attribute - "Region",Metric: Sales $,Page-By - "Store"

Note that Region can be drilled to District to Store. In the given specs, we have to keep "Store" in the Page-By section. And, the user is free to drill to District on the report.

Metric requirement is that "Sales $" when calculated at Store level, should display blank (or NULL). So, I defined a fact extension for underlying base fact on which "Sales $" metric was defined, and defined a allocation expression
at "Store" attribute level as ZEROIFNULL(0.0).

In ideal scenario, as long as we don't keep "Store" in the Page-by, everything works good. We get to see metric value until "Distrct" level, and it becomes NULL, as and when we drill to Store. So, fact extension works good.

However, taking "Store" to Page-By, it doesn't work that way. Always, I get blanks ir-respective of when "Store" attribute is set to "Total" or not. So, fact extension doesn't work good in this scenario, becuase, MicroStrategy just keeps looking for if "Store" attribute has been put on the report or not, ir-respective of whether it is set to "Total" or not.

Per the report requirements, when the report runs, "Store" attribute should be set to "Total", and when set, "Sales $" metric should be calculated at "Region" level. And, as soon as, the user selects any "Store" value, then, this metric should display blank.

In my view, "Total" should allow MicroStrategy to avoid considering "Store" attribute" on the report, and shouldn't apply the Fact extension allocation expression. Or, could there be a way to use "Total" in the DECODE logic to build the metric.

Any help would greatly be appreciated.

 

[entaroadun]

First of all, I don't think the report is set up properly, even if the fact extension were to "work".

If your store dimension looked like this:

Region     District     Store
A          X            1
A          X            2
A          Y            3

If you had a report with Store 1 in the page by:

Page By:  Store 1

              Sales
Region A      (sales for only Store 1)

Is that what you want? You will display sales for only the Store in the page by, even though you are displaying Region in the attribute. You won't get total sales for the Region.


This is more of a philosophical issue, but the fact extension is working properly. The ReportLevel of your report is Store, because that's the lowest level attribute on the report. This drives the SQL engine; displaying or not displaying the attribute is done by the analysis engine after the fact. The fact extension is done at the SQL step. So would any security filters or level settings.


OK, criticisms notwithstanding, here is a solution:

Create an alias for your Store lookup table. Create a new attribute called "Store" in a separate folder. Map this attribute to your fact table and your alias table. Keep the fact extension for the original Store attribute.

Use this new Store attribute in your Page By. Here's why it will work:

For the Region report, the ReportLevel will be the Region attribute and Store(v2) attribute. The fact extension doesn't apply here, so the calculation displays properly.

For the District report, the ReportLevel will be the District attribute and Store(v2) attribute. Again, the fact extension doesn't apply.

For the Store report, the ReportLevel will be the Store attribute and Store(v2) attribute. The fact extension applies here, so you get NULL.

Let me know if you have any issues with this approach.

 

[entaroadun]

Oh shoot, I just realized, you may not have to do all that.

Create a new metric with dimensionality set to ReportLevel S/S and Region S/S. Use this metric on the Region report.

Create a new metric with dimensionality set to ReportLevel S/S and District S/S. Use this metric on the District report.

Use the regular metric on the Store report.

I don't think the fact extension will apply to the first two metrics because of the higher level group setting. If this works, you don't need to do all of that attribute stuff I mentioned.

 

[ArunKKK]

Thanks a lot entaroadun,

Good and Clean approach. I am sure I would have missed out in my explanation somewhere. Let me re-explain this issue -

Let me go back to your old approach with the attributes stuff - While you are on the Region report, and have Store(v2) in the Page-By, and Store (v2) is set to "Total" (i.e., no Selection), then the metric should evaluate total sales at Region level. However, if the Store (v2) changes to any value other than "Total" (i.e., achieved thru' SubTotal), then, this metric (still at being the Region level) should display NULL (or blank). It's all hapenening in the same report.

So, with your attributes approach, I may not be able to get NULL, as soon as I select a value off of the "Store (v2)" attribute.

Please let me know if I have misunderstood your approach. Any hints would greatly be appreciated......

Thanks

 

[entaroadun]

I don't think I understand what you mean by Total and SubTotal. Subtotaling on the report template don't affect the SQL engine, so there's no impact.

If you turn all subtotaling off, my approach should still work. Moving the Store(v2) up to page by will automatically dynamically aggregate the metric values. Dynamic agg is different than subtotaling...

As for the drill-down, let's be clear that a drill-down in MSTR is not a real drill-down, as it would be in a MOLAP application. What is really happening is that MSTR is creating a completely new report object with the objects that you need in the drill-down report.

Therefore, when you drill from the Region report to the District report, you are getting a completely new report. The Region report will have Region and Store(v2) on it. When you drill to District, you get a completely new report with District and Store(v2). SQL for the District report is completely regenerated as if it were a new report. When you drill from District to Store, MSTR treats the Store report as a new report.

That's why you can drill from the District report, which runs SQL without the fact extension, to the Store report, which runs SQL with the fact extension. Therefore, you drill from a report with data to a report with NULL values.

Please let me know if I am being unclear.

 

[ArunKKK]

Thanks a lot, entaroadun, for your patience. I perfectly agree with your views here, and have no question on this. Let me clarify what I meant by "Total or SubTotal" on Store (v2) attribute.

While defining a report - I have put Region in row, and metric in column, and Store (v2) in Page-By (let's say). From the Data-SubTotal options, by using "Across" suboption, I turned on the "Total" on the "Store (v2)" attribute, and in the next tab, I selected the option where it says Top of the values for Page-By. Later one is just to give the feature - when the user runs this report for the first time, user would see "Total" in the Store (v2) attribute selection window, and wouldn't see any other value of the Store - thwough they would be avavailble as soon as user clicks the down arrow on the Store(v2) attribute in the run time.

WE have to accomplish all of this in just one report, i.e., Region report. I also agree your explanation on the SQL Engine and Analytic Engine. In the scenario, SQL Engine will not apply the fact extension when Store (v2) attributes values are selected . simple reason - we have built the fact extension on Store (v1) attribute which falls in the Region->District->Store hierarchy. However, since it won't apply the fact extension, I would not get NULL in the metric column, which is not ours requirement. Ours requirement is as soon as user makes any value selection on the Store(v2) attribute, then, the metric value should be set to NULL, blank. If, Store (v2) is all the time set to "Total" then, metric value should be calculated normally.

Please let me know if you think there coule be a solution.

Thanks a lot - appreciate your kind help....

 

[entaroadun]

Ah... I see. Sorry about that.

I don't have a solution. I think what you want to do is beyond Desktop.