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.
