Browsing Cube in PowerBI

[powerBI_L1]

I am trying to see if there is a way in PowerBI(using builtin or custom visuals) where the end user can drag and drop the dimensions or mesaures into the report.

The basic idea is to create blank report and the end user will choose what dimensions\measures they want to analyse. My requirement is similar to how we can browse the cube in SSMS.

I want to be able to do that same in PowerBI. Is this possible?

So far, based on what I tested and researched, it looked like we cannot slice and dice the data once deployed. Any ideas?

[vanessafvg · Accepted solution]

are you wanting to expand individual rows?  

If you want to expand all the data you can use the new matrix visual, you can also drill down into the data but sadly I dont think you can expand an individual row in the table/matrix because i have been looking for a solution like this too and haven't found one yet.

seems to be a common requirement so i hope the power bi team sort it out soon.

[Greg_Deckler · Accepted solution]

You could use the new matrix visualization for drill down. You could also just add a date slicer perhaps?

[v-qiuyu-msft · Accepted solution]

Hi @powerBI_L1,

You can create a report connect live to SSAS in desktop, which you can get all dimensions and measures like in SSMS. Then there are two methods you can try:

1.  Publish that report to a Power BI app workspace. Grant member Edit permission in this app workspace. Each member can edit the report. See: Create and distribute an app in Power BI

2. Publish that report to My Workspace, then create a content pack to package report and dataset, publish the content pack to whole organization or specific group. End user can get this content pack and make a copy of content pack to customize it. See: Intro to organizational content packs in Power BI.

To get expand and collapse feature, you can vote on this idea: Matrix +/- Collapse/ Expand Buttons. You can slicer data in matrix preview on different levels as suggested by other guys: Use the new Matrix visual in Power BI Desktop (Preview).

Best Regards,
Qiuyun Yu

[powerBI_L1]

Thanks every one. I am trying to doing this on premises PowerBI

Also, After I enabled the new matrix preview fliter, I am not able to deploy the report. Is this because this is a preview feature?

[vanessafvg]

@powerBI_L1 what error are you getting?

[Greg_Deckler]

You should be able to create a Direct Query query to the cube. What type cube is it Tabular or MDX? What you are discussing is exactly what Power BI was designed for so I must be missing something.

[powerBI_L1]

Thanks smoupre for the help. I am using MDX and "Connect Live" connection. I guess thats direct query,so i do not see any explicit connection "DirectQuery" in PowerBI Desktop.

The issue is giving the end user the ability to slice and dice the data. Let me explain with an example. Say, you have TotalSales as Measure and Date as Dimension and Date is a hireachy( Year- Quarter- Month). 

So, Rigth now, I am using Table Visualization( I do not want any bars\graphs for this), and it giving stragith Table display. There is no way for me to collapse\expand(slice\dice) the data by based on the Date dimension.

I want the end user to have the ability to slice and dice the date dimension as needed.

[Greg_Deckler · Accepted solution]

You could use the new matrix visualization for drill down. You could also just add a date slicer perhaps?

[vanessafvg · Accepted solution]

are you wanting to expand individual rows?  

If you want to expand all the data you can use the new matrix visual, you can also drill down into the data but sadly I dont think you can expand an individual row in the table/matrix because i have been looking for a solution like this too and haven't found one yet.

seems to be a common requirement so i hope the power bi team sort it out soon.

[KHorseman]

Sounds like you're talking about drilldown. You'll want to use the new matrix preview visual for that. You have to turn it on in your options under the preview settings.