Query PowerPivot data

[ZackT]

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

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.