Link Data Model

roymunoz03

New Member
Joined
Aug 1, 2010
Messages
8
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.
 

Excel Facts

Enter current date or time
Ctrl+: enters current time. Ctrl+; enters current date. Use Ctrl+: Ctrl+; Enter for current date & time.
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.
 
Upvote 0
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 :eek::cool:

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
 
Upvote 0
Hi Scott,
apart from being a women, you're probably right. Will give it a though - or two...
;) Imke
 
Upvote 0
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???
 
Upvote 0

Forum statistics

Threads
1,213,531
Messages
6,114,172
Members
448,554
Latest member
Gleisner2

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top