Thanks:  0
Likes:  0

1. ## Link Data Model

Hello,

I created a file that imports multiple tables, and then I do several modifications and calculations with Power Pivot. The problem is that I want to use the final results, not only in that current workbook, but also in other workbooks.

So my question is, is there a way to link other excel files to that data model that I created and worked on?

Please note the data changes every day, so I am looking for a permanent connection.

Appreciate any help here.

2. ## Re: Link Data Model

The only way I know is SharePoint.

3. ## Re: Link Data Model

And there's PPs Big Brother from "corporate BI": SSAS in tabular mode (https://msdn.microsoft.com/en-us/library/gg492155.aspx )
If you are lucky and have access to a SQL Server, that's able to run Analysis Services in Tabular mode, you just import your xlsx-file there and create a tabular Project with just a very few clicks. This will make your (exPP-)data model sit in in the SQL-Server as a central hub.

4. ## Re: Link Data Model

So you want to connect other Workbooks to a PowerPivot data model... without SharePoint... without SSAS... yes it can be done, but of course with limitations. In short, expose the PowerPivot data to Excel via a DAX query, then pivot on that table to another workbook. More details here: https://brentpearce.wordpress.com/20...ix-or-do-they/

5. ## Re: Link Data Model

Hey fellow PowerPivot-Geeks,
Wouldn’t it be so nice to have a simple solution for sharing PowerPivot Models on a file-to-file basis? Maintaining one in a special place where it keeps the current “MasterModel” and all other report files connecting and updating from it instead of keeping it on an expensive server or in the cloud?

Hey presto – here we go:
Gerhard developed the technique (Restoring a SSAS Tabular Model to Power Pivot | Gerhard Brueckl's BI Blog - simply brilliant), we are going to expand the use case: Transplant Power Pivot Data Model from one file to the other - just with Excel – how cool is this?: Unzip – extract – zip back – unzip – implant – zip back

Take the first makro that clones the Data Model from your “MasterFile” and stores it in a dedicated place on your file server. Transplant this MasterModel into your report files by using the 2nd macro.

This is not supported by Microsoft, so rather a Guerilla-method

Just don’t forget that your report file needs a data model, otherwise the necessary filespaths in the xls/zip don’t exist. So if you use a blank xls file it’s enough to mark a single cell with any value – “Powerpivot – Tables – AddToTheDataModel”. It will be replaced later anyway. And it needs to be stored as xlsm.

Anyone out there having experience with this approach? Wonder if the files “suffer” from the zip and unzipping procedures. WDYT?

Code:
Sub ExportMasterDataModel()

'Save current workbook
ActiveWorkbook.Save

'Save current workbook as zip-file
ActiveWorkbook.SaveCopyAs "C:\Users\Imke\Documents\BI\Beispieldaten\EigeneBeispiele\TransplantModel\MasterV1Zip.Zip"

'Unzip the new file
'This macro is based on: http://www.rondebruin.nl/win/s7/win002.htm
'Dim FSO As Object
Dim oApp As Object
Dim Fname As Variant
Dim FileNameFolder As Variant
Dim DefPath As String
Dim strDate As String

'Filename  -- Change to yours
Fname = "C:\Users\Imke\Documents\BI\Beispieldaten\EigeneBeispiele\TransplantModel\MasterV1Zip.Zip"

'Root folder for the new folder.
DefPath = "C:\Users\Imke\Documents\BI\Beispieldaten\EigeneBeispiele\TransplantModel\ZipDonor\"

'Create the folder name: strDate is optional: This way every version of the ModelFiles is stored
strDate = Format(Now, " dd-mm-yy h-mm-ss")
FileNameFolder = DefPath & strDate & "\"

'Make the normal folder in DefPath
MkDir FileNameFolder

'Extract the files into the newly created folder
Set oApp = CreateObject("Shell.Application")

oApp.Namespace(FileNameFolder).CopyHere oApp.Namespace(Fname).items

'Copy the DataModel file into your destination folder
FileCopy FileNameFolder & "xl\model\item.data", "C:\Users\Imke\Documents\BI\Beispieldaten\EigeneBeispiele\TransplantModel\ZipDonor\CurrentModel\item.data"

'Close and save changes
ActiveWorkbook.Close SaveChanges:=True

End Sub
Code:
Sub ImplantCurrentModel()

'Ask for the file to update
strFileToOpen = Application.GetOpenFilename _
(Title:="Please choose a file to open", _
FileFilter:="Excel Files *.xls* (*.xls*),")

Dim xlsName As String
Dim zipName As Variant
Dim Implant As Variant
Dim zipDestination As Variant

'Define the paths & files
Implant = "C:\Users\Imke\Documents\BI\Beispieldaten\EigeneBeispiele\TransplantModel\ZipDonor\CurrentModel\item.data"   '<< change
xlsName = strFileToOpen
zipName = Replace(xlsName, "xlsm", "zip")
zipDestination = zipName & "\xl\model\"

'zip-name the xlsm-file'
Name xlsName As zipName

'transfer the model file
Set oApp = CreateObject("Shell.Application")
oApp.Namespace(CVar(zipDestination)).CopyHere CVar(Implant)
Application.Wait (Now + TimeValue("0:00:05"))

'recreate xlsm from zip
Name zipName As xlsName
Application.Wait (Now + TimeValue("0:00:01"))

'open the newly created
Workbooks.Open (CVar(xlsName))

End Sub

7. ## Re: Link Data Model

Hi Scott,
apart from being a women, you're probably right. Will give it a though - or two...
Imke

8. ## Re: Link Data Model

ImkeF (aka mad woman) - if I'm following you right... your method would allow 1 person to perform all the ETL functions to build the data model... and other folks could then use the data model in their own unique "reporting" workbooks... thus relieving them the burdon of performing the ETL... AND they won't need special security to accesss any source data, AND they would not need any special toolsets for the ETL (like Oracle drivers, ODBC defs, PowerShell...)... AND it would probably be quicker even if they had those things... The more I think about it the more I like it. However, if their "reporting" workbooks are not unique there would be no benefit - just give them a copy of the "master" - Yes???

9. ## Re: Link Data Model

Hi PentaGalCXO,
You got it!

10. ## Re: Link Data Model

Smart people on this thread.

Also take a look at Power Update if you ever get tired of writing and running scripts. It can restore/push/publish data model workbooks to SSAS Tabular, SharePoint, Power BI Online, file shares, etc.

Introducing Power Update! « PowerPivotPro

## User Tag List

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•