Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
daxreport
Frequent Visitor

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

1 ACCEPTED SOLUTION
christinepayton
Super User
Super User

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. 

View solution in original post

3 REPLIES 3
SaiTejaTalasila
Responsive Resident
Responsive Resident

Hi,

 

You will need a data gateway connection if you are pulling data from SQL server.Please check this for more details.https://community.fabric.microsoft.com/t5/Desktop/Direct-Query-Connection-with-SharePoint-Online/td-...

christinepayton
Super User
Super User

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. 

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

Helpful resources

Announcements
Europe Fabric Conference

Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

Power BI Carousel June 2024

Power BI Monthly Update - June 2024

Check out the June 2024 Power BI update to learn about new features.

RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.

Top Solution Authors