Import Data model in Excel

[Anonymous]

Hello all,

I am working on a project where I have created a data model in powerBI and have it saved in a dataset. I have brought this into excel through a connection, and I have various pivot tables and cubemember formulas that are sliced by specific entities (or account parents or other things). This works well for many of the users, but there are executives who want to be able to view this data while on not connected to the internet (whether on a plane or at a game or something else). When they try to slice the data differently or drill into details on pivot tables, it give them an error.

I originally set up these reports through power pivot and a flat excel file as the reference, and then everything was sitting in Excel's data model. This approach worked for the offline problem, but then users are not able to refresh the data themselves and I get stuck generating 40 reports based on each users access. 

Does anyone have any ideas on how to make this work? I think the best thing is if I can import the data model from the dataset into Excel and then just reference "ThisWorkbooksDatamodel" as my connection throughout the workbook, but I have not been able to figure out how to do this. 

I appreciate the help!

[lbendlin]

and cubemember formulas

Try to limit that.  Power BI is based on SSAS Tabular and needs to convert the DAX to MDX for that which results in performance penalties.  Better to use DAX directly.

What you can try is import individual tables from your Power BI dataset and then wire them up in the Power Pivot data model.  That way you have the data model and the users can refresh individual tables.  (of course that requires your dataset to be rather small).

[Anonymous]

Thank you. How do you bring in the data set into excel though? When I go into Excel and click Get Data, I only see options for PowerBi, DataFlows, and Dataverse.

PowerBi only gets me a connection.

Data flows is a layer to low, because I assign security at the dataset. My data set uses a data flow, but then security is not applied.

I am not familiar with Data Verse.

Maybe its something with my settings? Just want to confirm you can bring in your dataset some how besides those three options I see (or if Dataverse, how do I use that)? 

Thank you

[lbendlin]

You use Analysis Services in import mode.