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.
 

Some videos you may like

Excel Facts

Test for Multiple Conditions in IF?
Use AND(test, test, test, test) or OR(test, test, test, ...) as the logical_test argument of IF.

ImkeF

Well-known Member
Joined
Feb 1, 2015
Messages
544
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.
 

ImkeF

Well-known Member
Joined
Feb 1, 2015
Messages
544
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
 

ImkeF

Well-known Member
Joined
Feb 1, 2015
Messages
544
Hi Scott,
apart from being a women, you're probably right. Will give it a though - or two...
;) Imke
 

PentaGalCXO

New Member
Joined
Jan 31, 2012
Messages
46
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???
 

Watch MrExcel Video

Forum statistics

Threads
1,095,170
Messages
5,442,799
Members
405,197
Latest member
queryashish

This Week's Hot Topics

  • Copy entire row if CountA <>0 to another sheet
    [B]I want to copy entire row if CountA <>0 for column J7:AM7 (headers on J6:AM6) and so on till the last used cell is column D and paste the...
  • Select last used Row in Table
    I have created a Table in a Worksheet which is locked to prevent user errors and protect formula. Some of the cells require freetext entries which...
  • excel workbook: do not allow certain file name
    Hello all, Don't think this has ever been asked before, but how do I restrict file save [Before_Save Event] if the name of the file being saved...
  • fixing problem autofilter
    hello i need help about my code when i search by code in textbox it doesn't show anything this is my data [ATTACH type="full"...
  • “Weight”
    Hi, i’ve got a long sheet filled with weights such as kg,g,L & ml. i can build a formula to convert kg into g and liter into ml. How ever, my...
  • How to capitalize everything before a certain character?
    In column A, I have some text: Hello good day.mp3 Hello good day.flac etc. I'd like to capitalize everything before the period. I don't need the...
Top