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

  • 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 6 months, 1 week ago by  ktflash.

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

  • How is DiscountMeasure calculated?

    If the answer to your question can be found with a brief Google search, please perform the search yourself, rather than expecting one of the SSC members to do it for you.
    See https://www.sqlservercentral.com/articles/forum-etiquette-how-to-post-datacode-on-a-forum-to-get-the-best-help/ for details of how to post T-SQL code-related questions.

  • 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

  • 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)?

    If the answer to your question can be found with a brief Google search, please perform the search yourself, rather than expecting one of the SSC members to do it for you.
    See https://www.sqlservercentral.com/articles/forum-etiquette-how-to-post-datacode-on-a-forum-to-get-the-best-help/ for details of how to post T-SQL code-related questions.

  • 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 6 months ago by  ktflash.

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

Viewing 5 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply