How to achieve this in the cube?

• Charmer

  SSChampion

  Points: 14903

  More actions

  January 10, 2018 at 3:52 am

  #409943

  Hi Friends,

  Last week I got a interesting task which is projecting some sales.
  My task is to find out the missing sales for the previous week in FACT_Sales table. Lets say that we have some sales in between Dec/31/2017 to Jan/06/2018. Some times we miss sales for a day or two because of delay in getting the input files from users.
  For example, Let's say that we have not got any sales on Tuesday, so I would project some sales in to a new table Fact_Sales_Project for Tuesday. 

  In view, I use Union ALL to merge the actual sales along with the projectred with a field called IsProjected to distinguishing actual sales vs projected sales.
  Select *, 'No' AS IsProjected From Fact_Sales
  UNION ALL
  Select *, 'Yes' AS IsProjected From Fact_Sales_Project

  The end user wants to see this in the cube. When they report sales data, by default it should show actual sales in the report. Since this is coming from the FACT, it is going into a measure in the cube which I think will not serve the purpose. Is there any way to achieve this? 
  Please give me your suggestions. Let me know in case if more details required.

  Thanks,
  Charmer

• Jeff Moden

  SSC Guru

  Points: 1003907

  More actions

  January 10, 2018 at 6:52 pm

  #1975429

  Charmer - Wednesday, January 10, 2018 3:52 AM

  Hi Friends,

  Last week I got a interesting task which is projecting some sales.
  My task is to find out the missing sales for the previous week in FACT_Sales table. Lets say that we have some sales in between Dec/31/2017 to Jan/06/2018. Some times we miss sales for a day or two because of delay in getting the input files from users.
  For example, Let's say that we have not got any sales on Tuesday, so I would project some sales in to a new table Fact_Sales_Project for Tuesday. 

  In view, I use Union ALL to merge the actual sales along with the projectred with a field called IsProjected to distinguishing actual sales vs projected sales.
  Select *, 'No' AS IsProjected From Fact_Sales
  UNION ALL
  Select *, 'Yes' AS IsProjected From Fact_Sales_Project

  The end user wants to see this in the cube. When they report sales data, by default it should show actual sales in the report. Since this is coming from the FACT, it is going into a measure in the cube which I think will not serve the purpose. Is there any way to achieve this? 
  Please give me your suggestions. Let me know in case if more details required.

  No problem.  Just grab the preceeding and proceeding row for each range of blank row(s), take the average of those two rows and distribute that number to the blank rows. 

  If you want more than just a helpful suggestion, then you know what you need to do. 😉

  --Jeff Moden

  ---

  RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
  First step towards the paradigm shift of writing Set Based code:
  ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

  Change is inevitable... Change for the better is not.
  

  ---

  Helpful Links:
  How to post code problems
  How to Post Performance Problems
  Create a Tally Function (fnTally)