Merge SQL databases in Excel PowerPivot

marboe
Posts: 5
Joined: Tue Mar 07, 2017 9:19 pm

Merge SQL databases in Excel PowerPivot

Postby marboe » Tue Mar 07, 2017 10:40 pm

Good day,

I have 4 databases in SQL Server Express. They all have the same columns. Each database contains 2.000.000 rows.

I would like to make queries and/or pivottables from PowerPivot on all 4 databases at once.

I tried to follow these instructions:
http://accessanalytic.com.au/combine-mu ... wer-query/

If I do the same for 2 databases (instead of 2 tables as in the link),
I get this code in the Advanced Editor (step 4 from the link):

======================================================================
let
Source = Sql.Database("DESKTOP-1\SQLEXPRESS", "2016-Apples-Part3"),
dbo_AppleData3 = Source{[Schema="dbo",Item="AppleData3"]}[Data],
#"Appended Query" = Table.Combine({dbo_AppleData3, dbo_AppleData3})
in
#"Appended Query"
======================================================================

My seconds database: is ("DESKTOP-1\SQLEXPRESS", "2016-Apples-Part4"),
dbo_AppleData4

My problem is that the Table.Combine statement in my case does not combine Source, it does combine dbo_AppleData3 which is a table in a database.

Does anyone know how to modify the code so that it does Table.Combine for dbo_AppleData3 in database 2016-Apples-Part3 and dbo_AppleData4 in database 2016-Apples-Part4 ?

thank you!

Mike

MattAllington
Posts: 816
Joined: Sun May 04, 2014 4:01 pm
Location: Sydney, Australia

Re: Merge SQL databases in Excel PowerPivot

Postby MattAllington » Wed Mar 08, 2017 7:07 pm

If your source is sql server and you want to load the 4 tables to power pivot, presumably as appended tables, I recommend you write sql code instead and then import from there. The sql would be something like this.

Select *
From table1

Union

Select *
From table2

Union

Select *
From table3

Union

Select *
From table4
Matt Allington is Professional Self Service BI Consultant, Trainer and Author of the book "Learn to Write DAX". You can hire me at http://Exceleratorbi.com.au
http://exceleratorbi.com.au/what-is-power-pivot/
http://xbi.com.au/learndax

marboe
Posts: 5
Joined: Tue Mar 07, 2017 9:19 pm

Re: Merge SQL databases in Excel PowerPivot

Postby marboe » Thu Mar 09, 2017 12:31 am

Hello Matt,

Thank you for your answer.

I have 4 seperate databases in SQL Server Express (due to the limit of 10 GB per database).
Each database contains 1 table. I will try if it is possible to make a query for 4 different databases.

I was hoping I could reach all DB's with one query from PowerPivot (without loading those into a sheet because of the limit of 1.000.000 rows). I suppose modification of the query would be less flexible because you have to modify it in 4 rows every time. If I make a query for all records i think it is too big because of the limit of 10GB.

I will do some testing and let you know.

Thanks.

User avatar
AndVGri
Posts: 18
Joined: Thu Jun 02, 2016 2:41 am
Location: Sankt-Peterburg

Re: Merge SQL databases in Excel PowerPivot

Postby AndVGri » Fri Mar 10, 2017 12:46 am

Hi
Matt wrote about sql query for solution of your task.
Sql text for two different database of single server is

Code: Select all

let
    source = Sql.Database(
       "DESKTOP-1\SQLEXPRESS",
       "2016-Apples-Part3",
       [Query=
"Select * From 2016-Apples-Part3.dbo.AppleData3
Union All
Select * From 2016-Apples-Part4.dbo.AppleData3"
   ])
in
    source

Regards,

marboe
Posts: 5
Joined: Tue Mar 07, 2017 9:19 pm

Re: Merge SQL databases in Excel PowerPivot

Postby marboe » Sat Mar 11, 2017 1:56 am

Hi And,

Thank you for answer. For some reason I do not succeed to replicate the situation in step 4 from the link. I should have saved this particular situation. I will try again in the coming days and let you know if I can replicate the situation and could integrate your code.

Thanks

marboe
Posts: 5
Joined: Tue Mar 07, 2017 9:19 pm

Re: Merge SQL databases in Excel PowerPivot

Postby marboe » Fri Apr 21, 2017 9:15 pm

THIS MESSAGE ABOVE FROM Macros IS SPAM.

Moderator can you delete this please.

Thank you.
Last edited by marboe on Sat Apr 22, 2017 4:38 am, edited 3 times in total.

marboe
Posts: 5
Joined: Tue Mar 07, 2017 9:19 pm

Re: Merge SQL databases in Excel PowerPivot

Postby marboe » Fri Apr 21, 2017 9:40 pm

Hi And, Hi Matt,

I have tried to find a workaround.

I have made a script in Excel VBA with a query to import the records I need from the SQL-databases.

I have 12 databases and I do 12 queries by a loop in VBA.
(Within SQL I made a count first so that every query does not contain > 1.000.000 (the maximum rows in Excel).)

This works perfect with the ADO-query from this website:
https://www.excel-sql-server.com/excel- ... ng-vba.htm

After I have run the loop, I have a workbook with 12 sheets, all with between 700.000 and 1.000.000 rows. All with similar headings.

I would like to make a pivottable with Power Pivot from those 12 sheets with data.
I have added all sheets to the datamodel, after that I try to make a pivottable by clicking Powerpivot > Home > Pivottable.
If I click a certain columnname to use as a Pivot table field, my tables are still separated in the pivot table output.
Is there an option so that Powerpivot sees all 12 sheets as one table?

Thank you!


Return to “Power Query”

Who is online

Users browsing this forum: No registered users and 1 guest