Is it possible to display Date in between in cube without multiplying the Data?

• ktflash

  Default port

  Points: 1400

  More actions

  July 21, 2020 at 7:36 am

  #3771650

  Ahoi,

  I have to display "discount" as a measure in my Cube.

  The basic structure is:

  • DateFrom
  • DateUntill
  • DiscountMeasure

   

  The request is:

  Have a Date and display the DiscountMeasure.

  My question is:

  Is there a better way to solve this than creating a dataset for each day between DateFrom and DateUntill?

  I was hoping the aggregate function could help me, but either they can't or i am missing something.

   

  Thanks

  • This topic was modified 5 years ago by ktflash.

  I want to be the very best
  Like no one ever was

  https://www.sqlservercentral.com/articles/forum-etiquette-how-to-post-datacode-on-a-forum-to-get-the-best-help

• Phil Parkin

  SSC Guru

  Points: 247172

  More actions

  July 21, 2020 at 1:27 pm

  #3771745

  How is DiscountMeasure calculated?

  ---

• ktflash

  Default port

  Points: 1400

  More actions

  July 22, 2020 at 4:07 am

  #3772205

  Phil Parkin wrote:

  How is DiscountMeasure calculated?

  Its not calculated its just a number and the 2 date ranges (+ other dimension references)

  I want to be the very best
  Like no one ever was

  https://www.sqlservercentral.com/articles/forum-etiquette-how-to-post-datacode-on-a-forum-to-get-the-best-help

• Phil Parkin

  SSC Guru

  Points: 247172

  More actions

  July 22, 2020 at 1:29 pm

  #3773789

  OK, are these date ranges included in your cube? If so, why are you doing that, rather than allowing the end user to perform the aggregation in a pivot table (or however they are consuming the data)? I mean, why not simply have (Date, DiscountMeasure)?

  ---

• ktflash

  Default port

  Points: 1400

  More actions

  July 23, 2020 at 4:08 am

  #3774038

  Phil Parkin wrote:

  OK, are these date ranges included in your cube? If so, why are you doing that, rather than allowing the end user to perform the aggregation in a pivot table (or however they are consuming the data)? I mean, why not simply have (Date, DiscountMeasure)?

   

  because the user cant deal with the folloewing example:

  Discount: 30%

  DateFrom: 28.2.2019

  DateTill: 08.09.2020

   

  If the user requests any date that is between Datefrom and DateTill the 30% discount is relevant, but only appears if the specific dates DateFrom or DateTill are selected. I am intrested if there was a better way to allow the user to show the discount in the  date range without  creating a single row for every date between the range.

   

  Edit:

  I just tested what the multiplying version looks like. OOOF

  I have 1.6 million data rows (already filtered) which leads to almost 500 million Cube rows for a single year.

  Sucks i cant simply write them only on the first of the month since the data range can change mid month.

  • This reply was modified 5 years ago by ktflash.

  I want to be the very best
  Like no one ever was

  https://www.sqlservercentral.com/articles/forum-etiquette-how-to-post-datacode-on-a-forum-to-get-the-best-help