locked
data model best practice RRS feed

  • Question

  • I am building a dashboard style set of reports.  Some of the reports do not need to use all of the same dimensions, but are using the same fact table.  Is it better to have a different data models such that each report is based on a data model that only has the dimensions that it is using or can I have all of the reports based on just one data model where only the dimensions needed are used in the reports?
    Thursday, December 5, 2013 1:34 PM

Answers

  • Hi Jeffrey,

    It would be best to keep them in the same model if there is a slight chance that the users will at some point need to see the data broken down by dimensions that are not currently used by them. In addition to this, having separate models would mean the common DAX calculations would have to be duplicated in each model and this introduces a maintenance overhead. That said, if you are 100% sure that they will never use some of these dimensions and you don't mind the maintenace overhead, then you could create a separate Power Pivot workbook for each user group.

    If you do go with a single model, you may want to consider using perspectives to hide the complexity of the model from ther users but this is only applicable if you will be publishing the Excel workbooks to SharePoint. You can read more about this here http://technet.microsoft.com/en-us/library/hh272057.aspx

    Hope this helps.

    Michael




    • Proposed as answer by Michael Amadi Thursday, December 5, 2013 4:12 PM
    • Edited by Michael Amadi Thursday, December 5, 2013 4:16 PM minor edit
    • Marked as answer by JeffreyShumaker Friday, December 6, 2013 12:26 AM
    Thursday, December 5, 2013 4:12 PM

All replies

  • Hi Jeffrey,

    It would be best to keep them in the same model if there is a slight chance that the users will at some point need to see the data broken down by dimensions that are not currently used by them. In addition to this, having separate models would mean the common DAX calculations would have to be duplicated in each model and this introduces a maintenance overhead. That said, if you are 100% sure that they will never use some of these dimensions and you don't mind the maintenace overhead, then you could create a separate Power Pivot workbook for each user group.

    If you do go with a single model, you may want to consider using perspectives to hide the complexity of the model from ther users but this is only applicable if you will be publishing the Excel workbooks to SharePoint. You can read more about this here http://technet.microsoft.com/en-us/library/hh272057.aspx

    Hope this helps.

    Michael




    • Proposed as answer by Michael Amadi Thursday, December 5, 2013 4:12 PM
    • Edited by Michael Amadi Thursday, December 5, 2013 4:16 PM minor edit
    • Marked as answer by JeffreyShumaker Friday, December 6, 2013 12:26 AM
    Thursday, December 5, 2013 4:12 PM
  • thanks mike for a very thorough explanation of why
    Friday, December 6, 2013 12:26 AM