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.

If you have a comment or question about the blog post content, please feel free to post it here. If you need help adapting this solution to your own needs, please post in our free help forum.

Your email address will not be published. Required fields are marked *