• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 89
  • Last Modified:

automatically update power pivot models linked to sql database - How to?

So my company is using a Microsoft NAV erp (sql based system). I am responsible for carrying out analysis; mainly in excel.

I currently use power pivot models and DAX queries to aggregate and explore my data further.

However I would like my models to link automatically to the table data in sql so that my models update automatically when the sql database is updated.

My questions are as follows?

How do I link a NAV table to powerpivot
Do I have to load all the tables from sql into powerpivot or can I create a connections?
How do I link the models to the sql tables so that the update automatically? I've heard something about using Sharepoint

Thanks
Mike
0
mikes6058
Asked:
mikes6058
2 Solutions
 
tomfarrarCommented:
How do I link a NAV table to powerpivot?

Generally, linking tables to power pivot can be done by using Manage Data Model option under the PowerPivot Tab.  Try this first.
Do I have to load all the tables from sql into powerpivot or can I create a connections?

Once the connection is made to the table via the item mentioned above, the link should remain in place so data shown in PowerPivot table is really linked data.


How do I link the models to the sql tables so that the update automatically? I've heard something about using Sharepoint

As far as automatically, not sure what you mean because manually updated the table is not difficult.  But this link may help:

https://msdn.microsoft.com/en-us/library/gg399134(v=sql.110).aspx
0
 
MarcjevCommented:
The only product supporting this out of the box is sharepoint, as said above.  (see https://technet.microsoft.com/en-us/library/ee210651(v=sql.105).aspx)

Another possibility would be writing an excel macro and calling the 'Sheets("sheetx").PivotTables("PivotTablex").RefreshTable method. You could run this macro automatically when opening the excel document.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now