View RSS Feed

The ExcelGuru (Ken Puls) Blog

PowerPivot - It sure would be nice if...

Rate this Entry
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] <> '#'
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] <> '#'
It would sure make it a LOT easier to read, and a LOT easier to work with.

Submit "PowerPivot - It sure would be nice if..." to Digg Submit "PowerPivot - It sure would be nice if..." to del.icio.us Submit "PowerPivot - It sure would be nice if..." to StumbleUpon Submit "PowerPivot - It sure would be nice if..." to Google Submit "PowerPivot - It sure would be nice if..." to Twitter Submit "PowerPivot - It sure would be nice if..." to Facebook Submit "PowerPivot - It sure would be nice if..." to LinkedIn

Tags: powerpivot
Categories
PowerPivot , Excel

Comments

  1. sourav.majumder's Avatar
    API/Service to interact ith PowerPivot excel 2010

    1. Is there any Java API to interact with PowerPivot excel 2010?
    2. Any web service / .net api to the same?
    3. Once we save the imported data, how it gets embedded in PowerPivot?
    4. Is there any way to understand the relation and fetch the PowerPivot data through any API?