Recently I have been assigned with a reporting requirement to compare measures (E.g. Sales Figures) from This Year’s date compared with the same date from Last Year’s. Below might give some idea of the reporting requirement or the report format requires.

The reason for such reporting requirement is because the client need to compare how good the sales is compared with the same day of last year. This is to make sure that the marketing strategy could be in place or to discontinue certain line of products when there is a drastic change of the sales observed. Nevertheless there could be multiple ways to look at these data.

This Year Date Last Year Date This Year Sales Last Year Sales
01.01.2015 01.01.2014 5,500.00 4,000.00
02.01.2015 02.01.2014 4,000.00 3,500.00

In order to create such a reporting requirements, a single InfoCube that contains the Day Sales are created. In this InfoCube there should be 3 different dates, that is the Current Date, Last Year Date and Next Year Date. These dates should be populated accordingly during the data load.

In order to view the above mentioned report format we need to combine two different InfoCubes but in order to prevent data duplication of Previous Year data, we could instead create another InfoSet from the same InfoCube that we have created previously and named it as Sales Figure (Last Year). This eventually will be used in the MultiProvider that combines the original InfoCube and the new InfoSet created.

In the MultiProvider, it should combine 2 InfoProviders, that is the original InfoCube and the InfoSet. There within the MultiProvider should be the Current Date and the Last Year Date InfoObjects. Additionally, 2 sets of key figures should be included that is the sales figures from the InfoCube and the InfoSet.

Assignment of the InfoObjects to the InfoProvider should look like below.

InfoProvider This Year Date Last Year Date This Year Sales Last Year Sales
InfoCube This Year Date Last Year Date Sales Figure
InfoSet (Last Year) Next Year Date This Year Date Sales Figure

Explanation:

In order to get the Last Year Sales figures from the InfoSet the “This Year Date” should be assigned to the Next Year Date InfoObject in the InfoSet and  “Last Year Date” should be assigned to the This Year Date InfoObject in the InfoSet. In this manner we are able to read the Last Year Sales figure without duplication of the data in another InfoCube.

I hope my explanation of the above is details enough and clear for your understanding.

This blog is based on my original blog in my blog post but wanted to post it in this space in order to gain points for my contribution.

Best Regards

David Yee

To report this post you need to login first.

1 Comment

You must be Logged on to comment or reply to a post.

  1. Luis Vila

    Hi David,

    Thanks for sharing your expertise. I have a similar requirement and found your approach very interesting as it can provide a solution without coding but could it be possible for you to share some more details on your solution like the multiprovider design and how to assign the fields in multiprovider.

    Your help is very much appreciated,

    Louis

     

    (0) 

Leave a Reply