Sharing Datamodel between users?

[hakanfa]

Hi All,
I need some advise and ideas. I have a huge Excel dashboard containing a data model with +600t records which I update every week. Then I distribute the Excel file to every user that save a copy of the Dashboard.xlsx on their own computer and uses it as such. What I would like to do, is to only share the "data model" as a file to the users. That means that the changes the the user makes to the dashboard.xlsx are not lost every week when the data model is updated.

I prefer not to build any SQL servers containing the data - that is not what I am looking for - jsyut need to find an easy wy to distribute the updated data model (if possible)

Any thoughts or ideas how this could be achieved? Worth mentioning that the users have no knowledge regarding databases etc. nor I can assume that the could activate power pivot and update the data model - not an option either.

Best regards,
Hakan

 

[severynm]

What changes are the users making to the dashboard.xlsx and do you want them to make changes in that file?

Would it just be possible to just add a command button to call some VBA to refresh the query? I have done something similar - I imported and formatted/cleaned data via Power Query, and then have a button on a different page that just calls (refreshes) the query). I would think the query in dashboard.xlsx could query the main database file.

Public Sub cmd_RefreshQuery_Click()
    SheetName.ListObjects("NameOfQueryTable").DataBodyRange.ClearContents
    Dim idx As Long
    For idx = 0 To 1000000
    Next idx
    SheetName.ListObjects("NameOfQueryTable").QueryTable.Refresh
End Sub

 

[hakanfa]

What changes are the users making to the dashboard.xlsx and do you want them to make changes in that file?

Would it just be possible to just add a command button to call some VBA to refresh the query? I have done something similar - I imported and formatted/cleaned data via Power Query, and then have a button on a different page that just calls (refreshes) the query). I would think the query in dashboard.xlsx could query the main database file.

Public Sub cmd_RefreshQuery_Click()
    SheetName.ListObjects("NameOfQueryTable").DataBodyRange.ClearContents
    Dim idx As Long
    For idx = 0 To 1000000
    Next idx
    SheetName.ListObjects("NameOfQueryTable").QueryTable.Refresh
End Sub

Hi,
thank you for your answer. The users are allowed to make changes and notes etc. Also the data (the raw data) is changing daily (but weekly updates are enough). The problem is that to be able to work as you mentioned they must first open power query, import the new data (refresh the table) from the Excel and then load the datamodel use it - that will not work, too complicated for the users, I'm sorry


best regards,
Hakan

 

[severynm]

Sorry... maybe I was not super clear. The code I provided is for a command button added into the worksheet that will do the work of refreshing the query (see attached image for an example) - all the users have to to is click the button and the data is automatically refreshed. If you do all of the setup work in an initial dashboard.xlsm template to set up the query to reference your database and do the data transformations you want, you can just distribute that template and all of those steps will be applied when the button is pressed. Is that what you are looking for?