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:

  1. 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] <> '#'

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

  1. SELECT
  2. [dbo].[vw_opt_MemberProfiles].[AccountType],
  3. [dbo].[vw_opt_MemberProfiles].[CustomerType],
  4. [dbo].[vw_opt_MemberProfiles].[SortCode_Account],
  5. [dbo].[vw_opt_MemberProfiles].[SortCode_LastName],
  6. [dbo].[vw_opt_MemberProfiles].[ClubMemberCode],
  7. [dbo].[vw_opt_MemberProfiles].[Full_Name],
  8. [dbo].[vw_opt_MemberProfiles].[LastName],
  9. [dbo].[vw_opt_MemberProfiles].[FirstName],
  10. [dbo].[vw_opt_MemberProfiles].[MemberCardNumber],
  11. [dbo].[vw_opt_MemberProfiles].[GranCert_Previous],
  12. [dbo].[vw_opt_MemberProfiles].[Gran_Sold_Date],
  13. [dbo].[vw_opt_MemberProfiles].[Gran_Sold_To]
  14.  
  15. FROM [dbo].[vw_opt_MemberProfiles]
  16.  
  17. WHERE [dbo].[vw_opt_MemberProfiles].[SortCode_Account] <> '#'

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.