PowerPivot- Are Measures alwasy required in SQL Queries?

[Jerry Sullivan]

Hello PowerPivot users!

I'm relatively new to PowerPivot and I'm trying to understand a fundamental concept of SQL Queries of OLAP sources.

Is there a way to extract a lookup table from an OLAP source without including a Measure in the Query?

For example, if I want to generate a table that has two fields: Project and Project Manager for all projects in the database, it seems the only way I can create that table in the PowerPivot Table Import Wizard design window is if I also include a measure like "Revenue" or "NetProfit".

I can do that then hide the field, but it seems unnecessary.

I'm hoping that someone can explain the concept that I'm missing instead of just a work around to make that specific table.

Thanks in advance!

 

[Jacob Barnett]

Jerry, I've never had the opportunity to use an OLAP source with PowerPivot but it looks like Marco's post may be talking about this issue:

SQLBI - Marco Russo : Using SSAS 2005/2008 as PowerPivot Data Source: Query Designer

 

[Jerry Sullivan]

Re: PowerPivot- Are Measures always required in SQL Queries?

Hi Jacob, Thanks for the finding that link. I'm surprised to find that the fix is as simple as clicking the "Show Empty Cells" button.
The article states "...it is not intuitive for the Excel user who is trying to import data in PowerPivot." - which was an understatement in my case.

Standard Excel PivotTables have a similar option to "show items with no data". The difference is that the PowerPivot Design Wizard shows no records when the "Show Empty Cells" button is not clicked instead of showing only those records that don't have empty cells. If you or anyone else can share some insight as to why that is I would appreciate learning that.