Blog Title Changed to "Technology Discussions by Ajay Singh"


This blog was initially started to share the thoughts on the PerformancePoint Server product from Microsoft. However with the recent announcement of PPS Planning being discontinued and Monitoring getting integrated to SharePoint, I thought it would be better to have a generic title for the blog. So I changed the blog title to "Technology Discussions by Ajay Singh" from "PerformancePoint Server Information by Ajay Singh".

 

This doesn’t mean that I will stop posting the information about PerformancePoint Services. Its just that I may post information related to other technologies as well (which I have already started doing 🙂 )

Advertisements

Issue with SubSelect in MDX Query generated by Excel 2007 with Analysis Services 2005


I am using Excel 2007 for connecting to SSAS 2005 (AdventureWorksDW) cube and created a very simple report on perspective "Financial Reporting" where Account (Net Income) is taken on row, Column has Scenario (Actual and Budget) and Fiscal Year (FY 2002) and measure selected is "Amount". Now with this setup (case 1.1) report runs fine and displays the data correctly. However when we move the Scenario to Filter axis (Case 1.2) the Excel shows data only for Actual scenario and Budget data is not considered in the aggregation.

 

However when we create the same report in a Excel file (which is saved in 2003 compatibility mode) the data gets aggregated and the result is displayed as per the expectation.

 

Points that needs to be considered are:

  • Pivot table version used in both cases will be different
  • Usage of SubSelect (introduced from AS2005 SP2) in Excel 2007 version

 

Excel 2007:

Case 1.1: Scenario on Columns

Report Output:

image

MDX Generated:

SELECT NON EMPTY CrossJoin(Hierarchize({[Scenario].[Scenario].[Scenario].Members}), Hierarchize({DrilldownLevel({[Date].[Fiscal Year].[All Periods]})})) DIMENSION PROPERTIES PARENT_UNIQUE_NAME ON COLUMNS , NON EMPTY Hierarchize({[Account].[Accounts].[Account Level 01].Members}) DIMENSION PROPERTIES PARENT_UNIQUE_NAME,[Account].[Accounts].[Account Number],[Account].[Accounts].[Account Type],[Account].[Accounts].[Accounts] ON ROWS  FROM (SELECT ({[Scenario].[Scenario].&[1], [Scenario].[Scenario].&[2]},{[Date].[Fiscal Year].&[2002]}) ON COLUMNS , ({[Account].[Accounts].&[47]}) ON ROWS  FROM [Adventure Works]) WHERE ([Measures].[Amount]) CELL PROPERTIES VALUE, FORMAT_STRING, LANGUAGE, BACK_COLOR, FORE_COLOR, FONT_FLAGS

MDX Output:

image

 

Case 1.2: Scenario on Filter Axis (Scenario member selection is still ‘Actual’ and ‘Budget’ both)

Report Output:

image

MDX Generated:

SELECT NON EMPTY Hierarchize({DrilldownLevel({[Date].[Fiscal Year].[All Periods]})}) DIMENSION PROPERTIES PARENT_UNIQUE_NAME ON COLUMNS , NON EMPTY Hierarchize({[Account].[Accounts].[Account Level 01].Members}) DIMENSION PROPERTIES PARENT_UNIQUE_NAME,[Account].[Accounts].[Account Number],[Account].[Accounts].[Account Type],[Account].[Accounts].[Accounts] ON ROWS  FROM (SELECT ({[Date].[Fiscal Year].&[2002]}) ON COLUMNS , ({[Account].[Accounts].&[47]}) ON ROWS  FROM (SELECT ({[Scenario].[Scenario].&[2],[Scenario].[Scenario].&[1]}) ON COLUMNS  FROM [Adventure Works])) WHERE ([Measures].[Amount]) CELL PROPERTIES VALUE, FORMAT_STRING, LANGUAGE, BACK_COLOR, FORE_COLOR, FONT_FLAGS

MDX Output:

image

 

Excel 2007 in 2003 Compatibility Mode:

Case 2.1: Scenario on Columns

Report Output:

image

MDX Generated:

SELECT NON EMPTY CrossJoin(Hierarchize(Except({AddCalculatedMembers(Except({AddCalculatedMembers([Scenario].[Scenario].[Scenario].members)}, {[Scenario].[Scenario].[Budget Variance %],[Scenario].[Scenario].[Budget Variance],[Scenario].[Scenario].&[3]}))}, {[Scenario].[Scenario].[Budget Variance %],[Scenario].[Scenario].[Budget Variance],[Scenario].[Scenario].&[3]})),Hierarchize(Except({AddCalculatedMembers(Except({AddCalculatedMembers(DrilldownLevel({[Date].[Fiscal Year].[All Periods]}))}, {[Date].[Fiscal Year].&[2007],[Date].[Fiscal Year].&[2005],[Date].[Fiscal Year].&[2004],[Date].[Fiscal Year].&[2003]}))}, {[Date].[Fiscal Year].&[2007],[Date].[Fiscal Year].&[2005],[Date].[Fiscal Year].&[2004],[Date].[Fiscal Year].&[2003]}))) DIMENSION PROPERTIES PARENT_UNIQUE_NAME ON COLUMNS , NON EMPTY Hierarchize(Except({AddCalculatedMembers(Except({AddCalculatedMembers([Account].[Accounts].[Account Level 01].members)}, {[Account].[Accounts].&[95],[Account].[Accounts].&[1]}))}, {[Account].[Accounts].&[95],[Account].[Accounts].&[1]})) DIMENSION PROPERTIES PARENT_UNIQUE_NAME ON ROWS  FROM [Adventure Works] WHERE ([Measures].[Amount]) CELL PROPERTIES VALUE, FORMAT_STRING, LANGUAGE, BACK_COLOR, FORE_COLOR, FONT_FLAGS

MDX Output:

image

 

Case 2.2: Scenario on Filter Axis (Scenario member selection is still ‘Actual’ and ‘Budget’ both)

Report Output:

image

MDX Generated:

WITH   MEMBER [Scenario].[Scenario].[XL_QZX] AS ‘Aggregate({[Scenario].[Scenario].&[2],[Scenario].[Scenario].&[1]})’ SELECT NON EMPTY Hierarchize(Except({AddCalculatedMembers(Except({AddCalculatedMembers(DrilldownLevel({[Date].[Fiscal Year].[All Periods]}))}, {[Date].[Fiscal Year].&[2007],[Date].[Fiscal Year].&[2005],[Date].[Fiscal Year].&[2004],[Date].[Fiscal Year].&[2003]}))}, {[Date].[Fiscal Year].&[2007],[Date].[Fiscal Year].&[2005],[Date].[Fiscal Year].&[2004],[Date].[Fiscal Year].&[2003]})) DIMENSION PROPERTIES PARENT_UNIQUE_NAME ON COLUMNS , NON EMPTY Hierarchize(Except({AddCalculatedMembers(Except({AddCalculatedMembers([Account].[Accounts].[Account Level 01].members)}, {[Account].[Accounts].&[95],[Account].[Accounts].&[1]}))}, {[Account].[Accounts].&[95],[Account].[Accounts].&[1]})) DIMENSION PROPERTIES PARENT_UNIQUE_NAME ON ROWS  FROM [Adventure Works] WHERE ([Measures].[Amount],[Scenario].[Scenario].[XL_QZX]) CELL PROPERTIES VALUE, FORMAT_STRING, LANGUAGE, BACK_COLOR, FORE_COLOR, FONT_FLAGS

MDX Output:

image

Why I use Firefox for Browsing instead of Internet Explorer 8


I generally like Microsoft products, however I don’t have the same feeling as far as Browser is concerned. There are many small things that are not available in IE (even in latest release 8.0) which gives frustration during normal browsing. Because of which I use Firefox for regular browsing and IE is used only for Business Applications (e.g. use of ActiveX Controls etc…).

Here are some of the items which are implemented in much user friendly way in Firefox that it is in IE:

#1: Closing Tabs: Why I don’t get the close tab icon for each tab in IE. I always have to select the tab so that tab gets activated and then only I get the close icon. If I have 10 tabs open and I want to close 5 of them, I have to first select each of them and then click on close.

image 

image

 

#2: Close option when only tab is open: This should be always available and it should just point the current tab to the my Home Page.

image

image

 

#3: ‘Undo Close Tab’ feature of Firefox is very useful and is accessible very easy way. In IE 8 I have to open a new tab to see the list of recently closed tab.

image

image

 

#4: Adding link to Favourites: Have you ever tried adding links to Favourites in Firefox and IE8. Try to do that once and you will see the difference. If you drag the link over a Folder in Firefox the folder gets expanded and you can place the link any where or even in a nested folder inside the root folder. However doing the same in IE 8 you can see that the folder doesn’t gets expanded and you end up adding the link to the root folder.

#5: Expanding of Favourites folder: Very small thing but really effective. In Firefox you expand one Favourite folder and then mouse over to the next folder, you can see that the previous folder gets closed and the current highlighted folder gets expanded. However do the same thing in IE and you can see that nothing happens. If you click once the previous folder gets closed and if you click second time then the current folder gets expanded. Very bad indeed.

#6: Download Manager: Why there is not a integrated Download Manager in IE 8.

#7: Firefox’s Awesome Bar is really more intelligent than the IE 8. 80% of the time (for me) I get what I am looking for just by typing the relevant words.

These are just few point to which I will keep adding which hopefully gives some ideas to Microsoft folks to why a common user is forced to use other browsers over IE for regular browsing.