PowerPivot – It sure would be nice if…

I’ve been doing a lot of work with PowerPivot where I connect to databases. My normal development cycle is as follows:

  • Connect to a view or table, pulling in ALL columns
  • Work through my data scenario until I’ve worked out my logic and solve the issue I’m trying to solve
  • Cut the table/view query down to just the required columns
  • Add WHERE clauses to cut the data down to just the bare minimum I need
  • Deploy to my users

By doing all this, I really focus on trying to optimize the file size and refresh time as much as possible.

As I do this, I cut the number of columns out, then flip from the GUI table view to SQL to add my WHERE clause(s). One thing that I find that really sucks though, is that when I do flip the “Table Properties” to SQL view, it comes out like this:

[code]SELECT [dbo].[vw_opt_MemberProfiles].[AccountType],[dbo].[vw_opt_MemberProfiles].[CustomerType],[dbo].[vw_opt_MemberProfiles].[SortCode_Account],[dbo].[vw_opt_MemberProfiles].[SortCode_LastName],[dbo].[vw_opt_MemberProfiles].[ClubMemberCode],[dbo].[vw_opt_MemberProfiles].[Full_Name],[dbo].[vw_opt_MemberProfiles].[LastName],[dbo].[vw_opt_MemberProfiles].[FirstName],[dbo].[vw_opt_MemberProfiles].[MemberCardNumber],[dbo].[vw_opt_MemberProfiles].[GranCert_Previous],[dbo].[vw_opt_MemberProfiles].[Gran_Sold_Date],[dbo].[vw_opt_MemberProfiles].[Gran_Sold_To] FROM [dbo].[vw_opt_MemberProfiles] WHERE [dbo].[vw_opt_MemberProfiles].[SortCode_Account] <> '#'[/code]

I would LOVE to see it come out more like this by default:

[code]
SELECT
[dbo].[vw_opt_MemberProfiles].[AccountType],
[dbo].[vw_opt_MemberProfiles].[CustomerType],
[dbo].[vw_opt_MemberProfiles].[SortCode_Account],
[dbo].[vw_opt_MemberProfiles].[SortCode_LastName],
[dbo].[vw_opt_MemberProfiles].[ClubMemberCode],
[dbo].[vw_opt_MemberProfiles].[Full_Name],
[dbo].[vw_opt_MemberProfiles].[LastName],
[dbo].[vw_opt_MemberProfiles].[FirstName],
[dbo].[vw_opt_MemberProfiles].[MemberCardNumber],
[dbo].[vw_opt_MemberProfiles].[GranCert_Previous],
[dbo].[vw_opt_MemberProfiles].[Gran_Sold_Date],
[dbo].[vw_opt_MemberProfiles].[Gran_Sold_To]

FROM [dbo].[vw_opt_MemberProfiles]

WHERE [dbo].[vw_opt_MemberProfiles].[SortCode_Account] <> '#'
[/code]

It would sure make it a LOT easier to read, and a LOT easier to work with.As it is, I now copy my code out of PowerPivot, then head over to Instant SQL Formatter.  Copy, paste, format, copy and paste it back into PowerPivot.  A heck of a lot easier to read, but also an unnecessary pain in the behind.