Forum Discussion

daxreport's avatar
daxreport
Frequent Visitor
2 years ago
Solved

Best Practice for using Excel mapping file in a composite model

Hello,

 

I have a semantic model which I need to enrich with an offline mapping table in Excel. I am aware I can turn this into a composite model by bringing in data from Excel. However, looking to understand best practices for the following:

 

1. Where exactly to save the Excel? In the C drive or somewhere in the SharePoint? 

2. Will the Refresh options work with a model that has both an Excel and a Direct query connection/dataflow?

 

Eventually, I would like to save this composite model as my own semantic model in Myworkspace. From this base model, I will create different reports with a live connection to this saved out in the drive. This way I can maintain one copy of the main model with all the measures. Second objective is to keep all the measures private when I finally publish my reports in a Shared Workspace. 

 

Thanks

Need Help
  • christinepayton's avatar
    christinepayton
    2 years ago

    Put the Excel file in SharePoint Online. That will let you schedule refresh without using a gateway. You can have direct query and import mode sources in the same dataset. 

3 Replies

  • christinepayton's avatar
    christinepayton
    Most Valuable Professional
    2 years ago

    Put the Excel file in SharePoint Online. That will let you schedule refresh without using a gateway. You can have direct query and import mode sources in the same dataset. 

    • daxreport's avatar
      daxreport
      Frequent Visitor
      2 years ago

      Thank you, Will I need to schedule any other refresh once I create a live connection from a shared worksplace to this model saved in my personal Workspace? 

       

      In your experience, does it impact performance when there is a live connection to a model which in turn is using a DirectQuery for the fact table? 

       

      Thanks