October 7, 2015 at 5:25 am
Hi everyone,
I have to deal often with the requirement of the comparable periods. I will try to explain the details:
We have a set of reports with different KPIs, which are always compared using these three, let’s say, categories: Last Year, Year to Date and Forecasted Value. Suppose also that there are two dimensions: Store and Time.
The stores are sometime closed due to facelift, refurbishment, etc.
It is required to compared the figures of each store and the three categories above only if the store was open in the current period and in the last year period and if the store has forecasted figures for the next year, otherwise the figures for the store must be discarded because are not comparable. A simple example could be a store the open in summer this year, of course if I compare the sales against the sales last year there is a huge increment, but it is not totally right or a faired comparison.
The challenge is to automated this calculation into the cube, and then the report developer only have to select a Boolean filter to select the total values or only the values that correspond to the comparable periods. In the past the report developers create this logic manually in every report and also the analyst have to recreate it in their Excel sheets ad-hoc analysis.
At the moment I implemented this logic in the view layer, creating the aggregation of stores, and days. Then if the combination store-day is comparable with the last year and the forecast I flagged it as comparable, otherwise this value is set to false. Then I have a dimension called comp logic with two members, total and comp.
The logic is more complex that I’ve explained here and I would like to know if anyone of you have faced a similar requirement and which approach was implemented.
Any comment would be appreciated.
Kind Regards,
October 7, 2015 at 6:31 am
I may be misunderstanding you here, but it sounds like you are doing a year-over-year comparison on stores that have the same time series with a forecast. In meaning, if the store was not open in the year-over-year and did not have a forecast, they were not a good candidate for the year-over-year comparison.
In my organization, we sometimes use cubes that have similar KPI's. When it comes to year-over-year, they typically pull the two sets of dates and do the comparison themselves in the front-end application, whether that be Excel or something like Tableau.
While it's certainly good practice to define that logic on the centralized reporting platform, it's also sometimes a easy one to skip over because you're just pulling two data sets and comparing them with very simple logic.
Outside of that, it sounds like you are heading in the right direction. You first must confirm if the two sets are good candidates for the year-over-year comparison. So, you defined a simple Boolean attribute that triggers the computation.
October 7, 2015 at 11:58 am
Hi, thanks for your answer.
Yes you are right, it's a YOY measure that should be available for the last two years.
If a store opening date is newer as today minus two years is filtered out.
The complicated cases are combinations of different attributes, i.e. a store that was close during one week due to a refurbishment. If the close occurred this year I have to block the same period last year, but still be able to compare the other weeks and the levels above.
We also have a fiscal calendar and a retail calendar and the YOY has to work for both.
I'm just wondering if there are better ways to implement it.
I saw today an answer in a forum that proposes a calculated member with MDX subqueries, but it proofs if there are transactions in both periods. This solution only considers the transactions and not the store attributes. The disadvantage, appart from the performance, is when a store was open but has not made any sales.
Kind regards,
October 15, 2015 at 11:04 am
Have you considered building the flags into a type-2 dimension for stores?
You would require the logic in your ETL layer and drive type-2 changes based on the different rule combinations, but in my opinion it would work much better for all types of reporting if these flags are persisted in your dimensional model. I have done something similar before, and this approach seemed to work best in our case (also retail).
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply