-2

I am not sure if this is possible but I have a very large data set (extracted using Microsoft SQL Server Management Studio) and would like to import this into some sort of cube (OLAP?) to be analysed in Microsoft Excel (Pivot tables etc.) which is of course limited by c.1 million lines.

Ideally I would to run a query from management studio directly into a cube which is then accessed via Excel.

Thanks in advance.

Kajan
  • 45
  • 2
  • 9

1 Answers1

0

You can create a Pivot Table using an external data.

Then you can give the SQL Server as a source and it would not matter that the source is with more than 1 million rows:

enter image description here

Vityata
  • 42,633
  • 8
  • 55
  • 100
  • Thank you for the help. Is there a way to make this connection dynamic? Pass in variables using cell in an Excel sheet? – Kajan Feb 09 '18 at 13:19
  • @Kajan - I really hope it is not possible to access the DB dynamically, because it means that the password and the username for the DB would be visible in the cell. And this is a huge security problem. – Vityata Feb 09 '18 at 16:25
  • Very true but I simply wanted to refresh using user defined variables e.g. date range. – Kajan Feb 12 '18 at 14:15
  • @Kajan - something like this should be quite ok for a simple refresh - `Range("A1").ListObject.QueryTable.Refresh BackgroundQuery:=False` – Vityata Feb 12 '18 at 14:22
  • thank you but where do you pass in the date variables? – Kajan Feb 12 '18 at 16:14