tag:blogger.com,1999:blog-329985002024-03-13T06:20:33.424+05:30Microsoft Analysis Services & MDX - blogs by Ajit SinghAjit Singh on Business Intelligence using Microsoft Analysis Services and MDX (MultiDimensionalExpressions)Ajit Singhhttp://www.blogger.com/profile/17889678799981918534noreply@blogger.comBlogger30125tag:blogger.com,1999:blog-32998500.post-54020305981213819222013-06-21T11:59:00.001+05:302013-06-21T11:59:06.874+05:30Business Intelligence domain players–2011 to 2013<p> </p> <p><a href="http://lh5.ggpht.com/-3Vhqz4H1wV8/UcPyriEDeII/AAAAAAAAI00/XVqyZI67B3o/s1600-h/image%25255B4%25255D.png"><img style="background-image: none; border-bottom: 0px; border-left: 0px; padding-left: 0px; padding-right: 0px; display: inline; border-top: 0px; border-right: 0px; padding-top: 0px" title="image" border="0" alt="image" src="http://lh4.ggpht.com/-T2t_4Mw3unw/UcPysQQjAnI/AAAAAAAAI08/xs37C-SSqAo/image_thumb%25255B2%25255D.png?imgmax=800" width="1133" height="408" /></a></p> Ajit Singhhttp://www.blogger.com/profile/17889678799981918534noreply@blogger.com0tag:blogger.com,1999:blog-32998500.post-51829097296029406652012-03-23T11:49:00.002+05:302012-04-11T10:16:04.394+05:30MDX Clients and Servers<p>The following is a list of some familiar applications/clients and servers that support MDX in the marketplace.</p> <table><tbody> <tr> <td> <p><strong><span>MDX Clients</span></strong> </p> </td> <td> <p><strong><span>MDX Servers</span></strong> </p> </td> </tr> <tr> <td> <p><span><a href="http://www.arcplan.com/en/products/arcplan-edge/" target="_blank"><span>arcplan Edge</span></a></span></p> </td> <td> <p><span><a href="http://www.descisys.com/products/terasolve" target="_blank"><span>Descisys Terasolve</span></a></span></p> </td> </tr> <tr> <td> <p><span><a href="http://www.arcplan.com/en/products/enterprise/" target="_blank"><span>arcplan Enterprise</span></a></span></p> </td> <td> <p><span><a href="http://www-01.ibm.com/software/data/cognos/products/tm1/" target="_blank"><span>IBM Cognos TM1</span></a></span></p> </td> </tr> <tr> <td> <p><span><a href="http://www.arcplan.com/en/products/excel-analytics/" target="_blank"><span>arcplan Excel Analytics</span></a></span></p> </td> <td> <p><span><a href="http://www-01.ibm.com/software/data/infosphere/" target="_blank"><span>IBM InfoSphere</span></a></span></p> </td> </tr> <tr> <td> <p><span><a href="http://www.arcplan.com/en/products/freemobilebi/" target="_blank"><span>arcplan Mobile BI</span></a></span></p> </td> <td> <p><span><a href="http://www.infor.com/solutions/infor10/" target="_blank"><span>Infor PM OLAP (formerly MIS Alea)</span></a></span></p> </td> </tr> <tr> <td> </td> <td> </td> </tr> <tr> <td> <p><span><a href="http://www.bissantz.com/products/" target="_blank"><span>Bissantz DeltaMaster</span></a></span></p> </td> <td> <p><span><a href="http://www.kognitio.com/pablo" target="_blank"><span>Kognitio Pablo</span></a></span></p> </td> </tr> <tr> <td> <p><span><a href="http://www-01.ibm.com/software/data/cognos/" target="_blank"><span>IBM Cognos</span></a></span></p> </td> <td> <p><span><a href="http://www.microsoft.com/sqlserver/2008/en/us/analysis-services.aspx" target="_blank"><span>Micrsosoft Analysis Services</span></a></span></p> </td> </tr> <tr> <td> <p><span><a href="http://www.jaspersoft.com/" target="_blank"><span>Jaspersoft</span></a></span></p> </td> <td> <p><span><a href="http://sourceforge.net/project/shownotes.php?release_id=576754" target="_blank"><span>Mondrian</span></a></span></p> </td> </tr> <tr> <td> <p><span><a href="http://office.microsoft.com/en-ca/excel/default.aspx" target="_blank"><span>Microsoft Excel</span></a></span></p> </td> <td> <p><span><a href="http://www.oracle.com/technology/products/bi/olap/index.html" target="_blank"><span>Oracle Database OLAP Option</span></a></span></p> </td> </tr> <tr> <td> <p><span><a href="http://www.microsoft.com/india/bi/products/proclarity/proclarity-overview.aspx" target="_blank"><span>Microsoft Proclarity</span></a></span></p> </td> <td> <p><span><a href="http://www.oracle.com/technetwork/middleware/essbase/overview/index.html" target="_blank"><span>Oracle Essbase</span></a></span></p> </td> </tr> <tr> <td> <p><span><a href="http://www.microsoft.com/sqlserver/2008/en/us/reporting.aspx" target="_blank"><span>Microsoft Reporting Services</span></a></span></p> </td> <td> <p><span><a href="http://www.panoratio.com/products.html" target="_blank"><span>Panoratio</span></a></span></p> </td> </tr> <tr> <td> <p><span><a href="http://www.microstrategy.com/" target="_blank"><span>Microstrategy</span></a></span></p> </td> <td> <p><span><a href="http://www.sap.com/solutions/sapbusinessobjects/large/enterprise-performance-management/planningandconsolidation/index.epx" target="_blank"><span>SAP BusinessObjects Planning and Consolidation</span></a></span></p> </td> </tr> <tr> <td> <p><span>Oracle Interactive Reporting (formerly Brio)</span></p> </td> <td> <p><span><a href="http://www.sap.com/solutions/sapbusinessobjects/enterprise-performance-management/pcm/index.epx" target="_blank"><span>SAP BusinessObjects Profitability and Cost Management</span></a></span></p> </td> </tr> <tr> <td> <p><span><a href="http://www.panorama.com/" target="_blank"><span>Panorama NovaView</span></a></span></p> </td> <td> <p><span><a href="http://www.sap.com/solutions/sapbusinessobjects/enterprise-performance-management/strategy/index.epx" target="_blank"><span>SAP BusinessObjects Strategy Management</span></a></span></p> </td> </tr> <tr> <td> <p><span><a href="http://www.response42.com/products.html" target="_blank"><span>Response 42 Query & Report</span></a></span></p> </td> <td> <p><span><a href="http://www.sap.com/platform/netweaver/index.epx" target="_blank"><span>SAP NetWeaver BW</span></a></span></p> </td> </tr> <tr> <td> <p><span><a href="http://www.sap.com/solutions/sapbusinessobjects/sme/reporting-dashboarding/index.epx" target="_blank"><span>SAP BusinessObjects Crystal Reports</span></a></span></p> </td> <td> <p><span><a href="http://www.sap.com/hana/overview/index.epx" target="_blank"><span>SAP HANA</span></a></span></p> </td> </tr> <tr> <td> <p><span><a href="http://www.sap.com/solutions/sapbusinessobjects/large/business-intelligence/advanced-analytics/voyager/index.epx" target="_blank"><span>SAP BusinessObjects Voyager</span></a></span></p> </td> <td> <p><span><a href="http://www.sas.com/technologies/dw/storage/mddb/index.html" target="_blank"><span>SAS OLAP Server</span></a></span></p> </td> </tr> <tr> <td> <p><span><a href="http://www.sap.com/solutions/sapbusinessobjects/large/business-intelligence/reporting-analysis/analysis-olap/index.epx" target="_blank"><span>SAP BusinessObjects Analysis, Edition for OLAP (next version of Voyager)</span></a></span></p> </td> <td> <p><span><a href="http://www.teradata.com/tools-and-utilities/Teradata-OLAP-Connector/" target="_blank"><span>Teradata</span></a></span></p> </td> </tr> <tr> <td><a href="http://code.google.com/p/pentahoanalysistool/" target="_blank">Saiku (Formerly PentahoAnalysisTool)</a></td> <td> <p><span> </span></p> </td> </tr> <tr> <td> </td> <td> </td> </tr> </tbody></table> Ajit Singhhttp://www.blogger.com/profile/17889678799981918534noreply@blogger.com1tag:blogger.com,1999:blog-32998500.post-12602854983187679392008-07-19T10:46:00.001+05:302008-09-26T15:00:27.835+05:30Fact table design for "State Workflow Analysis": Analysis Services Dimensional modeling<p><em>Note: This is a work in progress article and may be updated regularly based on feedback.</em></p> <p>There are many business analytical needs whose data is in the form of “State Workflow”. A state workflow consists of a set of states. There are a valid number of “State Transitions”, i.e. changing of one state to another. The state transitions happen by some event. There can be a valid number of events responsible for state transition. </p> <p>The typical business problem that conforms to above situation is “Sales Pipeline” analysis. The typical queries that need to be resolved are:</p> <ul> <li>How long it took to move from one state to another? </li> <li>How many items moved from one state to another? </li> <li>How much time spent in each state for a given period range? </li> <li>What is the count of items in a given state for a period range vs another period range? </li> <li>What is the aggregation of a related attribute with reference to state? </li> </ul> <p>One typical analysis as presented by below graph is that for each day, what is the "% of Count of All opportunities" in each stages of sales pipeline.</p> <p><a href="http://lh3.ggpht.com/ajit555/SNyqhSvZRsI/AAAAAAAABJ4/iMGv53e3Tyk/s1600-h/image%5B6%5D.png"><img style="border-right: 0px; border-top: 0px; border-left: 0px; border-bottom: 0px" height="284" alt="Pipleline Evaluation by Stage" src="http://lh5.ggpht.com/ajit555/SIF4RAZjmVI/AAAAAAAABJ8/iou-vw0dL1Y/image_thumb%5B5%5D.png?imgmax=800" width="504" border="0" /></a> </p> <p>Or analyze the data using additional dimension such as "by Industry"</p> <p><a href="http://lh4.ggpht.com/ajit555/SIF4S1mLLUI/AAAAAAAABKA/TjuGTYveGw4/s1600-h/image25%5B3%5D.png"><img style="border-right: 0px; border-top: 0px; border-left: 0px; border-bottom: 0px" height="440" alt="image" src="http://lh6.ggpht.com/ajit555/SIF4UYsvY1I/AAAAAAAABKE/WjK2o0LnGws/image25_thumb%5B2%5D.png?imgmax=800" width="504" border="0" /></a> </p> <p>Or by "Booked Revenue"</p> <p><a href="http://lh6.ggpht.com/ajit555/SIF4XzaJmpI/AAAAAAAABKI/P4zkInKBv4I/s1600-h/image29%5B3%5D.png"><img style="border-right: 0px; border-top: 0px; border-left: 0px; border-bottom: 0px" height="440" alt="image" src="http://lh6.ggpht.com/ajit555/SIF4ZqcC7MI/AAAAAAAABKM/rHqTTcVbUKk/image29_thumb%5B2%5D.png?imgmax=800" width="504" border="0" /></a> </p> <p>The question is how we design our OLAP system to do such kind of analysis. Well, the trick lies in creating an intelligent fact table where we capture many of details at the time of ETL process itself rather than importing the transaction system "AS-IS" and hoping that some OLAP magic wand would answer all of our queries.</p> <p>Let us understand it with some simple example which needs similar kind of analysis. In today’s rising gasoline cost, what better example is to understand how I drive or use my car?</p> <p>If you think about operating a car, you will immediately think of several states that could be modeled in a workflow. Here are the states that I’ve chosen for this example:</p> <ul> <li>Not Running: In this state, you are in the car but the engine is not running. </li> <li>Idling: You’ve now started the engine, but you’re not moving. </li> <li>Moving Forward: The car is moving forward. </li> <li>Moving in Reverse: The car is moving in reverse. </li> <li>Done with the Car: You are finished with the car. </li> </ul> <p>There are many other states that you could model, but this list is enough to provide a substantial example. The next step is to identify the events that can occur while you are in each state.</p> <p>Below table lists the events that are allowed for each state, along with the planned state transitions as each event is handled.</p> <p><a href="http://lh4.ggpht.com/ajit555/SIF4bRXt14I/AAAAAAAABJo/RQ2AvUCFxW8/s1600-h/image10%5B7%5D.png"><img style="border-right: 0px; border-top: 0px; border-left: 0px; border-bottom: 0px" height="302" alt="image" src="http://lh3.ggpht.com/ajit555/SIF4cwHmpLI/AAAAAAAABJs/ieEf2pjU4qo/image10_thumb%5B6%5D.png?imgmax=800" width="404" border="0" /></a> </p> <p>(Well, I still drive a manual gear car, for the sheer pleasure of it)</p> <p>Lets look at our typical transactional system. Lets say I have an advanced tracking system installed in my car which records the states and events in a file. Typically, the transactional data recorded is very simple in the following structure:</p> <p><a href="http://lh4.ggpht.com/ajit555/SIF4eT1ZqiI/AAAAAAAABJA/nNxxzamiZwY/s1600-h/image14%5B3%5D.png"><img style="border-right: 0px; border-top: 0px; border-left: 0px; border-bottom: 0px" height="964" alt="image" src="http://lh4.ggpht.com/ajit555/SIF4gJZ-2PI/AAAAAAAABJE/5c1jog8r3HM/image14_thumb%5B2%5D.png?imgmax=800" width="360" border="0" /></a> </p> <p>Now, the typical analytical questions to understand my driving habits are: </p> <p>On a given day, month or year: </p> <ul> <li>How many miles I drive in “Moving Forward”+ “Moving in Reverse” or individual state? </li> <li>How many times I “Apply Brake” while in “Moving Forward” state? </li> <li>How many miles I drive in “Gear Four” event in “Moving Forward” state? (higher the gear, better is the mileage) </li> <li>How much time is spent in “Idling” state for “Traffic Wait” event? (higher the time, higher is the fuel wastage) </li> <li>How many times, I do “Start the engine” event from “Not Running” state? </li> <li>How many times, I do “Beep Horn” even? (Am I compulsive honker?) </li> <li>How many times I change gears while in “Moving Forward” state? </li> <li>What is my average speed per trip? </li> <li>What is my average speed per trip while in “Moving Forward” state? </li> <li>Many more you can think of. </li> </ul> <p>To answer the above analytical questions from our transactional data presented above is difficult for the end users. If a cube is created directly on the above transactional data, the MDX queries would be even more difficult. The point is, we want our end users to do more of analysis rather than spend effort on arranging the data prior to analysis.</p> <p>The trick is to setup a ETL system which produces a synthesized fact table for data analysis. Lets take the below fact table which presents the same transactional data provided above:</p> <p><a href="http://lh6.ggpht.com/ajit555/SIF4h5LdoEI/AAAAAAAABKQ/UBiORxUh6to/s1600-h/image18%5B7%5D.png"><img style="border-right: 0px; border-top: 0px; border-left: 0px; border-bottom: 0px" height="693" alt="image" src="http://lh3.ggpht.com/ajit555/SIF4jmXj2qI/AAAAAAAABKU/xu726Vxfzss/image18_thumb%5B6%5D.png?imgmax=800" width="504" border="0" /></a> </p> <p>From the above synthesized fact table, lets review if we can get the answers of our typical analytical questions to understand my driving habits. I think it should answers all of them.</p> <p>We can create an OLAP cube on top of this fact table and using aggregation functions, answer the same analytical questions using MDX queries or expose the data to some OLAP client. Well, this is a topic for some other day.</p> Ajit Singhhttp://www.blogger.com/profile/17889678799981918534noreply@blogger.com14tag:blogger.com,1999:blog-32998500.post-86904808423379779472008-07-14T23:13:00.002+05:302008-07-14T23:14:21.102+05:30Google specialized search for Analysis Services and MDX web resources integrated in my blog<p>I have been tracking web pages related to Analysis Services and MDX since 2005. Over these few years, I have collected numerous links, articles, blogs on Analysis Services and Multi-Dimensional Expressions. </p> <p>Normally, I tried to store the key articles on my computer disk but there are many resources which I wished I could just search on. Finally, this Sunday afternoon, I extracted all the URLs and created my own Google custom search engine which tracks the web sites indexed my me. It turned out to be very cool and most of the time gave very accurate results which otherwise was not possible to get in generic Google search engine. </p> <p>E.g., if you search for a very generic term called "Bucket", here is what you would get:</p> <p><a href="http://lh4.ggpht.com/ajit555/SHuQCAlssYI/AAAAAAAABHA/qGjboX-UaRQ/s1600-h/BucketGoogleSearch%5B3%5D.jpg"><img style="border-right: 0px; border-top: 0px; border-left: 0px; border-bottom: 0px" height="811" alt="BucketGoogleSearch" src="http://lh4.ggpht.com/ajit555/SHuQEkDC8iI/AAAAAAAABHE/bsbptL6tG2A/BucketGoogleSearch_thumb%5B1%5D.jpg?imgmax=800" width="756" border="0" /></a> </p> <p>Well, try the same search in the customized search option in my blog and here is the result:</p> <p><a href="http://lh3.ggpht.com/ajit555/SHuQGkEJ-QI/AAAAAAAABHI/FptpnjMfgxE/s1600-h/AjitSinghMDXCustomSearch%5B3%5D.jpg"><img style="border-right: 0px; border-top: 0px; border-left: 0px; border-bottom: 0px" height="855" alt="AjitSinghMDXCustomSearch" src="http://lh3.ggpht.com/ajit555/SHuQI0LEzhI/AAAAAAAABHM/oeT3Y9Q4iSM/AjitSinghMDXCustomSearch_thumb%5B1%5D.jpg?imgmax=800" width="761" border="0" /></a> </p> <p>Here you would get a bunch of links which deals with the "Bucket Analysis" using Multi-dimensional expression (MDX) queries. Well, the result also listed lots of sites from one "Gary Low" whose blog on Analysis Services had a name "Bucket" so, I needed to filter out the "Gary Low" in my search keyword.</p> <p>There has been a lot of effort on my part gone into setting up this custom search specifically for Analysis Services and MDX and hope that you too can take advantage of it.</p> <p>Happy searching!!</p>Ajit Singhhttp://www.blogger.com/profile/17889678799981918534noreply@blogger.com1tag:blogger.com,1999:blog-32998500.post-83865233895533685302008-07-12T20:39:00.001+05:302008-07-14T09:09:54.333+05:30NextAnalytics and MDX : Part 1 - Swap Cells with Row Labels<p>NextAnalytics blog on "<a href="http://www.nextanalytics.com/MyBlog/MyBlog/Can-a-business-intelligence-product-be-used-to-answer-analytic-questions.html" target="_blank">Can a business intelligence product be used to answer analytic questions?</a>"  raised some valid questions on the complexity, the business users face while analyzing the business data. It did generate quite of few <a href="http://sqlblog.com/blogs/mosha/archive/2008/06/10/follow-up-on-nextanalytics-challenge.aspx" target="_blank">good responses</a> on how MDX can provide the similar solution. I think both the approaches address different level of needs and both can co-exist.</p> <p>I just started to look at their <a href="http://developer.nextanalytics.com/demo/OpenChooseProject.aspx" target="_blank">online demo</a> site. Being in CPM (Corporate Performance Management) industry for about eight years, I can understand the difficulty the functional users face while venturing outside the realm of predefined reports and designing on their own. </p> <p>I plan to write a multi-part articles on how NextAnalytics solutions can be replicated using MDX. I  would be using "Adventure Works DW" OLAP database to replicate the similar business case.</p> <p>I saw a very interesting case in NextAnalytics where the result grid cells distinct values can be swapped with either rows or columns as labels. The row or column members appear in against the swapped cell's. This is a very important and useful feature for performing the basket analysis or outliers in a report.</p> <p>The goal is not to prove who is superior or complex to use. Just the sheer pleasure of validating that MDX is equally capable of fulfilling similar requirement.</p> <p><strong>Let me illustrate as what is happening in NextAnalytics' "Swap Cells with Row Labels" Feature:</strong></p> <p>1. This is the start point. The sales data is presented in the cross tab format. (a limited portion of data is displayed, hence in screens, they might not match)</p> <p><a href="http://lh5.ggpht.com/ajit555/SHjIzHU02gI/AAAAAAAABFc/XxAxsBbaJzc/s1600-h/image%5B13%5D.png"><img style="border-top-width: 0px; border-left-width: 0px; border-bottom-width: 0px; border-right-width: 0px" height="214" alt="image" src="http://lh5.ggpht.com/ajit555/SHjI0bX7t9I/AAAAAAAABFg/81EzRXiYNAQ/image_thumb%5B9%5D.png?imgmax=800" width="680" border="0" /></a> </p> <p>2. Now the variation of Sales data for each day and sales person (column-wise) is calculated and displayed. I guess, the formula would be somewhat at the below line:</p> <p>Variation = (Sales Amount - Average) / (Standard Deviation for the day)</p> <p><a href="http://lh6.ggpht.com/ajit555/SHjI12Rei7I/AAAAAAAABFk/8RZpP1lVSv4/s1600-h/image%5B9%5D.png"><img style="border-top-width: 0px; border-left-width: 0px; border-bottom-width: 0px; border-right-width: 0px" height="210" alt="image" src="http://lh3.ggpht.com/ajit555/SHjI36QPmcI/AAAAAAAABFo/LzS_9zqaxVk/image_thumb%5B7%5D.png?imgmax=800" width="678" border="0" /></a> </p> <p>3. Using "Swap cells to Row Labels", the unique values of variation is shifted to rows and the row members are loaded in the corresponding standard deviation cells.</p> <p><a href="http://lh4.ggpht.com/ajit555/SHjI577M7KI/AAAAAAAABFs/42ueINaiKFA/s1600-h/image%5B17%5D.png"><img style="border-top-width: 0px; border-left-width: 0px; border-bottom-width: 0px; border-right-width: 0px" height="747" alt="image" src="http://lh6.ggpht.com/ajit555/SHjI70uqFnI/AAAAAAAABFw/3gkfj1xGjeA/image_thumb%5B11%5D.png?imgmax=800" width="676" border="0" /></a> </p> <p>I would say that the above feature is simply awesome. I can visually say who are the "Outstanding" sales reps (Standard Deviation of more than 3) and who are the laggards (in above screen, standard deviation of -1) and whether they are  consistent in their performance.</p> <p><strong>Challenge to replicate the same requirement using MDX:</strong></p> <p>1. Lets get some sample cross tab data</p> <p></p> <p>WITH <br />SET Emp AS <br />{ <br />[Employee].[Employee].&[290] <br />,[Employee].[Employee].&[289] <br />,[Employee].[Employee].&[284] <br />,[Employee].[Employee].&[291] <br />,[Employee].[Employee].&[283] <br />,[Employee].[Employee].&[288] <br />,[Employee].[Employee].&[282] <br />,[Employee].[Employee].&[296] <br />,[Employee].[Employee].&[281] <br />,[Employee].[Employee].&[286] <br />,[Employee].[Employee].&[295] <br />,[Employee].[Employee].&[292] <br />,[Employee].[Employee].&[287] <br />,[Employee].[Employee].&[272] <br />,[Employee].[Employee].&[294] <br />,[Employee].[Employee].&[293] <br />,[Employee].[Employee].&[285] <br />} <br />SELECT <br />NON EMPTY <br />(EXISTING <br />{ <br />[Date].[Date].&[915] <br />,[Date].[Date].&[946] <br />,[Date].[Date].&[975] <br />,[Date].[Date].&[1006] <br />,[Date].[Date].&[1036] <br />,[Date].[Date].&[1067] <br />} <br />* <br />[Reseller Sales Amount] <br />) ON COLUMNS <br />,NonEmpty(emp) ON ROWS <br />FROM [Adventure Works] <br />WHERE <br />[Date].[Calendar Year].&[2004] </p> <p></p> <p><a href="http://lh3.ggpht.com/ajit555/SHjI9RnpMyI/AAAAAAAABF0/sYTWlxMNV2I/s1600-h/image%5B21%5D.png"><img style="border-top-width: 0px; border-left-width: 0px; border-bottom-width: 0px; border-right-width: 0px" height="415" alt="image" src="http://lh5.ggpht.com/ajit555/SHjI_na6vPI/AAAAAAAABF4/KjOaZcw8ys0/image_thumb%5B13%5D.png?imgmax=800" width="1109" border="0" /></a> </p> <p>2. Now, lets calculate the variation. I would convert the variation amounts to the range basket so that we would have few but distinct variation baskets in the cell.</p> <p>WITH <br />SET Emp AS <br />{ <br />[Employee].[Employee].&[290] <br />,[Employee].[Employee].&[289] <br />,[Employee].[Employee].&[284] <br />,[Employee].[Employee].&[291] <br />,[Employee].[Employee].&[283] <br />,[Employee].[Employee].&[288] <br />,[Employee].[Employee].&[282] <br />,[Employee].[Employee].&[296] <br />,[Employee].[Employee].&[281] <br />,[Employee].[Employee].&[286] <br />,[Employee].[Employee].&[295] <br />,[Employee].[Employee].&[292] <br />,[Employee].[Employee].&[287] <br />,[Employee].[Employee].&[272] <br />,[Employee].[Employee].&[294] <br />,[Employee].[Employee].&[293] <br />,[Employee].[Employee].&[285] <br />} <br />MEMBER sales_avg AS <br />Avg <br />( <br />[Date].[Date].CurrentMember * [Emp] <br />,[Reseller Sales Amount] <br />) <br />MEMBER sales_StDev AS <br />StDev <br />( <br />[Date].[Date].CurrentMember * [Emp] <br />,[Reseller Sales Amount] <br />) <br />MEMBER Sales_Variation AS <br />([Reseller Sales Amount] - sales_avg) / sales_StDev <br />,format_string = "currency" <br />MEMBER Sales_Variation_Basket AS <br />CASE <br />WHEN <br />Sales_Variation > 3 <br />THEN <br />3 <br />WHEN <br />Sales_Variation > 2 <br />THEN <br />2 <br />WHEN <br />Sales_Variation > 1 <br />THEN <br />1 <br />WHEN <br />Sales_Variation > 0 <br />THEN 0 <br />WHEN <br />Sales_Variation > -1 <br />THEN -1 <br />WHEN <br />Sales_Variation > -2 <br />THEN -2 <br />WHEN <br />Sales_Variation > -3 <br />THEN -3 <br />END <br />SELECT <br />NonEmpty <br />( <br />(EXISTING <br />{ <br />[Date].[Date].&[915] <br />,[Date].[Date].&[946] <br />,[Date].[Date].&[975] <br />,[Date].[Date].&[1006] <br />,[Date].[Date].&[1036] <br />,[Date].[Date].&[1067] <br />} <br />* <br />Sales_Variation_Basket <br />) <br />,NonEmpty(emp) <br />) ON COLUMNS <br />,NonEmpty(emp) ON ROWS <br />FROM [Adventure Works] <br />WHERE <br />[Date].[Calendar Year].&[2004] </p> <p><a href="http://lh3.ggpht.com/ajit555/SHjJA9dHldI/AAAAAAAABF8/z5LsSaEndDg/s1600-h/image%5B33%5D.png"><img style="border-top-width: 0px; border-left-width: 0px; border-bottom-width: 0px; border-right-width: 0px" height="413" alt="image" src="http://lh5.ggpht.com/ajit555/SHjJCYSnlyI/AAAAAAAABGA/OTJXIngPebA/image_thumb%5B19%5D.png?imgmax=800" width="1150" border="0" /></a> </p> <p>3. Now, we need to do "Cells to Row Labels" transformation on the above MDX. What i mean is to retain the dats on the column, put 3,2,1,0,-1,-2,-3 on rows and fill in the corresponding employees in the cells.</p> <p>The result should be somewhat as below grid ( I have filled couple of cells manually for illustration).</p> <p><a href="http://lh5.ggpht.com/ajit555/SHjJD3K51DI/AAAAAAAABGE/EIGT-5_PkXQ/s1600-h/image%5B37%5D.png"><img style="border-top-width: 0px; border-left-width: 0px; border-bottom-width: 0px; border-right-width: 0px" height="536" alt="image" src="http://lh5.ggpht.com/ajit555/SHjJFCjIlyI/AAAAAAAABGI/eUMdJIUlxZc/image_thumb%5B21%5D.png?imgmax=800" width="651" border="0" /></a> </p> <p>The MDX in item #1 and item#2 is for illustration of concept and not necessary the accuracy of calculation. The trick needed is to use the item#2 MDX and generate the last item#3 output.</p> <p></p> <p></p> <p>Is anybody up for the challenge to write the third part of the MDX query? </p> <p>Mosha provided the first cut of the query as below:</p> <p>WITH <br />  SET Emp AS <br />    { <br />      [Employee].[Employee].&[290] <br />     ,[Employee].[Employee].&[289] <br />     ,[Employee].[Employee].&[284] <br />     ,[Employee].[Employee].&[291] <br />     ,[Employee].[Employee].&[283] <br />     ,[Employee].[Employee].&[288] <br />     ,[Employee].[Employee].&[282] <br />     ,[Employee].[Employee].&[296] <br />     ,[Employee].[Employee].&[281] <br />     ,[Employee].[Employee].&[286] <br />     ,[Employee].[Employee].&[295] <br />     ,[Employee].[Employee].&[292] <br />     ,[Employee].[Employee].&[287] <br />     ,[Employee].[Employee].&[272] <br />     ,[Employee].[Employee].&[294] <br />     ,[Employee].[Employee].&[293] <br />     ,[Employee].[Employee].&[285] <br />    } <br />  MEMBER sales_avg AS <br />    Avg <br />    ( <br />      [Date].[Date].CurrentMember * [Emp] <br />     ,[Reseller Sales Amount] <br />    ) <br />  MEMBER sales_StDev AS <br />    StDev <br />    ( <br />      [Date].[Date].CurrentMember * [Emp] <br />     ,[Reseller Sales Amount] <br />    ) <br />  MEMBER Sales_Variation AS <br />    ([Reseller Sales Amount] - sales_avg) / sales_StDev <br />   ,format_string = "currency" <br />  MEMBER Sales_Variation_Basket AS <br />    CASE <br />      WHEN <br />        Sales_Variation > 3 <br />      THEN 3 <br />      WHEN <br />        Sales_Variation > 2 <br />      THEN 2 <br />      WHEN <br />        Sales_Variation > 1 <br />      THEN 1 <br />      WHEN <br />        Sales_Variation > 0 <br />      THEN 0 <br />      WHEN <br />        Sales_Variation > -1 <br />      THEN <br />        -1 <br />      WHEN <br />        Sales_Variation > -2 <br />      THEN <br />        -2 <br />      WHEN <br />        Sales_Variation > -3 <br />      THEN <br />        -3 <br />    END <br />  MEMBER Measures.[-2] AS <br />    Generate <br />    ( <br />      Filter <br />      ( <br />        Emp <br />       , <br />        Sales_Variation_Basket = -2 <br />      ) <br />     , <br />      Employee.Employee.CurrentMember.Name + "," <br />    ) <br />  MEMBER Measures.[-1] AS <br />    Generate <br />    ( <br />      Filter <br />      ( <br />        Emp <br />       , <br />        Sales_Variation_Basket = -1 <br />      ) <br />     , <br />      Employee.Employee.CurrentMember.Name + "," <br />    ) <br />  MEMBER Measures.[0] AS <br />    Generate <br />    ( <br />      Filter <br />      ( <br />        Emp <br />       , <br />        Sales_Variation_Basket = 0 <br />      ) <br />     , <br />      Employee.Employee.CurrentMember.Name + "," <br />    ) <br />  MEMBER Measures.[1] AS <br />    Generate <br />    ( <br />      Filter <br />      ( <br />        Emp <br />       , <br />        Sales_Variation_Basket = 1 <br />      ) <br />     , <br />      Employee.Employee.CurrentMember.Name + "," <br />    ) <br />  MEMBER Measures.[2] AS <br />    Generate <br />    ( <br />      Filter <br />      ( <br />        Emp <br />       , <br />        Sales_Variation_Basket = 2 <br />      ) <br />     , <br />      Employee.Employee.CurrentMember.Name + "," <br />    ) <br />SELECT <br />  { <br />    [Date].[Date].&[915] <br />   ,[Date].[Date].&[946] <br />   ,[Date].[Date].&[975] <br />   ,[Date].[Date].&[1006] <br />   ,[Date].[Date].&[1036] <br />   ,[Date].[Date].&[1067] <br />  } ON COLUMNS <br /> ,{ <br />    Measures.[-2] <br />   ,Measures.[-1] <br />   ,Measures.[0] <br />   ,Measures.[1] <br />   ,Measures.[2] <br />  } ON ROWS <br />FROM [Adventure Works]</p> <p>And the output is as desired (partial screenshot):</p> <p><a href="http://lh5.ggpht.com/ajit555/SHrKN1ZvACI/AAAAAAAABGM/2Wb_PJBx2Ug/s1600-h/image%5B4%5D.png"><img style="border-right: 0px; border-top: 0px; border-left: 0px; border-bottom: 0px" height="144" alt="image" src="http://lh3.ggpht.com/ajit555/SHrKP7NZn7I/AAAAAAAABGQ/WLiFBNcRDGk/image_thumb%5B1%5D.png?imgmax=800" width="1394" border="0" /></a> </p> <p>While the above meets the requirement, there is one significant limitation that we need to overcome.</p> <p>In the solution, the distinct values of "Sales_Variation_Basket" are manually defined as Measures.[3].....Measures.[-3].</p> <p> <br />We need to automate it. I mean, using MDX, to read the set of "Sales_Variation_Basket" values, read the distinct values out of it and sort it, and then put the distinct values of "Sales_Variation_Basket" on rows and then put the employees in the result cells.</p> <p>I am sure we would have the solution soon.</p> Ajit Singhhttp://www.blogger.com/profile/17889678799981918534noreply@blogger.com2tag:blogger.com,1999:blog-32998500.post-7322902090601868462008-06-28T09:53:00.001+05:302008-09-26T14:19:34.303+05:30<h5><a href="http://asmdx.blogspot.com/2008/05/code-utility-code-for-converting.html"></a></h5> <p><strong>Design</strong></p> <p><a href="http://asmdx.blogspot.com/2008/05/cube-structure-optimization-for-mdx.html">Cube structure optimization for MDX query performance in Analysis Services 2005 SP2: Tips for Parent Child Hierarchies usage</a></p> <p><a href="http://asmdx.blogspot.com/2008/07/fact-table-design-for-state-workflow.html" target="_blank">Fact table design for “State Workflow Analysis”: Analysis Services Dimensional modeling</a></p> <p><a href="http://asmdx.blogspot.com/2008/05/handling-inter-dimensional-members.html">Handling inter-dimensional members dependency and reducing cube sparsity using reference dimensions in Analysis Services 2005 SP2 : Cube design tip</a></p> <p><a href="http://asmdx.blogspot.com/2008/06/handling-intra-dimensional-members.html">Identifying intra-dimensional members relationship and reducing cube sparsity in Analysis Services 2005 SP2 : Cube design tip</a></p> <p><a href="http://asmdx.blogspot.com/2008/04/leaf-my-quest-to-understand-it.html">Leaves() : An example to understand it for both regular hierarchies as well as parent child hierarchies</a></p> <p><a href="http://asmdx.blogspot.com/2008/04/aggregation-design-useful-tips.html">Aggregation design: useful tips</a></p> <p><a href="http://asmdx.blogspot.com/2008/04/level-based-attribute-hierarchy-mdx.html">Level based attribute hierarchy: MDX query performance woes in SQL Server 2005 SP2: Is it fixed in post SP2 hotfix?</a></p> <p><a href="http://asmdx.blogspot.com/2008/04/parent-child-hierarchy-to-level-base.html">Parent child hierarchy to level base hierarchy conversion: hiding placeholder dimension members in client application</a></p> <p><strong>Trouble / Troubleshooting</strong></p> <p><a href="http://asmdx.blogspot.com/2008/06/aggregate-sum-functions-using.html">Aggregate(), Sum() functions using calculated members does not work in Analysis Services 2005 SP2 (9.00.3042.00 version) but works in Analysis Services 2000 SP4</a></p> <p><a href="http://asmdx.blogspot.com/2008/03/custom-member-formula-issues-in.html">Analysis Services 2005 migration tool: Custom member formula issues in migrated database</a></p> <p><a href="http://asmdx.blogspot.com/2008/03/cube-partitions-fact-table-not-listing.html">Cube Partitions: Fact table not listing in Business Intelligence Development Studio in partition wizard</a></p> <p><a href="http://asmdx.blogspot.com/2008/03/many-to-many-relationship-does-not.html">Analysis Services 2005: Many-to-Many relationship does not support unary operators with parent-child dimension</a></p> <p><strong>MDX</strong></p> <p><a href="http://asmdx.blogspot.com/2008/07/nextanalytics-and-mdx-part-1-swap-cells.html">NextAnalytics and MDX : Part 1 - Swap Cells with Row Labels</a></p> <p><a href="http://asmdx.blogspot.com/2008/04/selecting-dimension-default-member.html">Selecting dimension's default member based on a member property</a></p> <p><a href="http://asmdx.blogspot.com/2007/12/sorting-members-on-member-codes-member.html">Sorting members on member codes / member properties</a></p> <p><a href="http://asmdx.blogspot.com/2008/04/time-dimension-how-to-set-default.html">Time Dimension: How to set Default Member to Current Month</a></p> <p><a href="http://asmdx.blogspot.com/2008/04/setting-default-member-in-dimension-x.html">Setting dynamic default member in dimension X based on the current member of dimensions Y</a></p> <p><strong>ADOMD.NET</strong></p> <p><a href="http://asmdx.blogspot.com/2008/05/code-utility-code-for-converting.html">Code : utility code for converting cellset to a data table</a></p> <p><strong>Others</strong></p> <p><a href="http://asmdx.blogspot.com/2008/07/google-specialized-search-for-analysis.html" target="_blank">Google specialized search for Analysis Services and MDX web resources integrated in my blog</a></p> <p><a href="http://asmdx.blogspot.com/2008/06/art-of-reading-mdx-articles.html">Art of reading MDX articles</a></p> <p><a href="http://asmdx.blogspot.com/2008/04/mdx-expression-builder-need-for-tool.html">MDX Expression Builder : Need for a tool making it easier for functional users to write MDX expressions, queries.</a></p> Ajit Singhhttp://www.blogger.com/profile/17889678799981918534noreply@blogger.com2tag:blogger.com,1999:blog-32998500.post-59778702251841712342008-06-25T16:39:00.001+05:302008-06-25T16:39:31.971+05:30Art of reading MDX articles<p>I learnt MDX much more by reading articles by knowledgeable people in this industry, Mosha for example. These extremely well written articles are sprayed with numerous MDX queries each explaining the nuances of MDX. Earlier, I used to open two windows, one for reading article and other for executing MDX queries. Many a times, continuous copy and paste distracted the subject context.</p> <p>Of late, I have devised a new technique. I just copy and paste the complete article in SS Management Studio's MDX query editor and just read in that. Whenever, I need to execute the query, I just select the query text portion and execute it. Now, I don't have to switch between windows or lost my focus, of course at the cost of text formatting. I wish I could do the same thing in Mosha's MDX Studio but it does not support text wrapping. (I have devised another technique where I copy and paste the article in my Notepad++ text editor and then re-wrap the text to the desired width and then paste). </p> <p><a href="http://lh4.ggpht.com/ajit555/SGInZGbtPrI/AAAAAAAABE8/XRVly0T1WgM/s1600-h/image%5B3%5D.png"><img style="border-right: 0px; border-top: 0px; border-left: 0px; border-bottom: 0px" height="743" alt="image" src="http://lh5.ggpht.com/ajit555/SGInauWU8pI/AAAAAAAABFA/MZc9am5sq8I/image_thumb%5B1%5D.png?imgmax=800" width="1091" border="0" /></a> </p> <p>It works great for me to read text based articles with lots of MDX in it without getting distracted.</p> Ajit Singhhttp://www.blogger.com/profile/17889678799981918534noreply@blogger.com0tag:blogger.com,1999:blog-32998500.post-63649006049765662122008-06-16T14:25:00.001+05:302008-06-16T14:25:47.778+05:30Aggregate(), Sum() functions using calculated members does not work in Analysis Services 2005 SP2 (9.00.3042.00 version) but works in Analysis Services 2000 SP4<p>When any aggregate function, viz, Aggregate(), Sum() is used on the set of calculated members in Analysis Services 2005 SP2 (9.00.3042.00 version), they are not computing the value and return nulls instead where as the same functions work correctly in Analysis Services 2000 SP4. This seems to be a bug and hope that soon some hotfix would be released.</p> <p>The workaround for this is not to use these functions for calculated members and rather calculate the values using the mathematical operator. e.s. instead of </p> <p>'aggregate( [Organization].[Organizations].[Calc5], <br />                      [Organization].[Organizations].[Calc3], <br />                      [Organization].[Organizations].[Calc4], <br />                      [Organization].[Organizations].[Calc6], <br />                      [Organization].[Organizations].[Calc7] <br />} )'</p> <p>define the formula as </p> '                    [Organization].[Organizations].[Calc5] + <br />                      [Organization].[Organizations].[Calc3] + <br />                      [Organization].[Organizations].[Calc4] + <br />                      [Organization].[Organizations].[Calc6] + <br />                      [Organization].[Organizations].[Calc7] <br />' <p> </p> <p>The issue is detailed in below section:</p> <p>The below Adventureworks query gets the result properly for '[Organization].[Organizations].[SelectMembers] ' member: </p> <p><font face="Courier New">with member [Organization].[Organizations].[SelectMembers] as 'aggregate( <br />     {    [Organization].[Organizations].&[5], <br />                      [Organization].[Organizations].&[3], <br />                      [Organization].[Organizations].&[4], <br />                      [Organization].[Organizations].&[6], <br />                      [Organization].[Organizations].&[7]} <br />)' <br />SELECT          { <br />                      [Date].[Fiscal Year].&[2002], <br />                      [Date].[Fiscal Year].&[2003], <br />                      [Date].[Fiscal Year].&[2004] <br />                } <br />                * <br />                {    [Organization].[Organizations].&[5], <br />                      [Organization].[Organizations].&[3], <br />                      [Organization].[Organizations].&[4], <br />                      [Organization].[Organizations].&[6], <br />                      [Organization].[Organizations].&[7], <br />                     [Organization].[Organizations].[SelectMembers]} <br />ON ROWS , </font></p> <p><font face="Courier New">           {[Account].[Accounts].&[101],[Account].[Accounts].&[52] <br />           } <br />ON COLUMNS <br />FROM [Finance] </font></p> <p>However, when the organizations members are converted as calculated members and referred in the aggregate() formula, the values returned are "Null" for '[Organization].[Organizations].[SelectMembers]' member. </p> <p><font face="Courier New">with <br />member [Organization].[Organizations].[Calc5] as '[Organization].[Organizations].&[5]' <br />member [Organization].[Organizations].[Calc3] as '[Organization].[Organizations].&[3]' <br />member [Organization].[Organizations].[Calc4] as '[Organization].[Organizations].&[4]' <br />member [Organization].[Organizations].[Calc6] as '[Organization].[Organizations].&[6]' <br />member [Organization].[Organizations].[Calc7] as '[Organization].[Organizations].&[7]' <br />member [Organization].[Organizations].[SelectMembers] as 'aggregate( <br />{                     [Organization].[Organizations].[Calc5], <br />                      [Organization].[Organizations].[Calc3], <br />                      [Organization].[Organizations].[Calc4], <br />                      [Organization].[Organizations].[Calc6], <br />                      [Organization].[Organizations].[Calc7] <br />} )' </font></p> <p><font face="Courier New">SELECT          { <br />                      [Date].[Fiscal Year].&[2002], <br />                      [Date].[Fiscal Year].&[2003], <br />                      [Date].[Fiscal Year].&[2004] <br />                } <br />                * <br />                {    [Organization].[Organizations].[Calc5], <br />                      [Organization].[Organizations].[Calc3], <br />                      [Organization].[Organizations].[Calc4], <br />                      [Organization].[Organizations].[Calc6], <br />                      [Organization].[Organizations].[Calc7], <br />                      [Organization].[Organizations].[SelectMembers] <br />} <br />ON ROWS , </font></p> <p><font face="Courier New">           {[Account].[Accounts].&[101],[Account].[Accounts].&[52] <br />           } <br />ON COLUMNS <br />FROM [Finance] </font></p> <p>However, the similar query for FoodMart2000 returns the same result for both the approach for '[Store].[SelectMembers] ' member: </p> <p><font face="Courier New">with member [Store].[SelectMembers] as 'Aggregate( <br />{ <br />[Store].[All Stores].[USA].[CA], <br />[Store].[All Stores].[USA].[OR], <br />[Store].[All Stores].[USA].[WA]} <br />)' <br />select crossjoin({[Time].[1997],[Time].[1998]} , <br />{ <br />[Store].[All Stores].[USA].[CA], <br />[Store].[All Stores].[USA].[OR], <br />[Store].[All Stores].[USA].[WA], <br /> [Store].[SelectMembers] } <br />) <br />on rows, <br />{[Account].[All Account].[Net Income].[Net Sales].[Gross Sales],[Account].[All Account].[Net Income].[Net Sales].[Cost of Goods Sold]} on columns <br />from budget </font></p> <p>The query returns the same result for '[Store].[SelectMembers]' member even though it is referring the calculated members: </p> <p><font face="Courier New">with <br />member [Store].[All Stores].[USA].[CalcCA] as '[Store].[All Stores].[USA].[CA]' <br />member [Store].[All Stores].[USA].[CalcOR] as '[Store].[All Stores].[USA].[OR]' <br />member [Store].[All Stores].[USA].[CalcWA] as '[Store].[All Stores].[USA].[WA]' <br />member [Store].[SelectMembers] as 'Aggregate( <br />{ <br />[Store].[All Stores].[USA].[CalcCA], <br />[Store].[All Stores].[USA].[CalcOR], <br />[Store].[All Stores].[USA].[CalcWA]} <br />)' <br />select crossjoin({[Time].[1997],[Time].[1998]} , <br />{ <br />[Store].[All Stores].[USA].[CalcCA], <br />[Store].[All Stores].[USA].[CalcOR], <br />[Store].[All Stores].[USA].[CalcWA], <br /> [Store].[SelectMembers] } <br />) <br />on rows, <br />{[Account].[All Account].[Net Income].[Net Sales].[Gross Sales],[Account].[All Account].[Net Income].[Net Sales].[Cost of Goods Sold]} on columns <br />from budget </font></p> Ajit Singhhttp://www.blogger.com/profile/17889678799981918534noreply@blogger.com0tag:blogger.com,1999:blog-32998500.post-35069074422760185642008-06-06T20:32:00.001+05:302008-06-09T12:50:07.192+05:30Identifying intra-dimensional members relationship and reducing cube sparsity in Analysis Services 2005 SP2 : Cube design tip<p>In my previous blog, I discussed the approach for "<a href="http://asmdx.blogspot.com/2008/05/handling-inter-dimensional-members.html">Handling inter-dimensional members dependency and reducing cube sparsity using reference dimensions in Analysis Services 2005 SP2 : Cube design tip"</a>. [<strong><em>Watch for the difference, Inter and Intra</em></strong>] The approach discuss the situation where members from one dimension are valid for a few members on other dimension. The typical example would be that for Sales Accounts, normally,only the sales department would be applicable and Sales data wont exists for say, "vehicle expenses department".</p> <p>But what if there exists a relation between the members on a given "single" dimension? During the cube modeling phase, many a times designers ignore this aspect and later, many of the needed business reports can not be generated by the MDX reporting clients and complex MDX queries need to be hand coded.</p> <p>Let us explain this with a simple business example of a consulting company. The typical chart of account of our fictitious consulting company is:</p> <p><a href="http://lh3.ggpht.com/ajit555/SElRPU5sv8I/AAAAAAAABDs/XgXYq_q16O4/s1600-h/image%5B6%5D.png"><img style="border-top-width: 0px; border-left-width: 0px; border-bottom-width: 0px; border-right-width: 0px" height="219" alt="image" src="http://lh5.ggpht.com/ajit555/SElRQxvcJRI/AAAAAAAABDw/Kqsk6kAFHMg/image_thumb%5B2%5D.png?imgmax=800" width="278" border="0" /></a> </p> <p>The data for two years are:</p> <p><a href="http://lh5.ggpht.com/ajit555/SElRRgHVuVI/AAAAAAAABD0/_i7deO0Ssgg/s1600-h/image%5B7%5D.png"><img style="border-top-width: 0px; border-left-width: 0px; border-bottom-width: 0px; border-right-width: 0px" height="223" alt="image" src="http://lh5.ggpht.com/ajit555/SElRUX0GmqI/AAAAAAAABD4/k51nj_rQka4/image_thumb%5B3%5D.png?imgmax=800" width="523" border="0" /></a>  </p> <p>The resultant fact table structure is:</p> <p><a href="http://lh4.ggpht.com/ajit555/SElRVEnixBI/AAAAAAAABD8/MFipEWt25bA/s1600-h/image%5B11%5D.png"><img style="border-top-width: 0px; border-left-width: 0px; border-bottom-width: 0px; border-right-width: 0px" height="416" alt="image" src="http://lh4.ggpht.com/ajit555/SElRWjy86uI/AAAAAAAABEA/Cghv_NTpruE/image_thumb%5B5%5D.png?imgmax=800" width="578" border="0" /></a> </p> <p>The desired report that needs to be generated is:</p> <p><a href="http://lh6.ggpht.com/ajit555/SElRXs6KAfI/AAAAAAAABEE/GfXBu8-spUA/s1600-h/image%5B15%5D.png"><img style="border-top-width: 0px; border-left-width: 0px; border-bottom-width: 0px; border-right-width: 0px" height="190" alt="image" src="http://lh5.ggpht.com/ajit555/SElRZ7dcxvI/AAAAAAAABEI/KTKC0dBCX6Q/image_thumb%5B7%5D.png?imgmax=800" width="674" border="0" /></a> </p> <p>However, what seems like a simple report can not be generated by the client applications since they don't understand the relationship that exists between the dimension members. The resultant MDX query would look something like below:</p> <table cellspacing="0" cellpadding="2" width="1048" border="1"><tbody> <tr> <td valign="top" width="1046">with member Measures.[USD/HR] as ' <br />IIF(Account.currentmember is [Sales - Product 1],[Sales - Product 1]/[Revenue Hours - Product 1], <br />    IIF(Account.currentmember is [Sales - Product 2],[Sales - Product 2]/[Revenue Hours - Product 2], <br />        IIF(Account.currentmember is [Sales - Product 3],[Sales - Product 3]/[Revenue Hours - Product 3], <br />            IIF(Account.currentmember is [Sales - Product 4],[Sales - Product 1]/[Revenue Hours - Product 4],0))))' <br />select { <br />[Sales - Product 1], <br />[Sales - Product 2], <br />[Sales - Product 3], <br />[Sales - Product 4], <br />} on rows, <br />Crossjoin({[2007],[2008]},{Measures.[Amount],Measures.[USD/HR]} on columns <br />from cube</td> </tr> </tbody></table> <p>Not a very elegant MDX, huh.</p> <p>However, we know that the following relation exist among the dimension members:</p> <p><a href="http://lh6.ggpht.com/ajit555/SElRbbx34GI/AAAAAAAABEM/kIewp0fLQ70/s1600-h/image%5B19%5D.png"><img style="border-top-width: 0px; border-left-width: 0px; border-bottom-width: 0px; border-right-width: 0px" height="143" alt="image" src="http://lh5.ggpht.com/ajit555/SElRcZq2uhI/AAAAAAAABEQ/AtLlvwNWvoA/image_thumb%5B9%5D.png?imgmax=800" width="539" border="0" /></a> </p> <p>The key is to relate these dimension members in the cube so that in the MDX queries, we can take advantage of these relations and make the queries faster as well as the simpler. </p> <p>In the below paragraph, we would use the following terminology:</p> <p>Attribute & parent dimension members: The dimension members which provide the details to the parent dimension members to which it is related. E.g., in above example, the "Revenue Hours - Product X" dimension members, Attribute dimension members, provide the number of hours for "Sales - Product X" dimension members, parent dimension members. </p> <p>There are two possible approaches to define the relation between these dimension members:</p> <p><strong><em>Approach 1: Define the attribute dimension member as the "<u>measure</u>" of the parent dimension members</em></strong></p> <p><strong><em>Approach 2 :  Define the attribute dimension member as the "<u>Attribute</u>" of the parent dimension members</em></strong></p> <p><strong><em>Approach 1: Define the attribute dimension member as the "<u>measure</u>" of the parent dimension members</em></strong></p> <p>If we know these relations beforehand, then we can modify the dimensional members and load the value of related dimension member as additional "Measures" in the fact table at the time of data load. </p> <p>We can load the same data as below:</p> <p><a href="http://lh3.ggpht.com/ajit555/SElReCFFRzI/AAAAAAAABEU/Sb1h6peEdds/s1600-h/image%5B23%5D.png"><img style="border-top-width: 0px; border-left-width: 0px; border-bottom-width: 0px; border-right-width: 0px" height="248" alt="image" src="http://lh4.ggpht.com/ajit555/SElRexBvX9I/AAAAAAAABEY/ef8MLVrhccI/image_thumb%5B11%5D.png?imgmax=800" width="751" border="0" /></a> </p> <p>The above structure lends itself more for analysis purpose and the end users can now make the desired report using MDX client tools. The representative MDX query would look something like below:</p> <table cellspacing="0" cellpadding="2" width="798" border="1"><tbody> <tr> <td valign="top" width="796"> <p>with member Measures.[USD/HR] as 'Measures.[Amount]/Measures.[Hours]' <br />select { <br />[Sales - Product 1], <br />[Sales - Product 2], <br />[Sales - Product 3], <br />[Sales - Product 4], <br />} on rows, <br />Crossjoin({[2007],[2008]},{Measures.[Amount],Measures.[USD/HR]} on columns <br />from cube</p> </td> </tr> </tbody></table> <p> </p> <p><strong><em>Approach 2 :  Define the attribute dimension member as the "<u>Attribute</u>" of the parent dimension members</em></strong></p> <p>This approach can be taken if the earlier approach of "converting attribute dimension members as measures" is not feasible. Approach #2 is much less efficient approach than the Approach #1, but it is still elegant than the approach of lots of nested IIF statements.</p> <p>In this approach, on account dimension, we create a property called "Hours" and for parent dimension members, we can store the reference of attribute dimension members.</p> <p>E.g., suppose, in this case, we store the Member Keys as the Hours attribute for "Sales - Product X" dimension members. Now, the MDX query to generate the same report would be something like below:</p> <p></p> <table cellspacing="0" cellpadding="2" width="798" border="1"><tbody> <tr> <td valign="top" width="796"> <p>with member Measures.[USD/HR] as 'Measures.[Amount]/(StrToMember("[Account].&[" + [Account].Currentmember.properties('Hours') + "]"))' <br />select { <br />[Sales - Product 1], <br />[Sales - Product 2], <br />[Sales - Product 3], <br />[Sales - Product 4], <br />} on rows, <br />Crossjoin({[2007],[2008]},{Measures.[Amount],Measures.[USD/HR]} on columns <br />from cube</p> </td> </tr> </tbody></table> Ajit Singhhttp://www.blogger.com/profile/17889678799981918534noreply@blogger.com6tag:blogger.com,1999:blog-32998500.post-32501975363631975282008-05-25T23:22:00.001+05:302008-05-25T23:22:50.876+05:30Code : utility code for converting cellset to a data table<p> </p> <p>Private Function getDataTable(ByVal cs As AdomdClient.CellSet) As DataTable <br />        'design the datatable <br />        Dim dt As New DataTable <br />        Dim dc As DataColumn <br />        Dim dr As DataRow </p> <p>        'add the columns <br />        dt.Columns.Add(New DataColumn("Description")) 'first column <br />        'get the other columns from axis <br />        Dim p As AdomdClient.Position <br />        Dim name As String <br />        Dim m As AdomdClient.Member <br />        For Each p In cs.Axes(0).Positions <br />            dc = New DataColumn <br />            name = "" <br />            For Each m In p.Members <br />                name = name + m.Caption + " " <br />            Next <br />            dc.ColumnName = name <br />            dt.Columns.Add(dc) <br />        Next </p> <p>        'add each row, row label first, then data cells <br />        Dim y As Integer <br />        Dim py As AdomdClient.Position <br />        y = 0 <br />        For Each py In cs.Axes(1).Positions <br />            dr = dt.NewRow 'create new row </p> <p>            ' Do the row label <br />            name = "" <br />            For Each m In py.Members <br />                name = name & m.Caption & "" </p> <p>            Next <br />            dr(0) = name 'first cell in the row </p> <p>            ' Data cells <br />            Dim x As Integer <br />            For x = 0 To cs.Axes(0).Positions.Count - 1 <br />                dr(x + 1) = cs(x, y).FormattedValue 'other cells in the row <br />            Next </p> <p>            dt.Rows.Add(dr) 'add the row <br />            y = y + 1 <br />        Next </p> <p>        Return dt <br />    End Function</p> Ajit Singhhttp://www.blogger.com/profile/17889678799981918534noreply@blogger.com4tag:blogger.com,1999:blog-32998500.post-24575047003392963492008-05-16T12:47:00.001+05:302008-05-16T12:48:42.847+05:30Handling inter-dimensional members dependency and reducing cube sparsity using reference dimensions in Analysis Services 2005 SP2 : Cube design tip<p>Traditionally the cubes structure is designed based on either star or snowflake schema. The dimensions in the cube is totally independent of each other and in the results can be obtained for any dimension members to any dimension. This is a classic illusion of an ideal world, i.e. even though the intention is good but the performance suffers since the cube is very sparse.</p> <p>High cube sparsity adversely affect the MDX query performance. A very sparse cube can take longer to resolve calculated members and calculated cells, and MDX functions involving empty cells, such as <b>CoalesceEmpty</b> or <b>NonEmptyCrossjoin</b>, take slightly longer to process because of the large volume of empty cells that must be considered by such functions. </p> <p>For example, the following diagram indicates three dimension hierarchies used to construct a cube for tracking orders. </p> <p><a href="http://lh6.ggpht.com/ajit555/SC00_d93S1I/AAAAAAAABC8/Y6gnNcfY9yk/s1600-h/StarSchema%5B3%5D.jpg"><img style="border-right: 0px; border-top: 0px; border-left: 0px; border-bottom: 0px" height="944" alt="StarSchema" src="http://lh3.ggpht.com/ajit555/SC01At93S2I/AAAAAAAABDE/56XCciF79XE/StarSchema_thumb%5B1%5D.jpg?imgmax=800" width="830" border="0" /></a> </p> <p>Each customer, product & time dimensions have 5 members each. In the above design, between customer, product and time, theoretically 125 cells are possible ( 5 customer X 5 product X 5 time members). </p> <p>However, in reality, may times, the existence of a real measure of a given dimensions is dependent on other dimensions. E.g. Sales rep can handle a few given territories, the given customer is handled by a few sales rep and the customer purchase a given set of products, some products are not even sold in other territories. So, up front in the design, we are sure that there would be a lot of dimension combinations for which real data would not exist.</p> <p>Suppose customer and product dimensions have dependency on each other and the following following 11 valid combinations exists:</p> <p><a href="http://lh3.ggpht.com/ajit555/SC01Bt93S3I/AAAAAAAABDM/se1FKccdIcU/s1600-h/IntermediateDimension%5B4%5D.jpg"><img style="border-right: 0px; border-top: 0px; border-left: 0px; border-bottom: 0px" height="859" alt="IntermediateDimension" src="http://lh3.ggpht.com/ajit555/SC01Ct93S4I/AAAAAAAABDU/uHA8xK4Dw34/IntermediateDimension_thumb%5B2%5D.jpg?imgmax=800" width="444" align="left" border="0" /></a> </p> <p>And suppose, these 11 combinations have data existing for all the 5 time periods, then the total number of valid combinations are 55 (11 customer-product X 5 time members).</p> <p>It means that the actual density of the cube is only 44 % (55 real measures / 125 theoretical measures).</p> <p>This cube is a theoretical example; in reality, many dimensions are much more sparse than indicated in this example. E.g. we have assumed that 11 customer product combinations have purchase history for all the 5 time periods, but if they have on an average 2 purchases out of five, then the actual number of cells would drop to 22 (11 customer-product X 2 time period) and the sparsity would drop further to 17% (22 real measures / 125 theoretical measures). </p> <p>By themselves, the dimensions do not appear overly sparse; each dimension has members with relevant fact table data. If these dimensions are used together in a cube, however, the sparsity of the cube increases exponentially with each dimension, because the introduction of each dimension exponentially increases the number of cells within the cube. The above diagram, the shaded cells on customer - product face indicate the cells that actually contain data.</p> <p>Dimensions with unrelated data can also greatly increase cube sparsity, especially if the dimensions are included as part of an associative relationship. For example, a business case is designed to compare the sales from retail customers with the sales from vendors, so a cube with three dimensions representing sales, customers, and vendors is created. The Customers dimension organizes retail customers by location, the Vendors dimension organizes vendors by sales region and vendor type, and the Orders dimension organizes order quantities by date. Both the Customers and Vendors dimension share elements with the Orders dimension, but not with each other. Because customers and vendors do not directly relate, from the viewpoint of the underlying data source, the result is a very sparse cube. In this case, it is easier to construct two cubes, one for vendors and one for customers, which share the Orders dimension. </p> <p>Conversely, if beforehand, we know that dimensions are dependent on each other, then we can reduce the cube sparsity at the cube design level itself. Analysis Services 2000 did not support "Reference Dimensions" but it is something we can utilize in Analysis Services 2005.</p> <p>We can design an intermediate dimension which contains the valid combinations of customer and product members at leaf level. This intermediate dimension joins with the fact table. The Customer and product dimensions are joined to fact table as a "Reference Dimension" utilizing the intermediate dimension.</p> <p>The below diagram illustrates the conversion of above star schema to a "Reference-Intermediate" dimension structure. The shaded region is the valid combination of Customer-Product intermediate dimension and the time dimension, i.e. 55 cells and the sparsity is zero. </p> <p><a href="http://lh3.ggpht.com/ajit555/SC01Dt93S5I/AAAAAAAABDc/opIAnW1v2rM/s1600-h/ReferenceDimensionCube%5B3%5D.jpg"><img style="border-right: 0px; border-top: 0px; border-left: 0px; border-bottom: 0px" height="824" alt="ReferenceDimensionCube" src="http://lh3.ggpht.com/ajit555/SC01Ft93S6I/AAAAAAAABDk/F2ZPocXPvUo/ReferenceDimensionCube_thumb%5B1%5D.jpg?imgmax=800" width="1243" border="0" /></a></p> <p> </p> <p>The intermediate dimension can be made invisible so that to a external OLAP cube consumer, there is no difference in the cube browsing experience even though the underlying cube structure has been changed drastically. The MDX queries too would run faster since the cube sparsity is reduced drastically by use of intermediate dimension.</p> Ajit Singhhttp://www.blogger.com/profile/17889678799981918534noreply@blogger.com7tag:blogger.com,1999:blog-32998500.post-76339882356937409842008-05-15T10:49:00.001+05:302008-06-24T20:08:06.734+05:30Cube structure optimization for MDX query performance in Analysis Services 2005 SP2: Tips for Parent Child Hierarchies usage<p><em>Note: The below findings have been found on the PC hierarchies. The same may or may not be applicable to level based hierarchies since I have not tested them. I need to convert the same PC hierarchies to level based ones and then retest to find out if the similar issues exists with them too.</em></p> <p>The bottom line of this blog, Analysis Servies 2005 does not handle rollup operators and custom members on Parent Child hierarchies properly leading to significant MDX query performance bottlenecks.</p> <p>There are ample amount of material available dealing with cube optimization to improve MDX query performance. The problem is that most of the materials almost give equal weightings to all the tips and we are not able to extract which tip would provide the biggest bang related to query performance. </p> <p>If you deal with parent child hierarchies extensively in your cube design, many of the query optimization tips don't even apply since parent child dimensions are specific case.</p> <p>Here, based on my personal experience, I have listed down the factors in the decreasing order:</p> <p>1. <strong>Parent child dimension, rollup operators and custom members on dimensions are the most lethal combinations to kill the query performance (or kill the cube itself, no MDX queries run).</strong></p> <p>If there are multiple Parent Child hierarchies on which for each hierarchy, we enable rollup operator and custom members, then even for very small cube (5-6 PC hierarchy few hundred members and fact table of 50k rows), the cube just hangs and MDX query don't run. I have validated this in my previous <a href="http://asmdx.blogspot.com/2008/04/parent-child-attribute-performance-woes.html" target="_blank">blog</a>. So, disable rollup operator and custom members as much as possible and find alternate ways of dealing with it.  </p> <p>By observing the profiler, millions of lines are generated with frequent occurrence of "get data from calculation cache" or query dimension and I suspect, there is some issue in calculation algorithm in Analysis Services where Parent Child hierarchies with rollup operator and custom members are involved. When I disable all of the rollup operator and custom members, the queries run in a couple of seconds. Even with I retain the custom members and rollup operator on only one PC hierarchy and disable on rest of the of the PC hierarchies, the query performance is good.</p> <p>2. <strong>In MDX query, provide the default members of the parent child hierarchies which are not used in the query.</strong></p> <p>Per best practices, we would enable "isAggregatable" property = Yes for all the attributes. In the MDX query, if these hierarchies are not needed in the query result, we would not provide any values for other PC hierarchies since they are supposed to provide values for "All" members.</p> <p>However, I found that if the other PC hierarchies contain lots of calculated members and if we don't provide the default members for these unused PC hierarchis in the MDX query, the Analysis Services seems to calculate the values of the other custom members even if they are not needed and the query runs longer. Providing the default value for other PC hierarchies significantly hastens the query time.</p> <p>E.g., an MDX query, which ran just under 4 seconds by providing all the default members on "where" clause, ran eternally which I needed to cancel the query after 5 minutes and by that time, it had taken all of available RAM (2gb) and the CPU processing when I did not specify the default members for other PC hierarchies.</p> <p>3. <strong>Rollup operators seems to degrade query performance much more than the custom members on PC hierarchies.</strong></p> <p>It was strange to find about it. I always though rollup operators must be faster than the custom members for the similar calculations.</p> <p>In an cube, I disabled rollup operators and enabled custom members on PC hierarchies. The MDX query ran in under 4 seconds. Now, I disabled custom members and enabled rollup operators on the same PC hierarchies. Now the same MDX query, even after 30 min did not run and I needed to cancel the query. This I experienced multiple times even after I started the Analysis Services.</p> <p>4. <strong>Conversion of PC hierarchy to level based hierarchy does not necessarily mean better query performance.</strong></p> <p>It was again a surprise. Many would suggest that level based hierarchies are better than PC hierarchies, but that advice needs to be taken with a pinch of salt. Level based hierarchies are certainly better than PC hierarchies. However, certain hierarchies are better represented in a PC fashion only e.g. a big employee organization hierarchy or financial account containing Income Statement or Balance sheet etc. The members in these hierarchies do not reside in any specific levels. In fact, the hierarchy continuously undergoes rapid changes whenever newer members are attached or existing members are deleted or moved under some other parents. When this happens, the number of levels changes dramatically and many member shift from one level to another. These hierarchies have many null members in many levels since the members at the leaf level may end in any level not necessarily at the lowest level.</p> <p>Hierarchies like above when converted to level based one and then used in the cube affect the query time drastically. Under PC hierarchy, the MDX which took about 9 seconds to execute, after converting to level based hierarchy took about 1 min 33 seconds to execute! This effect was validated multiple times.</p> <p>PC hierarchies where levels can be identified easily or all the leaf level members have proper parents in the above levels would improve query performance when converted to level based ones.</p> <p><strong>5. Maintain "Calculated Members" and delete unwanted ones</strong></p> <p>It has been observed that mere presence of calculated members on dimensions affect the query performance even if they are not referred in the MDX query context. We tend to create a lot of junk calculated members are they just hang out in hierarchy without any use. These kind of members needs to be regularly removed from hierarchy.   </p> <p><strong>6. Watch out for slow running MDX functions while creating calculated members</strong></p> <p>Functions like StrToMember are inherently slow. In words of Mosha, ".....I really dislike StrToMember function for many reasons. For the havoc it wrecks in the query optimizer, for the unpredictable caching guarantees, for the very dynamic binding by means of reparsing its input..."</p> <p>Slow running functions like this drastically amplify their inefficiencies if they get evaluated in the context of each and every cell of resultant cellset, e.g. if calculated measure use these slow running functions, they would get evaluated for each and every cell if calculated measure is on page level.</p> <p>Chris Webb on one  of his <a title="Parameterising Calculated Measure Definitions" href="http://cwebbbi.spaces.live.com/blog/cns!7B84B0F2C239489A!1602.entry" target="_blank">blog</a> explains how to handle StrToMember properly and avoid its evaluation for multiple cells.</p> <p><strong>7.  Take advantage of "Disable Prefetch Facts=True; cache ratio=1" connection properties</strong></p> <p>Parent child hierarchies don't store the aggregation for intermediate levels.  When we execute the MDX query involving the parent child dimension, the analysis services' formula engine uses a number of heuristics when formulating subcube requests from the storage engine to attempt to optimize overall performance. These include fetching data in one subcube request into cache for use by subsequent queries and the ordering of subcube requests for an individual query to optimize the use of cache (by retrieving as much information as necessary to answer a query by using as few noncached subcube query requests as possible).  This may result in excessive unnecessary subcube calculations. Per my experience, when the parent child dimensions are involved, using this connection string property has provided minor to many-many multiple time query time benefits.</p> <p><strong>8. Use effective partitioning strategy</strong></p> <p>There are many resources available to understand it.</p> <p><strong>9. Pre-calculate the formulas outside cube to avoid calculations inside the cube</strong></p> <p>The key requirement is to avoid simple calculations inside cube as much as possible so that cube can be freed up to do complex calculations.</p> <p>Below are the two prime candidates which can be calculated outside the cube:</p> <p>1. In the fact table itself as measure, e.g. using calculated column.</p> <p>2. The calculated members along the dimension, if their values are resolved at leaf level and then they need to rollup like any other member, then it is prudent to calculate their formula values outside and load it the fact table.</p> <p><strong></strong></p> <p>(<em><strong>The article is work in progress & would be regularly updated. If you have any tips, please share with me to post it here</strong></em>.) </p> Ajit Singhhttp://www.blogger.com/profile/17889678799981918534noreply@blogger.com7tag:blogger.com,1999:blog-32998500.post-85655862331410927902008-04-30T12:16:00.001+05:302008-04-30T13:47:26.020+05:30Level based attribute hierarchy: MDX query performance woes in SQL Server 2005 SP2: Is it fixed in post SP2 hotfix?<p> </p> <p>The other day, I was working on a level based dimension hierarchy and the fact table with rather very few records.</p> <p>I had applied all the dimensional modeling recommendations such as, </p> <p>1. for each attribute, </p> <ul> <li>disable unary operator and customrullupformula if not needed. </li> <li>remove unwanted attributes </li> <li>isaggregatable property = true </li> <li>attributehierarchyenabled = false if not needed </li> <li>attributehierarchyvisible = false if you need to access the attribute but not visible in dimension browser </li> <li>define attribute relationship </li> </ul> <p>2. In cube, all dimension to cube relationship is "Regular"</p> <p>3. In cube partition, updated all the statistics (which is evident in above screenshot)</p> <p>Testing process:</p> <p>1. I took a set of leaf fact table level data set of five rows. In the profiler , the "Query Dimension" went on and on </p> <p><a href="http://lh6.ggpht.com/ajit555/SBgVtgF3cfI/AAAAAAAAAno/Xmb-NOB5mH4/s1600-h/image%5B10%5D.png"><img style="border-top-width: 0px; border-left-width: 0px; border-bottom-width: 0px; border-right-width: 0px" height="1162" alt="image" src="http://lh4.ggpht.com/ajit555/SBgVvAF3cgI/AAAAAAAAAnw/0LyDrNru-Yw/image_thumb%5B4%5D.png?imgmax=800" width="899" border="0" /></a> </p> <p>Finally the query aborted and a "fatal error" message came:</p> <p>Analysis Services had taken entire available RAM for this small query:</p> <p><a href="http://lh5.ggpht.com/ajit555/SBgVzQF3cjI/AAAAAAAAAoI/I3uV69vck5Q/s1600-h/image%5B18%5D.png"><img style="border-top-width: 0px; border-left-width: 0px; border-bottom-width: 0px; border-right-width: 0px" height="454" alt="image" src="http://lh3.ggpht.com/ajit555/SBgV0wF3ckI/AAAAAAAAAoQ/SfWBMiTTOEI/image_thumb%5B8%5D.png?imgmax=800" width="793" border="0" /></a> </p> <p>It seems there is a post SP2 hotfix which sounds like to adddress the above problem. A similar issue was reported in <a href="http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=2161837&SiteID=1" target="_blank">Analyiss Services 2005 forum</a> from where I got the below information.</p> <p><a href="http://support.microsoft.com/kb/938077/">938077</a> (http://support.microsoft.com/kb/938077/) <br />FIX: The client application stops responding, and the Msmdsrv.exe process uses all the available memory after you perform the filtering operation and the browsing operation against an instance of SQL Server 2005 Analysis Services</p> <p>In fact, there are a bunch of problem addressed in <a href="http://support.microsoft.com/kb/936305/LN/" target="_blank">post SP2 hotfix</a>.</p> <p>Since, these hotfixes are not fully regression tested by Microsoft as yet, it is available through request only <a href="https://support.microsoft.com/contactus2/emailcontact.aspx?scid=sw;[LN];1414&from=KBHotfix&WS=hotfix" target="_blank">here</a>.</p> <p>Once, I apply the hotfix, would check if the above get resoled. I would update this blog then. </p> Ajit Singhhttp://www.blogger.com/profile/17889678799981918534noreply@blogger.com2tag:blogger.com,1999:blog-32998500.post-16762753886232147482008-04-25T11:30:00.001+05:302008-04-25T11:38:25.624+05:30MDX Expression Builder : Need for a tool making it easier for functional users to write MDX expressions, queries.<p>MDX is great for business problem analysis. Moving averages, % of parent, YoY growth etc are business needs and surely MDX can handle it. There is only one hitch though. Most of the business users can not write it. There are so many tools including SS Management studio which allows me to write complex SQL queries many a times just using my mouse, but hardly anything to write MDX expressions as flexibly. </p> <p>To make a start, at least a tool can be provided with the below functionality:</p> <p>1. The interface would display the dimension hierarchies and and an area to create MDX expression. The area would have two panes, one which shows the MDX expression in <a href="http://asmdx.blogspot.com/2008/04/mdx-parser-and-generator-similar-to.html" target="_blank">AST</a> hierarchy and other pane shows the real MDX script text.</p> <p> <br />2. The user can write MDX expression directly in the MDX script text pane and wheh toggles to AST pane, the expression would be shows as a expression tree.</p> <p> <br />3. The user can drag and drop the functions, operators or dimension members onto the AST pane and when switch it back to MDX script text pane, the MDX query text would be regenerated.</p> <p> <br />4. Of course, some wizards, templates can be pre-stored for common business expressions which gives the functional user some starting point to use it as is or further customize it. <br /></p> <p>In MDX studio by Mosha, I can paste a MDX query ant it generates a Syntax tree which i find extremely useful to analyse complext MDX statements. The problem is it is only one way, i.e. MDX query text to tree and not the other way around. Long back, I had seen an evaluation version of a tool named as Hungry Dog (if i remember correctly) which used to do it and I can not find them anymore.</p> <p>E.g. in below screen, the left side provides a tree representation of a MDX expression and right a text representation. The tree one is easier to understand. Over time, the it could be made much more user friendly.</p> <p><a href="http://lh3.ggpht.com/ajit555/SBF1LBAx27I/AAAAAAAAAmY/G0e-J8UnQm4/s1600-h/image%5B3%5D.png"><img style="border-right: 0px; border-top: 0px; border-left: 0px; border-bottom: 0px" height="722" alt="image" src="http://lh3.ggpht.com/ajit555/SBF1NBAx28I/AAAAAAAAAmg/XoxuAV723jc/image_thumb%5B1%5D.png?imgmax=800" width="1362" border="0" /></a> </p> <p>Unless we have something like above, we can not service those super intelligent functional users who understand business need in depth but can not analyze it themselves fully due to lack of a good tool to define MDX expressions.</p> <p>If anybody knows a good tool which works on Analysis Services, kindly let me know. </p> Ajit Singhhttp://www.blogger.com/profile/17889678799981918534noreply@blogger.com2tag:blogger.com,1999:blog-32998500.post-56650043031956320692008-04-24T10:53:00.001+05:302008-04-24T10:59:59.909+05:30MDX parser and generator similar to Abstract Syntax Tree (AST)<p>I was looking out for a utility / tool / class which can do following in Analysis Services 2005:</p> <p>1. Parse & validate an MDX expression/query and present it in a object hierarchy<br>2. Vice-versa, create an MDX query / expression from object hierarchy</p> <p>The above functionaity can be used in an application where the complexities of the MDX needs to be kept away from the users and it would be helpful in programatically managing the mdx queries.</p> <blockquote> <p>Per Analysis Services book by Melomed, Gorbach et all, Analysis Services itself handles it where it parses an MDX request. First, it analyzes the MDX statement, finds syntax errors, and produces a tree-based data structure called an abstract syntax tree (AST). An AST has operators, such as MDX functions as inner nodes, and operands, such as function arguments as leaf nodes. In the second parsing phase, Analysis Services traverses an AST, resolves the names of the objects referenced in the query, and validates the signatures of the functions. This phase produces an expression tree. Each node on the expression tree is an object that knows how to calculate itself. It also has a hook by which it can plug in to the calculation execution plan.</p></blockquote> <p>For example, if you send the below MDX query to Analysis Services, it will produce the AST shown<a name="ch28fig02"></a>: <h6>A Simple MDX Query</h6> <p><font face="Courier New">SELECT<br>Order(<br> Filter(<br> [Customer].[Customers].[Country].members,<br> [Measures].[Unit Sales].Value >1000<br> ),<br> [Measures].[Unit Sales],<br> BDESC<br> )<br> ON COLUMNS<br> FROM [Warehouse and Sales]</font><pre></pre><br /><p><br /><p><font face="Courier New"></font></p><b></b><b></b><b></b><b></b><b>In the first parsing phase, Analysis Services produces an AST.</b></p><br /><p><a href="http://lh6.ggpht.com/ajit555/SBAYyBAx2VI/AAAAAAAAAhk/HgmvZDPr9AE/s1600-h/clip_image002%5B4%5D.jpg"><img style="border-right: 0px; border-top: 0px; border-left: 0px; border-bottom: 0px" height="478" alt="clip_image002" src="http://lh4.ggpht.com/ajit555/SBAYzhAx2WI/AAAAAAAAAhs/2k_vWpuE7HQ/clip_image002_thumb%5B1%5D.jpg?imgmax=800" width="433" border="0"></a><br /><p><a name="ch28list01"></a>In the second parsing phase, Analysis Services traverses an AST, resolves the names of the objects referenced in the query, and validates the signatures of the functions. This phase produces an expression tree. Each node on the expression tree is an object that knows how to calculate itself. It also has a hook by which it can plug in to the calculation execution plan. For the <a name="iddle1391"></a><a name="iddle2192"></a><a name="iddle2501"></a><a name="iddle3279"></a><a name="iddle3447"></a><a name="iddle3534"></a><a name="iddle3771"></a>sample query, Analysis Services generates the expression tree as below:<br /><h6><a name="ch28fig03"></a>During the second parsing phase, Analysis Services performs the semantic analysis and produces an expression tree.</h6><br /><p><a href="http://lh4.ggpht.com/ajit555/SBAY0hAx2XI/AAAAAAAAAh0/sFEHl_R6qtE/s1600-h/clip_image004%5B4%5D.jpg"><img style="border-right: 0px; border-top: 0px; border-left: 0px; border-bottom: 0px" height="343" alt="clip_image004" src="http://lh5.ggpht.com/ajit555/SBAY1xAx2YI/AAAAAAAAAh8/l0e5N61nCH8/clip_image004_thumb%5B1%5D.jpg?imgmax=800" width="504" border="0"></a><br /><p>After creating the expression tree, Analysis Services is ready to move to the query resolution phase.<br /><p>I was wondering if the above functionalities can be exposed to outside application where they can convert an MDX to abstract syntax tree and vice versa.<br>Mondrian seems to have such class hierarchies exposed which seems to be programmatically exploitable. (<a href="http://mondrian.pentaho.org/api/mondrian/mdx/MdxVisitor.html">http://mondrian.pentaho.org/api/mondrian/mdx/MdxVisitor.html</a>)<br><br /><p>If anyone knows approach for SQL 2005 or earlier, please let me know. If I come to know something, sure would post it here.<br /> Ajit Singhhttp://www.blogger.com/profile/17889678799981918534noreply@blogger.com1tag:blogger.com,1999:blog-32998500.post-46278172810097798642008-04-23T19:56:00.001+05:302008-06-17T14:15:33.885+05:30Leaves() : An example to understand it for both regular hierarchies as well as parent child hierarchies<table cellspacing="0" cellpadding="2" width="1054" border="1"><tbody> <tr> <td valign="top" width="1052"><strong>Leaves() Gotcha in Analysis Services 2005 SP2: Be aware of the number of tuples that the MDX script is going to affect. <br /></strong> <br />The other day, I was trying to define a calculation for a member at the leaf level of all the dimension. After defining the formula and deploying the MDX script, the results were not displayed in the query in SS Management Studio 2005. <br /> <br />When I ran the same query in MDX Sample Application (Analysis Services 2000 utility to run MDX queries), it displayed the error as below: <br /> <br /><a href="http://lh5.ggpht.com/ajit555/SFd5pzBe-tI/AAAAAAAABEc/MjZ9jX_oDAE/s1600-h/image%5B4%5D.png"><img style="border-right: 0px; border-top: 0px; border-left: 0px; border-bottom: 0px" height="179" alt="image" src="http://lh4.ggpht.com/ajit555/SFd5qxxpVFI/AAAAAAAABEg/6ckAtE5pkmI/image_thumb%5B1%5D.png?imgmax=800" width="663" border="0" /></a> <br /> <br />It seems that the MDX script that I had setup was affecting more than 4.3 billion tuples in the set. The cube I used had 8 dimension with varying levels. I tried to search if this is documented, but could not find it. <br /></td> </tr> </tbody></table> <p>Leaves() is an interesting MDX functions introduced in SQL 2005. It is intended to be used only inside MDX Scripts to define the scope of the calculations (i.e. in the left-hand side of the assignments). </p> <p><strong><em>There are many situations where in we wish to perform the calculation at the leaf level of dimension and then aggregate or roll it up like any other member.</em></strong></p> <p>Mosha, in his <a href="http://www.sqljunkies.com/WebLog/mosha/archive/2005/02/13/7784.aspx" target="_blank">blog</a> explained the use of leaf level for achieving the above objective.</p> <p>This article explains how we can implement Leaves() in both parent child as well as regular hierarchies using AdventureWorks cube.</p> <p><strong><u>Leaves() function use with parent child hierarchy:</u></strong></p> <p>My objective was to create a measure 'BudAmount' which is equal to 'Amount' at the leaf level and then it needs to be aggregated like 'Amount' measure across any hierarchy in AdventureWorks' "Financial Reporting" measuregroup . </p> <p>I read a post in <a href="http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=3227958&SiteID=1&mode=1" target="_blank">MSDN forum</a> which was similar to my need. In the post, it was needed to define a calculated measure at leaf level and then aggregate the calculated measure like any normal measure.</p> <p>Chris Webb, in the answer to the post, suggested that:</p> <p><em><strong>"...It's certainly a fairly common problem, but Mosha has indeed blogged about the best way to handle this:</strong></em></p> <p><a href="http://www.sqljunkies.com/WebLog/mosha/archive/2005/02/13/7784.aspx"><em><strong>http://www.sqljunkies.com/WebLog/mosha/archive/2005/02/13/7784.aspx</strong></em></a></p> <p><em><strong>Create a <font color="#ff0000">new real (ie not calculated) measure</font> in your cube from a column that is always null in your fact table, and then assign the calculated value to the leaf level of all dimensions. Something like:</strong></em></p> <p><em><strong>(Measures.CM, Leaves()) = iif(Measures.M>0.9, 1, null);"</strong></em></p> <p>Based on this newfound knowledge, I started to setup my BudAmount measure.</p> <p>Below are the steps:</p> <p>1. In the DSV, changed the FactFiance table to a named query and added a column named  'BudAmount' with value as 0 (zero).</p> <p><a href="http://lh6.ggpht.com/ajit555/SBBEEBAx2tI/AAAAAAAAAkk/XAT8WjMUEHM/s1600-h/image%5B4%5D.png"><img style="border-top-width: 0px; border-left-width: 0px; border-bottom-width: 0px; border-right-width: 0px" height="1014" alt="image" src="http://lh5.ggpht.com/ajit555/SBBEGxAx2uI/AAAAAAAAAks/fMTaXpDmbSQ/image_thumb%5B1%5D.png?imgmax=800" width="983" border="0" /></a> </p> <p>2. In cube, added the measure 'BudAmount' alongside of 'Amount'</p> <p><a href="http://lh3.ggpht.com/ajit555/SBBEIRAx2vI/AAAAAAAAAk0/K2Ac98hIzG8/s1600-h/image%5B8%5D.png"><img style="border-top-width: 0px; border-left-width: 0px; border-bottom-width: 0px; border-right-width: 0px" height="479" alt="image" src="http://lh5.ggpht.com/ajit555/SBBEJxAx2wI/AAAAAAAAAlA/FGen747jIUE/image_thumb%5B3%5D.png?imgmax=800" width="829" border="0" /></a> </p> <p>3. In mdx script, added the below script: </p> <p>SCOPE (Leaves(),[Measures].[BudAmount]);  <br />  this = [Measures].[Amount]; <br />END SCOPE;</p> <p><a href="http://lh6.ggpht.com/ajit555/SBBELBAx2xI/AAAAAAAAAlI/eq_8KY2NgQU/s1600-h/image%5B12%5D.png"><img style="border-top-width: 0px; border-left-width: 0px; border-bottom-width: 0px; border-right-width: 0px" height="253" alt="image" src="http://lh3.ggpht.com/ajit555/SBBEMRAx2yI/AAAAAAAAAlQ/3t2ePY_i_2k/image_thumb%5B5%5D.png?imgmax=800" width="865" border="0" /></a> </p> <p>4. One important thing which I had missed earlier and the Leandro Tubia pointed it out at the <a href="http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=3233084&SiteID=1" target="_blank">MSDN Analysis Services forum</a> to setup the AggregatFunction of BudAmount to be same as Amount i.e. "ByAccount". Otherwise, for balance sheet account types, the amounts would be summed up rather than the last month values for higher levels of Time Dimension.</p> <p><a href="http://lh5.ggpht.com/ajit555/SBFuPhAx23I/AAAAAAAAAl4/ZpfnPBCP2xc/s1600-h/image%5B3%5D.png"><img style="border-top-width: 0px; border-left-width: 0px; border-bottom-width: 0px; border-right-width: 0px" height="664" alt="image" src="http://lh5.ggpht.com/ajit555/SBFuRhAx24I/AAAAAAAAAmA/PUOI2vRsJdY/image_thumb%5B1%5D.png?imgmax=800" width="772" border="0" /></a> </p> <p>5. Full processed the cube (I am yet to understand the appropriate processing to be used, so full processing may not be needed,  but just to be on safer side, using full processing)</p> <p>6.  Now, when you browse the cube, the value of BudAmount and Amount is same as desired.</p> <p><a href="http://lh5.ggpht.com/ajit555/SBFuThAx25I/AAAAAAAAAmI/ema9PjJvzpg/s1600-h/image%5B7%5D.png"><img style="border-top-width: 0px; border-left-width: 0px; border-bottom-width: 0px; border-right-width: 0px" height="676" alt="image" src="http://lh6.ggpht.com/ajit555/SBFuUxAx26I/AAAAAAAAAmQ/SHl-ii1TD2Q/image_thumb%5B3%5D.png?imgmax=800" width="828" border="0" /></a> </p> <p><strong><u><strong><u>Leaves() function use with</u></strong> with regular hierarchies:</u></strong></p> <p>In Adventureworks, I created a measure (per Chris Webb, it needs to be a real measure, not a calculated one) called 'SalesAmount1' on adventureworks cube which provides the same data as 'SalesAmount' measure. I mean if I query both the measure anyway, both should behave in same way and return the same amount at any level of other dimension. </p> <p>Below are the steps:</p> <p>1. In the DSV, in 'FactSalesSummary' query, add additional column as 'SalesAmount1' with value as 0.</p> <p><a href="http://lh4.ggpht.com/ajit555/SA9WJxAx2JI/AAAAAAAAAf8/spaav_g--No/s1600-h/image%5B23%5D.png"><img style="border-top-width: 0px; border-left-width: 0px; border-bottom-width: 0px; border-right-width: 0px" height="692" alt="image" src="http://lh3.ggpht.com/ajit555/SA9WLhAx2KI/AAAAAAAAAgE/fK1rbHE_fYk/image_thumb%5B11%5D.png?imgmax=800" width="835" border="0" /></a> </p> <p>2. Under 'Sales Summary' measure group, add the 'SalesAmount1' measure. (look for any white space in the name).</p> <p><a href="http://lh3.ggpht.com/ajit555/SA9WMhAx2LI/AAAAAAAAAgM/4zdH2TmXItE/s1600-h/image%5B31%5D.png"><img style="border-top-width: 0px; border-left-width: 0px; border-bottom-width: 0px; border-right-width: 0px" height="656" alt="image" src="http://lh3.ggpht.com/ajit555/SA9WNhAx2MI/AAAAAAAAAgU/eH2PfZP0biQ/image_thumb%5B15%5D.png?imgmax=800" width="481" border="0" /></a> </p> <p>3. In <strong><em><u>each</u></em></strong> of Sales Summary partition, add the new 'SalesAmount1' column:</p> <p><a href="http://lh4.ggpht.com/ajit555/SA9WOxAx2NI/AAAAAAAAAhc/6B3Kgt7Sw10/s1600-h/image%5B47%5D.png"><img style="border-top-width: 0px; border-left-width: 0px; border-bottom-width: 0px; border-right-width: 0px" height="657" alt="image" src="http://lh5.ggpht.com/ajit555/SA9WQBAx2OI/AAAAAAAAAhg/7ZKX9L78ykY/image_thumb%5B25%5D.png?imgmax=800" width="1134" border="0" /></a> </p> <p>4. In the MDX script, define the calculation:</p> <p>SCOPE (Leaves(),[MEASURES].[SalesAmount1]); <br />  this = [Measures].[Sales Amount]; <br />END SCOPE; </p> <p><a href="http://lh5.ggpht.com/ajit555/SA9WRBAx2PI/AAAAAAAAAgs/qQWggeBdMUg/s1600-h/image%5B35%5D.png"><img style="border-top-width: 0px; border-left-width: 0px; border-bottom-width: 0px; border-right-width: 0px" height="595" alt="image" src="http://lh6.ggpht.com/ajit555/SA9WSRAx2QI/AAAAAAAAAg0/5PkdgciVvRA/image_thumb%5B17%5D.png?imgmax=800" width="932" border="0" /></a> </p> <p>5. Check if the SalesAmount1 measure is same as [Sales Amount]. Yes, it is same.</p> SELECT <br />    NON EMPTY <br />    [Product].[Product Categories].[Category].MEMBERS <br />    ON ROWS , <br />    {   [Measures].[Sales Amount], [Measures].[SalesAmount1] <br />    } <br />    ON COLUMNS <br />    FROM [Adventure Works] <p><a href="http://lh3.ggpht.com/ajit555/SA9WThAx2RI/AAAAAAAAAg8/l3FunBvbfvo/s1600-h/image%5B39%5D.png"><img style="border-top-width: 0px; border-left-width: 0px; border-bottom-width: 0px; border-right-width: 0px" height="519" alt="image" src="http://lh3.ggpht.com/ajit555/SA9WUhAx2SI/AAAAAAAAAhE/2SUUQ8HhM_w/image_thumb%5B19%5D.png?imgmax=800" width="1006" border="0" /></a> </p> <p>6. Even if the cube is browsed in management studio, the value of both the measure is same. So, Leaves() is working in case of regular hierarchies.</p> <p><a href="http://lh6.ggpht.com/ajit555/SA9WWRAx2TI/AAAAAAAAAhM/TLYWSegXszA/s1600-h/image%5B43%5D.png"><img style="border-top-width: 0px; border-left-width: 0px; border-bottom-width: 0px; border-right-width: 0px" height="871" alt="image" src="http://lh4.ggpht.com/ajit555/SA9WXxAx2UI/AAAAAAAAAhU/xnagnmN6SyY/image_thumb%5B21%5D.png?imgmax=800" width="818" border="0" /></a></p> Ajit Singhhttp://www.blogger.com/profile/17889678799981918534noreply@blogger.com19tag:blogger.com,1999:blog-32998500.post-36036791574673319042008-04-22T12:54:00.001+05:302008-04-22T12:56:35.290+05:30Parent Child Attribute performance woes in SQL Server 2005 SP2: A case study<p><strong> Objective: </strong></p> <p>To understand why Parent Child attribute perform so bad vis-a-vis level based hierarchies. Also, the parent child attribute in SQL server 2005 SP2 performs much worse than SQL server 2000 SP4. The total number of fact table rows are very small (100 to be precise).</p> <p><strong>Note: the needed files to recreate the cubes and database are provided at the end of the blog.</strong></p> <p><strong>Test Approach: </strong></p> <p>Three OLAP databases were setup on the same relational database as below:</p> <p>1. AjitLevel - Level based dimension hierarchies in Analysis Services 2005 SP2 (AS2005SP2)</p> <p>2. AjitPC - Parent Child attribute hierarchy in AS2005SP2</p> <p>3. AjitPC2000 - Parent Child dimension in AS2000SP4</p> <p>The relational database was AjitDB in Sql Server 2005 SP2. The FACT table contained just 100 data rows.</p> <p>The MDX query which just queries the 11 children of a parent were run on all the three OLAP databases and the run time were noted. The results of all the 3 queries were same.</p> <p>The below MDX query was run:</p> <p><font face="Courier New">select descendants([Account].&[110]) on rows, <br />{[Measures].[MTD]} on columns <br />from repcube3</font></p> <p>Test Results:</p> <table cellspacing="0" cellpadding="2" width="400" border="1"><tbody> <tr> <td valign="top" width="133">OLAP database</td> <td valign="top" width="133">Query Runtime</td> <td valign="top" width="133">Trace Details</td> </tr> <tr> <td valign="top" width="133">AjitLevel </td> <td valign="top" width="133">Instantaneous </td> <td valign="top" width="133">37 rows</td> </tr> <tr> <td valign="top" width="133"><strong><font color="#ff0000">AjitPC </font></strong></td> <td valign="top" width="133"><strong><font color="#ff0000">15 minutes on server desktop, 40 minutes on my old homePC</font></strong></td> <td valign="top" width="133"><strong><font color="#ff0000">3.8 million rows. The size of trace file was 870 mb!</font></strong></td> </tr> <tr> <td valign="top" width="133">AjitPC2000 </td> <td valign="top" width="133">8 seconds on server desktop</td> <td valign="top" width="133">Trace not available for AS2000</td> </tr> </tbody></table> <p><strong>Screenshots:</strong></p> <p>MDX query result of Parent Child attribute dimension (note the time as 38 min 50 seconds)</p> <p><strong><a href="http://lh4.ggpht.com/ajit555/SA2SmRAx13I/AAAAAAAAAds/jpCJgIj0yX4/s1600-h/AjitPCResult%5B3%5D.jpg"><img style="border-right: 0px; border-top: 0px; border-left: 0px; border-bottom: 0px" height="784" alt="AjitPCResult" src="http://lh6.ggpht.com/ajit555/SA2SnxAx14I/AAAAAAAAAd0/_ntQj8j29MI/AjitPCResult_thumb%5B1%5D.jpg?imgmax=800" width="881" border="0" /></a></strong></p> <p>Profiler Trace on Parent Child cube: (3.8 million rows, 870 mb trace file size)</p> <p><a href="http://lh3.ggpht.com/ajit555/SA2SpBAx15I/AAAAAAAAAd8/d04EAD4dRa0/s1600-h/AjitPCTrace%5B3%5D.jpg"><img style="border-right: 0px; border-top: 0px; border-left: 0px; border-bottom: 0px" height="781" alt="AjitPCTrace" src="http://lh5.ggpht.com/ajit555/SA2SqhAx16I/AAAAAAAAAeE/nEOBcfdUTP8/AjitPCTrace_thumb%5B1%5D.jpg?imgmax=800" width="881" border="0" /></a> </p> <p>Level based cube: (3 seconds)</p> <p><a href="http://lh5.ggpht.com/ajit555/SA2SrhAx17I/AAAAAAAAAeM/hZadQEo2E2I/s1600-h/AjitLevelResult%5B3%5D.jpg"><img style="border-right: 0px; border-top: 0px; border-left: 0px; border-bottom: 0px" height="784" alt="AjitLevelResult" src="http://lh6.ggpht.com/ajit555/SA2SsxAx18I/AAAAAAAAAeU/coSPv6Uacbw/AjitLevelResult_thumb%5B1%5D.jpg?imgmax=800" width="881" border="0" /></a> </p> <p>Level based cube trace file: (37 rows)</p> <p><a href="http://lh3.ggpht.com/ajit555/SA2SuBAx19I/AAAAAAAAAec/xWtQpWLfjIg/s1600-h/AjitLevelTrace%5B3%5D.jpg"><img style="border-right: 0px; border-top: 0px; border-left: 0px; border-bottom: 0px" height="781" alt="AjitLevelTrace" src="http://lh5.ggpht.com/ajit555/SA2SvhAx1-I/AAAAAAAAAek/XrRfyVBqKaY/AjitLevelTrace_thumb%5B1%5D.jpg?imgmax=800" width="881" border="0" /></a> </p> <p>Parent child dimension cube in Analysis Services 2000: (8 seconds query time, same result)</p> <p><a href="http://lh5.ggpht.com/ajit555/SA2SwhAx1_I/AAAAAAAAAes/gkHClAZp7sg/s1600-h/AjitPCResultAS2000%5B3%5D.jpg"><img style="border-right: 0px; border-top: 0px; border-left: 0px; border-bottom: 0px" height="711" alt="AjitPCResultAS2000" src="http://lh5.ggpht.com/ajit555/SA2SxhAx2AI/AAAAAAAAAe0/KhXuovI1c7Y/AjitPCResultAS2000_thumb%5B1%5D.jpg?imgmax=800" width="770" border="0" /></a> </p> <p><strong>Files needed to recreate the cubes and database:</strong></p> <p><a href="http://cid-d78fe3e1cd22da3b.skydrive.live.com/self.aspx/Public/PC2LevelFiles/CreateTables.sql" target="_blank">CreateTables.sql</a> : Script to create the dimension and fact tables</p> <p><a href="http://cid-d78fe3e1cd22da3b.skydrive.live.com/self.aspx/Public/PC2LevelFiles/AjitDBData.rar" target="_blank">AjitDBData.rar</a> : Script to populate the data in dimension and fact tables</p> <p><a href="http://cid-d78fe3e1cd22da3b.skydrive.live.com/self.aspx/Public/PC2LevelFiles/AjitPC.xmla" target="_blank">AjitPC.xmla</a> : Script to create AjitPC cube </p> <p><a href="http://cid-d78fe3e1cd22da3b.skydrive.live.com/self.aspx/Public/PC2LevelFiles/AjitLevel.xmla" target="_blank">AjitLevel.xmla</a> : Script to create AjitLevel cube</p> <p><a href="http://cid-d78fe3e1cd22da3b.skydrive.live.com/self.aspx/Public/PC2LevelFiles/AjitPC2000.CAB" target="_blank">AjitPC2000.CAB</a> : Archive of AjitPC2000 cube to be restored in Analysis Services 2000</p> <p><a href="http://cid-d78fe3e1cd22da3b.skydrive.live.com/self.aspx/Public/PC2LevelFiles/TestQuery.mdx" target="_blank">TestQuery.mdx</a> : Simple MDX query used in testing</p> <p><a href="http://cid-d78fe3e1cd22da3b.skydrive.live.com/self.aspx/Public/PC2LevelFiles/AjitPCTrace.rar" target="_blank">AjitPCTrace.rar</a> : zipped trace file of AijtPC cube trace recorded via SQL Server 2005 profiler. Its 3 mb and upon unzipping becomes 870 mb.</p> <p><a href="http://cid-d78fe3e1cd22da3b.skydrive.live.com/self.aspx/Public/PC2LevelFiles/AjitLevelTrace.trc" target="_blank">AjitLevelTrace.trc</a> : trace file of AjitLevel cube query execution</p> Ajit Singhhttp://www.blogger.com/profile/17889678799981918534noreply@blogger.com7tag:blogger.com,1999:blog-32998500.post-65847945666031962032008-04-16T19:53:00.001+05:302008-04-16T19:53:50.930+05:30Member Properties: getting email address for each manager along with the sales amount<p>In the Adventure Works database I would like to return information about the set of Managers at Level 04 (one level of the Parent-Child hierarchy) plus the sum of the sales for that Manager and her reports. </p> <p>Along with the manager's name, I need to display the manager's email address too. </p> <p>with member [Measures].[Email] as '[Employee].[Employees].currentmember.properties("email address")' <br />SELECT <br />{[Measures].[Email] ,[Reseller Sales Amount]} on 0 , <br />NON EMPTY filter(([Employee].[Employees].[Employee Level 04].MEMBERS),[Reseller Sales Amount]>0) ON 1 <br />FROM [Adventure Works];</p> Ajit Singhhttp://www.blogger.com/profile/17889678799981918534noreply@blogger.com0tag:blogger.com,1999:blog-32998500.post-91925679116635522142008-04-16T16:33:00.001+05:302008-05-16T13:17:25.730+05:30Microsoft Certification : request for exclusive certification for Analysis Services<p>As an OLAP professional, I eat, sleep and drink "Analysis Services". A certification would help in stamping my expertise in this area (and may be enhance my salary too :) )</p> <p>Analysis Services is a subject area deep enough that does not leave anytime for me to practice other Microsoft BI tools such as Reporting Services, Data Mining, Integration services and so on.</p> <p>However, if I need to get certified in Analysis Services, there seems to be only one exam available below:</p> <p><a href="http://www.microsoft.com/learning/exams/70-445.mspx">Exam 70-445</a>: TS: Microsoft SQL Server 2005 Business Intelligence—Implementation and Maintenance</p> <p>The exam covers the following subject areas:</p> <ul> <li>Implementing and Maintaining Microsoft SQL Server 2005 Analysis Services (includes Data Mining too) </li> <li>Implementing and Maintaining Microsoft SQL Server 2005 Integration Services </li> <li>Implementing and Maintaining Microsoft SQL Server 2005 Reporting Services </li> </ul> <p>Can Microsoft come with an exam exclusively focusing on Analysis Services where they test the in-depth  knowledge of Analysis Services?</p> <p>Analysis Services is a pretty in-depth technology that needs the following expertise:</p> <ul> <li>Dimensional, cube, KPI, drill-through, Actions modeling </li> <li>Business problem solving using analysis services </li> <li>Underlying relational schema & data source view design </li> <li>Aggregation and partition design </li> <li>Write backs </li> <li>MDX calculations and queries </li> <li>Security </li> <li>Performance improvements </li> <li>.Net stored procedure extensions </li> <li>Administration </li> <li>Many more that I might have missed </li> </ul> Ajit Singhhttp://www.blogger.com/profile/17889678799981918534noreply@blogger.com8tag:blogger.com,1999:blog-32998500.post-40948222661372283842008-04-15T09:01:00.001+05:302008-04-15T09:01:39.983+05:30MDX Troubleshooting: comparing large numbers of MDX resultsets<p>Often I need to verify if the MDX results are correct after I fine tune the query or change the approach.</p> <p>Usually, I copy and paste the cellset results in Excel and then compare them.</p> <p>Recently, I got hold of a useful post from Chris Webb where he imported the cellset to a SQL table using SSIS and then used a Tablediff tool to compare the values. If we can make it a application, that would be great but till then, the approach serves the purpose.</p> <p>You can read the blog <a href="http://chriswebb.findtechblogs.com/default.asp?item=712159">here</a>.</p> Ajit Singhhttp://www.blogger.com/profile/17889678799981918534noreply@blogger.com0tag:blogger.com,1999:blog-32998500.post-5120625559503210552008-04-13T12:32:00.001+05:302008-04-18T11:52:17.179+05:30Aggregation design: useful tips<p>Many useful tips can be garnered from the below sources:</p> <p>1. <a href="http://www.microsoft.com/technet/prodtechnol/sql/2005/ssas2005perfguide.mspx">SSAS 2005 Performance Guide</a></p> <p>2. BIDs Helper:</p> <ul> <li><a href="http://www.codeplex.com/bidshelper/Wiki/View.aspx?title=Aggregation%20Manager&referringTitle=Home">Aggregation Manager</a> </li> <li><a href="http://www.codeplex.com/bidshelper/Wiki/View.aspx?title=Delete%20Unused%20Aggregations&referringTitle=Home">Delete Unused Aggregations</a> </li> <li><a href="http://www.codeplex.com/bidshelper/Wiki/View.aspx?title=Deploy%20Aggregation%20Designs&referringTitle=Home">Deploy Aggregation Designs</a> </li> <li><a href="http://www.codeplex.com/bidshelper/Wiki/View.aspx?title=Validate%20Aggregations&referringTitle=Home">Validate Aggregations</a> </li> </ul> <p>3. Many-to-many design aggregation design</p> <h6><a href="http://www.microsoft.com/downloads/details.aspx?familyid=3494E712-C90B-4A4E-AD45-01009C15C665&displaylang=en">Analysis Services Many-to-Many Dimensions: Query Performance Optimization Techniques</a></h6> Ajit Singhhttp://www.blogger.com/profile/17889678799981918534noreply@blogger.com0tag:blogger.com,1999:blog-32998500.post-56845780825917240952008-04-11T09:15:00.001+05:302008-04-11T09:16:31.701+05:30Selecting dimension's default member based on a member property<p>Suppose, I need to write an MDX statement that selects a certain month of my Time dimension based on a Month level member property, FLAG_LAST_MONTH, below are the two approaches:</p> <p>SELECT {StrToMember(Date.CurrentMember.Properties("FLAG_LAST_MONTH"))} on rows,</p> <p>{} on columns</p> <p>from MyCube</p> <p>or </p> <p>Based on the property name: <strong>FLAG_LAST_MONTH</strong>, I assume that it has a value like "1" only for one month.  In that case, you could use Filter() to select the desired month member:</p> <p><strong>SELECT Filter([Date].[Month].Members,</strong></p> <p><strong>[Date].CurrentMember.Properties("FLAG_LAST_MONTH") = "1") on rows</strong></p> <p><strong>from MyCube</strong></p> <p>If you try to use the below query, you get an error, since <strong>Date.CurrentMember.Properties("FLAG_LAST_MONTH")</strong> - doesn't return a member, </p> <p>SELECT {Date.CurrentMember.Properties("FLAG_LAST_MONTH")} on rows, <br />{Date.Month.Members} on columns <br />from MyCube</p> <p><a title="http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=911884&SiteID=1" href="http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=911884&SiteID=1">http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=911884&SiteID=1</a></p> Ajit Singhhttp://www.blogger.com/profile/17889678799981918534noreply@blogger.com0tag:blogger.com,1999:blog-32998500.post-43372394792852608362008-04-11T08:38:00.001+05:302008-04-11T08:42:45.104+05:30Time Dimension: How to set Default Member to Current Month<p>Question: </p> <p>I want to set current Month as a Default member in my Time Dimension. So that every time i see my data it should display most current data.</p> <p>Answer:</p> <p>Here is an example using the Adventure Works cube.  You can add this to the cube script for the Adventure Works cube and it will default the day to the current date using the Now() function.  I had to use (Now() - 1000) to set the date back to 3/25/2004 due to the fact that the Adventure Works cube Date dimension ends at 8/31/2004, but I think you will get the idea.  The other thing to note here is that the [Date].[Date] attribute has a "ValueColumn" defined that is of type "Date".  This allows the filter statement to use a straight date vs. date comparison.</p> <p>-- Now() = 12/19/2006</p> <p>-- Now() - 1000 = 3/25/2004</p> <p>ALTER CUBE CURRENTCUBE</p> <p>UPDATE DIMENSION [Date],</p> <p>DEFAULT_MEMBER = Tail(Filter([Date].[Date].Members,[Date].[Date].MemberValue < (Now() - 1000)),1)(0);</p> <p>HTH,</p> <p>Steve</p> <p> <hr align="left" width="25%" size="1" />Steve Pontello </p> <p><a title="http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=1036989&SiteID=1" href="http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=1036989&SiteID=1">http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=1036989&SiteID=1</a></p> Ajit Singhhttp://www.blogger.com/profile/17889678799981918534noreply@blogger.com0tag:blogger.com,1999:blog-32998500.post-7057580924148050492008-04-11T08:32:00.001+05:302008-04-11T08:33:57.322+05:30Setting dynamic default member in dimension X based on the current member of dimensions Y<p>Is it possible to define a default member in dimension X based on the current member of dimensions Y.</p> <p>E.g. I have 2 dimensions Cust (Customer)  and Ver (version).</p> <p>Suppose I have the following facts:</p> <p>Customer 1, version 1</p> <p>Customer 1, version 2</p> <p>Customer 2, version 1</p> <p>Customer 2, version 2</p> <p>Customer 2, version 3</p> <p>The default member in the version dimension should be the highest version for this specific Customer.</p> <blockquote> <p>Customer 1, version 2</p> <p>Customer 2, version 3</p> </blockquote> <p>I wrote the following MDX</p> <p>(TAIL(NONEMPTY({[Ver].[Ver - Ver].children}, [Cust].[Cust - Cust].CurrentMember), 1)).Item(0)</p> <p>However this returns the following error when I try to browse the cube from BIDS.</p> <p><strong>DefaultMember(Ver,Ver) (1, 46) The dimension '[Cust]' was not found in the cube when the string, [Cust].[Cust - Cust], was parsed.</strong></p> <p>When I connect from Excel, the default member is ignored and the ALL level is used.</p> <p>Answer:</p> <p>What I would do is to add a record into your version table called "Latest" or "Current" or something like that. Then I would setup this new member as the default member and add a script like the following to the cube.</p> <p>SCOPE ([Ver].[Ver - Ver].[Current]);</p> <p>   this = Aggregate(EXISTING [Cust].[Cust - Cust].Members</p> <p>                         , TAIL(NONEMPTY({[Ver].[Ver - Ver].children} ), 1).Item(0)</p> <p>                            )</p> <p>END SCOPE;</p> <p>This script finds all of the customers currently in context and then finds the last version for each one and aggregates them all together.</p> <p>The problem with using .CurrentMember in a default member declaration is that .CurrentMember returns the member currently in context for a given query. The default member is established before any queries take place, so there is no .CurrentMember.</p> <p><a title="http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=2394139&SiteID=1" href="http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=2394139&SiteID=1">http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=2394139&SiteID=1</a></p> Ajit Singhhttp://www.blogger.com/profile/17889678799981918534noreply@blogger.com1tag:blogger.com,1999:blog-32998500.post-55809619424955464372008-04-06T07:18:00.001+05:302008-04-06T07:21:14.412+05:30Parent child hierarchy to level base hierarchy conversion: hiding placeholder dimension members in client application<p>When a parent child hierarchy, which is essentially a unbalanced hierarchy is converted to level based hierarchy, it becomes a ragged hierarchy.</p> <p>In the hierarchy, for many members, the parent members are not present in the immediate above level and we need to put placeholder members as parent in those levels. The HideMemberIf property of a level in a hierarchy is set appropriately to hide these placeholder or missing members from end users.</p> <p>However, in the client applications, these placeholder members do not show properly as below:</p> <p><a href="http://lh5.google.com/ajit555/R_grgoz-ipI/AAAAAAAAAc8/A-26NAjwF9I/EmptyPlaceholderMembersShown%5B3%5D.jpg"><img style="border-right: 0px; border-top: 0px; border-left: 0px; border-bottom: 0px" height="419" alt="EmptyPlaceholderMembersShown" src="http://lh6.google.com/ajit555/R_grh4z-iqI/AAAAAAAAAdE/2r8W0Or0clw/EmptyPlaceholderMembersShown_thumb%5B1%5D.jpg" width="367" border="0" /></a> </p> <p>The hierarchy in the client application can be displayed properly by using the MDX Compatibility property in the connection string the instance of Analysis Services which must be set to 2 to display ragged hierarchies correctly. </p> <p>The MDX Compatibility property determines how placeholder members in a ragged or unbalanced hierarchy are treated. If you set the MDX Compatibility property value to 1, you expose a placeholder member in a ragged hierarchy.</p> <p><a href="http://lh3.google.com/ajit555/R_grjIz-irI/AAAAAAAAAdM/nVgbzS4Cqi4/ConnectionString%5B3%5D.jpg"><img style="border-right: 0px; border-top: 0px; border-left: 0px; border-bottom: 0px" height="329" alt="ConnectionString" src="http://lh3.google.com/ajit555/R_grlIz-isI/AAAAAAAAAdU/pz19PmpbJOk/ConnectionString_thumb%5B1%5D.jpg" width="638" border="0" /></a> </p> <p>Now the same hierarchy is displayed correctly:</p> <p><a href="http://lh6.google.com/ajit555/R_grm4z-itI/AAAAAAAAAdc/ZmEaik51cWA/EmptyPlaceholderMembersHidden%5B3%5D.jpg"><img style="border-right: 0px; border-top: 0px; border-left: 0px; border-bottom: 0px" height="349" alt="EmptyPlaceholderMembersHidden" src="http://lh4.google.com/ajit555/R_gr4Yz-iuI/AAAAAAAAAdk/NKrdPfAcNSU/EmptyPlaceholderMembersHidden_thumb%5B1%5D.jpg" width="333" border="0" /></a></p> Ajit Singhhttp://www.blogger.com/profile/17889678799981918534noreply@blogger.com6