none
Fact dimension vs Non-additive measure RRS feed

  • Question

  • For an OrderLineItem fact table, I can either create a fact dimension using the OrderLineItemID, or create normal measure group with a Non-additive OrderLineItemID measure.

    In the 2nd option, I can filter out the OrderLineItemID when doing MDX.

    So what is the pros and cons comparing performance and functionalities?

    Thanks.

    Sunday, December 9, 2012 11:30 PM

Answers

  • For an OrderLineItem fact table, I can either create a fact dimension using the OrderLineItemID, or create normal measure group with a Non-additive OrderLineItemID measure.

    In the 2nd option, I can filter out the OrderLineItemID when doing MDX.

    Are you sure you can do this? What sort of non-additive aggregate were you planning to user? I think the only way to be able to filter or display an individual OrderLineItemID would be to crossjoin enough other attributes to get you down to an equivalent grain to the orderLineItem - which is likely to be an expensive operation.

    If you need to be able to filter specific OrderLineItemsIDs then a fact dimension should perform much better at query time (at the expense of extra processing time) because of the extra indexes that are created over dimension attributes.


    http://darren.gosbell.com - please mark correct answers

    • Marked as answer by Koalass Monday, December 10, 2012 1:56 AM
    Monday, December 10, 2012 1:37 AM
    Moderator

All replies

  • For an OrderLineItem fact table, I can either create a fact dimension using the OrderLineItemID, or create normal measure group with a Non-additive OrderLineItemID measure.

    In the 2nd option, I can filter out the OrderLineItemID when doing MDX.

    Are you sure you can do this? What sort of non-additive aggregate were you planning to user? I think the only way to be able to filter or display an individual OrderLineItemID would be to crossjoin enough other attributes to get you down to an equivalent grain to the orderLineItem - which is likely to be an expensive operation.

    If you need to be able to filter specific OrderLineItemsIDs then a fact dimension should perform much better at query time (at the expense of extra processing time) because of the extra indexes that are created over dimension attributes.


    http://darren.gosbell.com - please mark correct answers

    • Marked as answer by Koalass Monday, December 10, 2012 1:56 AM
    Monday, December 10, 2012 1:37 AM
    Moderator
  • It looks I was wrong. Yes, if I need to filter down the OrderLineItemsIDs, I need to include many attributes. Not a solution.

    The reason I asked if that I found the queries at the OrderLineItemsID grain are normally very slow. I saw someone suggests using ROLAP on the detail grain queries. But having no idea how to do it. Does it mean using ROLAP for the partition?

    Monday, December 10, 2012 2:01 AM
  • Switching to ROLAP will pretty much never improve query performance. If you were only using the OrderLineItemID in drillthrough queries then having the fact dimension as ROLAP would reduce processing time and storage requirements, but it may actually make the queries slower.

    It depends how much control you have over the queries, If this is for a report at the detailed level then doing a DRILLTHROUGH query can be faster than doing a big crossjoin query. Another option if this is a report is to just write it as a SQL query. SSAS is a great aggregation engine, but as a consequence it's not so good at detail level reporting, but it depends on the type of query and the number of different attributes and the number of different dimensions involved.


    http://darren.gosbell.com - please mark correct answers

    Monday, December 10, 2012 5:17 AM
    Moderator