2

I'm attempting to create an Excel pivot table based on data in a Microsoft Dynamics NAV database.

The trick is I need the Excel to access the data directly from the SQL Server database with power query - and furthermore it must be able to access the data from the same table in multiple databases with different names and table names.

Does anybody have any experience or advice regarding this issue?

TylerH
  • 20,799
  • 66
  • 75
  • 101
AronChan
  • 245
  • 3
  • 19
  • Create SQL Server Stored procedure. Put all your calculations in it. The last statement of procedure should be `select` wich will return all data you need for the pivot. To access different databases and tables you may use [Dymanic SQL](https://msdn.microsoft.com/en-us/library/ms709342(v=vs.85).aspx) and [Exec](https://msdn.microsoft.com/ru-ru/library/ms188332(v=sql.120).aspx) – Mak Sim Jan 26 '16 at 14:24

2 Answers2

2

Step 1. First you should make a function where you can pass a server name, database name and table to be queried. Something like

let getData =(servername,dbname,tablename)=>
let
Source = Sql.Database(Servername, dbname, [Query="select  abc , def  from" & tablename & " where condition etc etc"]),
#"CustomStep1" = some action on Source,
in
.
.
#"CustomStepn" = some action on Added CustomStepn-1
in
#"Added CustomStepn"
in  
getData

You have a function ready which you can use in a table to create a custom column.

Step 2. Now use a parameter table approach. Create a table in normal excel area.Something like 

Server Name|DatabaseName | Table_to_be_used

Use now use menu option fromtable in powerquery options (or Data tab in Excel 16) Add a custom column in this table in powerquery steps using function getdata created in previous Step. Perform any other "Expand" (By default first function is going to return a table if you are not doing any other transformation) , "summarize" , Rename Operation.

However powerquery formula firewall is going to give you hardtime as powerquery doesn't trust Native SQL queries and you will have to approve each and every native sql query. You may try to uncheck checkbox for "Require user approval for new native database query" in query option.

Hope you get the idea and it helps.

S M
  • 81
  • 7
  • This will work well, but make sure that the `tablename` can only be modified by queries you trust, to avoid SQL injection against your database e.g. `Blah; DROP TABLE Users; select * from Blah` – Carl Walsh Jan 27 '16 at 00:39
  • 1
    :) I think that's one of the reason why power query doesn't want to trust sql queries from user beside it messes up with query folding. – S M Jan 27 '16 at 00:55
  • It probably would be better to avoid the native query like this `Source = Sql.Database(servername, dbname){[Item = tablename]}` that fixes the security concern and folding too! :) If you need to specify the table schema, then `Sql.Database(servername, dbname){[Schema = schemaname, Item = tablename]}` – Carl Walsh Jan 27 '16 at 08:39
1

Perhaps it would be worth looking into creating Query objects and exposing them via oData which is something Excel can read from. The benefit here is that it can handle table relations natively and can expose Flow Fields which you cannot see in direct SQL queries to the table.

Aside from a stored procedure to manage the different table names, there's not a simple way to query specific tables without hard coding the names in some capacity.

The Company table will give you the prefix$ and the table names are static between companies. You could write some fancy Excel logic to loop through them.

Jake Edwards
  • 1,190
  • 11
  • 24