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.
