Error Checking in Excel
Thanks Thanks:  0
Likes Likes:  0
Results 1 to 10 of 10

Thread: Link Data Model

  1. #1
    New Member
    Join Date
    Aug 2010
    Posts
    8
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Link Data Model


    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. #2
    Board Regular scottsen's Avatar
    Join Date
    Mar 2014
    Location
    Seattle, WA
    Posts
    1,263
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Link Data Model

    The only way I know is SharePoint.
    scott at tinylizard.com : Power Pivot Consultant, Trainer and Mercenary
    See my Power Pivot blog at http://tinylizard.com/blog

  3. #3
    Board Regular
    Join Date
    Feb 2015
    Location
    Berlin
    Posts
    544
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default 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. #4
    New Member
    Join Date
    Jan 2012
    Location
    Houston TX
    Posts
    46
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default 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. #5
    Board Regular
    Join Date
    Feb 2015
    Location
    Berlin
    Posts
    544
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default 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

  6. #6
    Board Regular scottsen's Avatar
    Join Date
    Mar 2014
    Location
    Seattle, WA
    Posts
    1,263
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Link Data Model

    You mad man
    scott at tinylizard.com : Power Pivot Consultant, Trainer and Mercenary
    See my Power Pivot blog at http://tinylizard.com/blog

  7. #7
    Board Regular
    Join Date
    Feb 2015
    Location
    Berlin
    Posts
    544
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Link Data Model

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

  8. #8
    New Member
    Join Date
    Jan 2012
    Location
    Houston TX
    Posts
    46
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default 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. #9
    Board Regular
    Join Date
    Feb 2015
    Location
    Berlin
    Posts
    544
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Link Data Model

    Hi PentaGalCXO,
    You got it!

  10. #10
    Board Regular
    Join Date
    Jan 2012
    Location
    Ohio
    Posts
    242
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default 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

Some videos you may like

User Tag List

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

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