Query PowerPivot data

ZackT

New Member
Joined
Oct 23, 2011
Messages
1
I would like to be able to query the tables in the PowerPivot cache.

My preference is to not have hidden worksheets or to create worksheets in vba to create temporary data and then delete the worksheet.

I am using PowerPivot which pulls the data from SQL Server Express. The data has been pulled in correctly and I can see the data from the PowerPivot Window Launch button.

I will give the basic table layouts:




Company table
  • CompanyId: integer
  • CompanyCode: string
  • CompanyName: string
Property table
  • PropertyId: integer
  • CompanyId: integer
  • PropertyCode: string
  • PropertyName: string
  • Bedrooms: integer
  • Bathrooms: integer
Availability table



<DIR>PropertyId: integer
AvailDate: date
BatchDate: date
AvailStateId: integer
Month: =Format(Availability[AvailDate], "MMM")
Year: =Year(Availability[AvailDate])
Day: =Day(Availability[AvailDate])

Note: PropertyId, AvailDate, BatchDate are unique



</DIR>AvailState table
  • AvailStateId: integer
  • AvailStateCode: char
I have a nice PowerPivot table with the following:
  • Row Label
    • Property.PropertyName
    • Availability.BatchDate
  • Column Label
    • Avilability.Year
    • Availability.Month
    • Availability.Day
  • Value
    • Sum(AvailStateId)
In the Availability table PropertyId, AvailDate, and BatchDate are unique. There is a unique index for this table in the SQL Server Express data. Therefore, Sum(AvailStateId) is AvailStateId or null. I could have used Max, min, or average instead of Sum.

While the slicers are really cool, they don't allow the ability to select only one value and I can't also configure their layout. My goal is to use two comboboxes.




Company_ComboBox
  • unique list of Company.CompanyNames
  • Change event triggers Property_ComboBox's list to change
Property_ComboBox
  • unique list of Property.PropertyNames that belong to Company defined in Company_ComboBox
  • change event set PivotTable's filter to filter based on Property.PropertyId
There are many examples on dependent comboboxes and also using VBA to alter a pivot table's filter.

But, I have been banging my head on the best way to populate the comboboxes.

Here are my initial thoughts in order of what I currently believe would be the best. I might be missing something very obvious and so I wanted to call out the direction that I have been trying to solve my problem.

Query PowerPivot cache
Can you query the PowerPivot cache directly?
Workbook connection to $Embedded$
I have created a second workbook connection based upon the "PowerPivot Data" connection. I can create a table using this connection. But I cannot figure out how to programmatically use this connection to enumerate through a recordset.
ADO Connection to $Embedded$
I have tried to make an ADO connection to the Embedded Data source, but I cannot connect

Code:
Dim cConnection As ADODB.Connection
cConnection.Open "Provider=MSOLAP.4;Initial Catalog=Microsoft_SQLServer_AnalysisServices;Data Source=$Embedded$"
Query against second PowerPivot table
Create a second PowerPivot table which is a join between Company and Property tables.

What methods can you use to query against a PowerPivot table.

"select CompanyId, CompanyName from Company order by CompanyName"

and then

"select PropertyId, PropertyName from Property where CompanyId = " & CompanyId

Can you use ADO against a PowerPivot table internally in the same workbook? All the examples I see use an exterior workbook file
Enumerate through second PowerPivot table
Create a second PowerPivot table which is a join between Company and Property tables.

I can enumerate through the RowRange and insert CompanyName into Company_ComboBox.

I can enumerate through the RowRange and insert PropertyName into Property_Combox based on Company_ComboBox value matching CompanyId

This is my current method, I am working on.
Enumerate through second PowerPivot table - variation
I understand I could create two pivot tables. One for Company and one for Property which has a filter of CompanyId match. The problem with this design it if I need to do another set combox drop downs for Company and Property I would need another two pivot tables.
 
My preference is not to require a seperate worksheet which contains another table. How can I access the PowerPivot cache or design this without needing temporary worksheets?

Thank you for any help.
 
Last edited:

Excel Facts

Can you AutoAverage in Excel?
There is a drop-down next to the AutoSum symbol. Open the drop-down to choose AVERAGE, COUNT, MAX, or MIN

Forum statistics

Threads
1,222,404
Messages
6,165,853
Members
451,986
Latest member
ExcelIsLove

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top