tag:blogger.com,1999:blog-86096726938547161412024-03-12T21:18:02.718-07:00Business Intelligence , Agile and .NET developmentAshokDugaputihttp://www.blogger.com/profile/14921707193483360012noreply@blogger.comBlogger20125tag:blogger.com,1999:blog-8609672693854716141.post-66615942100243460132012-03-08T10:44:00.000-08:002012-03-08T10:44:09.927-08:00Good example on Incremental vs. Iterative development<div dir="ltr" style="text-align: left;" trbidi="on">
<div class="MsoNormal" style="margin: 0in 0in 0pt;">
<span style="font-family: Calibri;">A good example (especially pictorial representation) on Incremental vs. Iterative development.</span></div>
<div class="separator" style="clear: both; text-align: center;">
<a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEiOxPySrh17T_5ewPqApHZVnS1TzEiVczCWPbavSLTaIV2sEuxo1DpC99DS3kNd5o7XSKmvdgylrPTVY4F1vKkWTh7BSLSEWQ2FBBn3a6O0aA0yo-8N1IGP-NmeT8RSFhyHgkOK7vfxW3wL/s1600/Agile.jpg" imageanchor="1" style="clear: left; cssfloat: left; float: left; height: 417px; margin-bottom: 1em; margin-right: 1em; width: 638px;"><img border="0" height="417" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEiOxPySrh17T_5ewPqApHZVnS1TzEiVczCWPbavSLTaIV2sEuxo1DpC99DS3kNd5o7XSKmvdgylrPTVY4F1vKkWTh7BSLSEWQ2FBBn3a6O0aA0yo-8N1IGP-NmeT8RSFhyHgkOK7vfxW3wL/s640/Agile.jpg" width="640" yda="true" /></a></div>
<div class="MsoNormal" style="margin: 0in 0in 0pt;">
<br /></div>
</div>AshokDugaputihttp://www.blogger.com/profile/14921707193483360012noreply@blogger.com1tag:blogger.com,1999:blog-8609672693854716141.post-9730901977518368162012-03-08T10:37:00.000-08:002012-03-08T10:37:04.038-08:00ETL 2.0: Redefining Data Integration<div dir="ltr" style="text-align: left;" trbidi="on">
<br />
Nice presentation on next generation of ETL. The content and presenting the latest trend in pictorial format is very impressive. Nicely presented the possibilities with BIGDATA.<br />
<br />
<div class="separator" style="clear: both; text-align: center;">
<a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEiyJ96pD4xrbaAmev9OO9PjpaQdSFQO9nqeiCAhGs63TmQblryKSxj2rLlntbbdjeURRzKF3ldwZL4Xt7KDsNrM8pGEPPGVUlrcomFMDJxpoUX6cDHZge2yGKLN3pQ4Etc-NLKLit-GJhQ2/s1600/ETL.png" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img border="0" height="177" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEiyJ96pD4xrbaAmev9OO9PjpaQdSFQO9nqeiCAhGs63TmQblryKSxj2rLlntbbdjeURRzKF3ldwZL4Xt7KDsNrM8pGEPPGVUlrcomFMDJxpoUX6cDHZge2yGKLN3pQ4Etc-NLKLit-GJhQ2/s320/ETL.png" width="320" yda="true" /></a></div>
</div>AshokDugaputihttp://www.blogger.com/profile/14921707193483360012noreply@blogger.com1tag:blogger.com,1999:blog-8609672693854716141.post-67924298378100332152010-09-23T06:37:00.001-07:002010-09-23T06:44:30.416-07:00MDX Variables to Boost OLAP reports performance significantly<span xmlns=''><p>Recently I figured out a way to make the OLAP reports significantly faster by avoiding MDX calculations at run time. I got tons of appreciation from my customers on OLAP reports performance.<br /></p><p>The trick is using MDX Variables. Please do not try to Google it <span style='font-family:Wingdings'></span> this is what I named it.<br /></p><h2>Business Cases<br /></h2><p>It is very common to see OLAP reports with tons of Time MDX functions in multiple scenarios. Some of those scenarios include<br /></p><ol><li>Providing Out of the box OLAP reports offered by using SSRS or any other reporting client by utilizing Time calculations.<br /></li><li>Custom OLAP client allowing report designers to provide the MDX to get a Financial Report<br /></li></ol><p>Typically MDX structure would be as mentioned below<br /></p><p><span style='color:blue; font-family:Arial Narrow; font-size:7pt'>WITH</span><br /> </p><p><span style='color:blue; font-family:Arial Narrow; font-size:7pt'>MEMBER [Measures].[CurrentMonth] AS ([Date].[Calendar].Currentmember,[Measures].[Amount])</span><br /> </p><p><span style='color:blue; font-family:Arial Narrow; font-size:7pt'>MEMBER [Measures].[CurrentMonthYTD] AS (Sum(YTD(),([Date].[Calendar].Currentmember,[Measures].[Amount])))</span><br /> </p><p><span style='color:blue; font-family:Arial Narrow; font-size:7pt'>MEMBER [Measures].[PrevYearSameMonth] AS (ParallelPeriod ([Date].[Calendar].[Calendar Year],1,[Date].[Calendar].Currentmember ),[Measures].[Amount])</span><br /> </p><p><span style='color:blue; font-family:Arial Narrow; font-size:7pt'>MEMBER [Measures].[PrevYearSameMonthYTD] AS (Sum(YTD(),ParallelPeriod ([Date].[Calendar].[Calendar Year],1,[Date].[Calendar].Currentmember ),[Measures].[Amount]))</span><br /> </p><p><span style='color:blue; font-family:Arial Narrow; font-size:7pt'>SELECT</span><br /> </p><p><span style='color:blue; font-family:Arial Narrow; font-size:7pt'>{ [Measures].[CurrentMonth],[Measures].[CurrentMonthYTD],[Measures].[PrevYearSameMonth],[Measures].[PrevYearSameMonthYTD]} ON COLUMNS,</span><br /> </p><p><span style='color:blue; font-family:Arial Narrow; font-size:7pt'>{Descendants({[Account].[Accounts].&[47]},[Account].[Accounts].[Account Level 02],SELF_AND_AFTER)} ON ROWS</span><br /> </p><p><span style='color:blue; font-family:Arial Narrow; font-size:7pt'>FROM [Adventure Works]</span><br /> </p><p><span style='color:blue; font-family:Arial Narrow; font-size:7pt'>WHERE ([Date].[Calendar].[Month].[February 2004],[Scenario].[Scenario].&[1])</span><br /> </p><p>If you have observed Time functions in WITH MEMBER clause, It requires multiple evaluation to get the correct Time period based where Slicer coordinate. This can significantly degrade the performance. Especially if you are working in a environment where 0% aggregate exist, then it is going to impact the data retrieval time quite significantly.<br /></p><p>Majority of the cases we know what the value is going to be evaluated for each measure here.<br /></p><h2>Solution<br /></h2><ol><li>Manage the MDX variables at database level and provide a User interface for administrator to set the default values<br /></li></ol><p><em>Example:</em><br /> </p><p>Name : @CURRENT_MONTH<br /></p><p>Value : <span style='font-family:Courier New; font-size:7pt'>[Date].[Calendar].[Month].[February 2004]</span><br /> </p><ol><li><div>Construct your MDX in your report by utilizing MDX variables.<br /></div><p><br /> </p></li></ol><p>Of course you will have do this by utilizing SSRS reports or any other way depending on your OLAP client environment. ( I am not going to detail level of explanation about how exactly MDX is going to be in SSRS.)<br /></p><p><span style='font-family:Arial Narrow; font-size:7pt'><span style='color:blue'>WITH</span><br /> <span style='color:blue'>MEMBER</span> [Measures].[CurrentMonth] <span style='color:blue'>AS</span> (@CURRENT_MONTH,[Measures].[Amount])</span><br /> </p><p><span style='font-family:Arial Narrow; font-size:7pt'><span style='color:blue'>MEMBER</span> [Measures].[CurrentMonthYTD] <span style='color:blue'>AS</span> (<span style='color:maroon'>Sum</span>(<span style='color:maroon'>YTD</span>(),(@CURRENT_MONTH,[Measures].[Amount])))</span><br /> </p><p><span style='font-family:Arial Narrow; font-size:7pt'><span style='color:blue'>MEMBER</span> [Measures].[PrevYearSameMonth] <span style='color:blue'>AS</span> (<span style='color:maroon'>ParallelPeriod</span> ([Date].[Calendar].[Calendar Year],1, @CURRENT_MONTH),[Measures].[Amount])</span><br /> </p><p><span style='font-family:Arial Narrow; font-size:7pt'><span style='color:blue'>MEMBER</span> [Measures].[PrevYearSameMonthYTD] <span style='color:blue'>AS</span> (Sum(YTD(),<span style='color:maroon'>ParallelPeriod</span> ([Date].[Calendar].[Calendar Year],1, @CURRENT_MONTH),[Measures].[Amount]))</span><br /> </p><p><span style='color:blue; font-family:Arial Narrow; font-size:7pt'>SELECT</span><br /> </p><p><span style='font-family:Arial Narrow; font-size:7pt'>{ [Measures].[CurrentMonth],[Measures].[CurrentMonthYTD],[Measures].[PrevYearSameMonth],[Measures].[PrevYearSameMonthYTD]} <span style='color:blue'>ON</span><br/><span style='color:blue'>COLUMNS</span>,</span><br /> </p><p><span style='font-family:Arial Narrow; font-size:7pt'>{<span style='color:blue'>Descendants</span>({[Account].[Accounts].&[47]},[Account].[Accounts].[Account Level 02],<span style='color:blue'>SELF_AND_AFTER</span>)} <span style='color:blue'>ON</span><br/><span style='color:blue'>ROWS</span></span><br /> </p><p><span style='font-family:Arial Narrow; font-size:7pt'><span style='color:blue'>FROM</span> [Adventure Works] <span style='color:blue'>WHERE</span> ([Date].[Calendar].[Month].[February 2004],[Scenario].[Scenario].&[1])</span><br /> </p><p>Try to run the report, you would notice that report runs faster. While evaluating this approach, please do not limit your thinking to just above example, try to think for thousands of report lines with so many columns in the above structure.<br /></p><p>In my environment I am keeping a separate type of MDX variables and replacing the Parallel Periods as well. This separate type of variables first run independently (simple MDX), get the correct variables before running the report.<br /></p><p>So MDX would look like:<br /></p><p><span style='color:blue; font-family:Arial Narrow; font-size:7pt'>WITH MEMBER [Measures].[CurrentMonth] AS (@CURRENT_MONTH,[Measures].[Amount])</span><br /> </p><p><span style='font-family:Arial Narrow; font-size:7pt'><span style='color:blue'>MEMBER [Measures].[CurrentMonthYTD] AS (</span><span style='color:maroon'>Sum</span><span style='color:blue'>(YTD(),(@CURRENT_MONTH,[Measures].[Amount])))</span></span><br /> </p><p><span style='color:blue; font-family:Arial Narrow; font-size:7pt'>MEMBER [Measures].[PrevYearSameMonth] AS (@PrevYear_Of_CurMonth,[Measures].[Amount])</span><br /> </p><p><span style='color:blue; font-family:Arial Narrow; font-size:7pt'>MEMBER [Measures].[PrevYearSameMonthYTD] AS (Sum(YTD(),(@PrevYear_Of_CurMonth,[Measures].[Amount]))</span><br /> </p><p><span style='color:blue; font-family:Arial Narrow; font-size:7pt'>SELECT</span><br /> </p><p><span style='color:blue; font-family:Arial Narrow; font-size:7pt'>{ [Measures].[CurrentMonth],[Measures].[CurrentMonthYTD],[Measures].[PrevYearSameMonth],[Measures].[PrevYearSameMonthYTD]} ON COLUMNS,</span><br /> </p><p><span style='color:blue; font-family:Arial Narrow; font-size:7pt'>{Descendants({[Account].[Accounts].&[47]},[Account].[Accounts].[Account Level 02],SELF_AND_AFTER)} ON ROWS</span><br /> </p><p><span style='color:blue; font-family:Arial Narrow; font-size:7pt'>FROM [Adventure Works] WHERE ([Date].[Calendar].[Month].[February 2004],[Scenario].[Scenario].&[1])</span><br /> </p><p>I applied this trick and trust me this is a GAME CHANGER in my environment. <span style='font-family:Wingdings'></span><br /> </p><p>I was not patient enough to provide give details. But if you think this is going to be helpful, please email me at <a href='mailto:ashok_d_in@yahoo.com'>ashok_d_in@yahoo.com</a>. I can explain you.<br /></p><p><br /> </p><p><br /> </p><p><br /> </p><p><br /> </p><p><br /> </p><p><br /> </p><p><br /> </p></span>AshokDugaputihttp://www.blogger.com/profile/14921707193483360012noreply@blogger.com2tag:blogger.com,1999:blog-8609672693854716141.post-76622432999149738322010-06-24T02:47:00.000-07:002010-06-24T03:09:06.280-07:00Attribute Vs Properties in Excel 2007 and OWC 11Following post demonstrates about how an attribute can be used to display the data on same column or separate column(s). Especially those who are coming from RDBMS background would feel the Screenshot #1 shown below is wrong. So wanted to show how an attribute is differentiated with Property<br /><br /><br /><strong>When attributes are added to Rows or Column Axis</strong> : When attributes are added Row or Column Labels then it would cross join the attributes and display the data in one column.<br /><br /><br /><a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEghg_K5pjYyQWpETO8jWKxXquFxA0W2klgxkZ8b4pdxi4ELrmQ77Us-3xvIGOoMx8w28Etkt3HJwZRz9FhOAOWAneJTHqY3VY9OhE50lVZKfSjbFSxSoK65Yvd-hvlHkQL4Y79alqS6sWoE/s1600/Image1.png"><img style="WIDTH: 320px; HEIGHT: 132px; CURSOR: hand" id="BLOGGER_PHOTO_ID_5486274803766027650" border="0" alt="" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEghg_K5pjYyQWpETO8jWKxXquFxA0W2klgxkZ8b4pdxi4ELrmQ77Us-3xvIGOoMx8w28Etkt3HJwZRz9FhOAOWAneJTHqY3VY9OhE50lVZKfSjbFSxSoK65Yvd-hvlHkQL4Y79alqS6sWoE/s320/Image1.png" /></a><br /><strong>When attributes are used as Properties:</strong><br /><br /><strong>Excel 2007 : </strong> When attributes are displayed properties, then for each leaf member it would display the corresponding attribute. This output is preferred in multiple situations.<br /><a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEilXCLovj6gNHWSjazWnXS-0z-ZTv0Wf1rvsOXStfVcuBEIEWJ_3oI6y_JyCTQ8Qpra3hvravpZjgZuHNiMSYYup_hbFfXLDDvlQeSYv6AjnwRKzs5WM_mVnUqgHueZDFNM9a6A1-UllQxb/s1600/Image2.png"><img style="WIDTH: 320px; HEIGHT: 138px; CURSOR: hand" id="BLOGGER_PHOTO_ID_5486275242634796258" border="0" alt="" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEilXCLovj6gNHWSjazWnXS-0z-ZTv0Wf1rvsOXStfVcuBEIEWJ_3oI6y_JyCTQ8Qpra3hvravpZjgZuHNiMSYYup_hbFfXLDDvlQeSYv6AjnwRKzs5WM_mVnUqgHueZDFNM9a6A1-UllQxb/s320/Image2.png" /></a><br /><br /><br /><br />Properties are enabled as shown in below image.<br /><br /><br /><a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEjaOPNj6AjOgEuXiWS3_KaS4X4fsUCsS0JccbcDGHCUp6abv9zObT2XQkdNPFucsD-v5qt5meX1GizRdtDhrSAvVhiJdY41nygp5ytr852ffaiK4KXpUcy3phW3aiyjJLKr79ersVmEsbim/s1600/Image3.png"><img style="WIDTH: 303px; HEIGHT: 320px; CURSOR: hand" id="BLOGGER_PHOTO_ID_5486275464742594098" border="0" alt="" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEjaOPNj6AjOgEuXiWS3_KaS4X4fsUCsS0JccbcDGHCUp6abv9zObT2XQkdNPFucsD-v5qt5meX1GizRdtDhrSAvVhiJdY41nygp5ytr852ffaiK4KXpUcy3phW3aiyjJLKr79ersVmEsbim/s320/Image3.png" /></a><br /><strong>OWC 11 ( Bids Cube browser)</strong> OWC acts little bit differently in OWC 11. Properties are enabled using the similar command dialog box shown in above image but output is totally different. Following image would display the Account properties.<br /><br /><a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEg5WltK48Seac0lm_jKFAoNoYfav8TQZsctivLkvWuHiFHRQJGuQx_CrbOnYcO_tKRJzuOOpM5kTQBZp0hDxkB4RI29eemlar0sEja2A5lweNQX8KaY2jfRSjgFBCwFpYnpuj93VKdWF7J3/s1600/Image4.png"><img style="WIDTH: 320px; HEIGHT: 231px; CURSOR: hand" id="BLOGGER_PHOTO_ID_5486279523657985634" border="0" alt="" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEg5WltK48Seac0lm_jKFAoNoYfav8TQZsctivLkvWuHiFHRQJGuQx_CrbOnYcO_tKRJzuOOpM5kTQBZp0hDxkB4RI29eemlar0sEja2A5lweNQX8KaY2jfRSjgFBCwFpYnpuj93VKdWF7J3/s320/Image4.png" /></a>AshokDugaputihttp://www.blogger.com/profile/14921707193483360012noreply@blogger.com1tag:blogger.com,1999:blog-8609672693854716141.post-26844916973909953892010-05-16T04:05:00.000-07:002010-05-16T04:15:51.611-07:00How to avoid Long running MDX Requests<p><span style="font-family:Calibri;">I wanted to write a small post about ‘Timeout’, which really helped me practically to manage long running MDX requests.</span></p><p><span style="font-family:Calibri;"><em><strong>Example of connection string with Timeout</strong></em></span></p><p style="MARGIN: 0in 0in 10pt" class="MsoNormal"><span style="font-family:Calibri;"><span style="mso-spacerun: yes"></span>Provider=MSOLAP.3; Data Source= MySSASServerName; Initial Catalog= MySSASDBName; User ID= windowsusername; Password= windowsuserpwd;<b style="mso-bidi-font-weight: normal"><span style="BACKGROUND: yellow; COLOR: #548dd4; mso-highlight: yellow; mso-theme: 153color:#0e002;" >Timeout=120;</span><span style="COLOR: #548dd4; mso-theme: 153color:#0e002;" > <?xml:namespace prefix = o ns = "urn:schemas-microsoft-com:office:office" /><o:p></o:p></span></b></span></p><p style="MARGIN: 0in 0in 10pt" class="MsoNormal"><span style="font-family:Calibri;">In the above example, requested MDX would be allowed to run in 2 minutes. After 2 minutes request will be cancelled.</span></p><p style="MARGIN: 0in 0in 10pt" class="MsoNormal"><span style="font-family:Calibri;">This is very important feature and every developer should consider using. Usually OLAP clients provide the flexibility to design the reports by authorized users and/or select the multiple dimension members on rows or columns or pages. So based on selection made by end user, sometimes prepared MDX could be big or complex and requires lot of resources & time to execute the same.<span style="mso-spacerun: yes"> </span>So when it is a multi user environment (especially concurrency is high) and end user has got flexibility to design the reports & run the same, then there will be ‘Competency for the resources’ and one request could bring down the server, which will drop everyone’s requests, which nobody wants to happen.</span></p><p style="MARGIN: 0in 0in 10pt" class="MsoNormal"><span style="font-family:Calibri;">Practically I encountered Production server (power full) going down and cancelling all the request because of Single MDX request. It was horrible situation to know that Server went down.</span></p><p style="MARGIN: 0in 0in 10pt" class="MsoNormal"><span style="font-family:Calibri;">In order to not to bring down the Production server, I used ‘Timeout’ property in connection string and given flexibility to change this number by Department and by U. Since I’ve given flexibility (Managing Timeout time in a separate table) to specify in the connection string, Reporting Administrator <span style="mso-spacerun: yes"></span>can manage control the Timeout time depending the importance of User or Department usage.</span></p><p style="MARGIN: 0in 0in 10pt" class="MsoNormal"><span style="font-family:Calibri;"><strong>Example: </strong>Finance department is working on ‘Month end closure’ or they got to present the Financial statements to their BOD, so obviously their work gets the highest priority. Similarly CEO is performing What-If Analysis and that is important step for defining ‘Strategy’ for entire company. In these type of situations, you do not want to drop their MDX request unless it is too long request to execute</span></p><p style="MARGIN: 0in 0in 10pt" class="MsoNormal"><span style="font-family:Calibri;">The above mentioned situation is not unique. I would think it is very common requirement and you never want single MDX request bringing the server down. So it is always safer to use Timeout property and manage the Server uptime well. </span></p><p style="MARGIN: 0in 0in 10pt" class="MsoNormal"><span style="font-family:Calibri;">Clearly there is no hard number of Timeout time that I can recommend because depending the user base, concurrency, complexity of the reports, flexibility that is given to the end user, one should determine how much this number (Time) should be.</span></p><p style="MARGIN: 0in 0in 10pt" class="MsoNormal"><span style="font-family:Calibri;">Finally, do not miss to handle the error in your OLAP client when Timeout drops the request. Always it is good practice to display the message to end user about why the requesst is dropped. It is also very important to integrate some mechanism to communicate Reporting Administrator about how many requests were dropped. In my case, I generate an email to Reporting Administrator with user & report details when a request is cancelled.</span></p><p style="MARGIN: 0in 0in 10pt" class="MsoNormal"><span style="font-family:Calibri;">Hope this helps!!</span></p>AshokDugaputihttp://www.blogger.com/profile/14921707193483360012noreply@blogger.com1tag:blogger.com,1999:blog-8609672693854716141.post-25672182020633108022010-04-25T18:58:00.000-07:002010-04-25T19:03:20.871-07:00How to measure ROI on BI applicationsI’m excited to see Boris Evelson’s excellent Blog post on measuring ROI on Business Intelligence application. He has listed out the key data points (Usage Tracking, Efficiency & Effectiveness) to collect/track in an organization and also mentioned about how these data points will be helpful for defining action items and planning future BI applications. I’ve seen some BI implementations with little or no insight on ROI and continuing the BI implementation endlessly. Though it is difficult to collect (automatically & painless) the data points that he mentioned are really informative.<br />In SaaS environment , Self service BI & Response time are really key items and measuring the same would really helpful in assessing the SaaS based BI applications.<br />Here is the URL of the post <a href="http://blogs.forrester.com/boris_evelson/10-04-05-bi_bi">http://blogs.forrester.com/boris_evelson/10-04-05-bi_bi</a>AshokDugaputihttp://www.blogger.com/profile/14921707193483360012noreply@blogger.com0tag:blogger.com,1999:blog-8609672693854716141.post-21912634086026169682010-03-04T09:05:00.000-08:002010-03-04T09:10:52.947-08:00Debugging MDX with MDX StudioMDX Studio by Mosha Pasumansky is a gift to the SSAS developers.<br />MDX Studio is a tool which helps users of Microsoft Analysis Services to analyze complex MDX expressions, monitor performance characteristics of MDX queries and get insight into how MDX interacts with other UDM features such as attribute relationships. MDX Studio provides unique visualization representation of MDX expression and allows the user to interact with different stages of MDX execution.<br /><br />Interesting feature is Debugging, Video is available on You tube (<a href="http://www.youtube.com/watch?v=MNUJkHYwOKI">http://www.youtube.com/watch?v=MNUJkHYwOKI</a><br /><br />In addition to this, many times it requires a tool to format lengthy MDX for better understanding. Fthis purpose, In addition to Desktop version of MDX studio, online MDX studio is also available with some basic functionality. This is available at <a href="http://mdx.mosha.com/default.aspx">http://mdx.mosha.com/default.aspx</a> . I use this a lot when I get to analyze the MDX.AshokDugaputihttp://www.blogger.com/profile/14921707193483360012noreply@blogger.com0tag:blogger.com,1999:blog-8609672693854716141.post-51214358143025058212010-03-04T08:52:00.000-08:002010-03-04T09:01:59.700-08:00Host Analytics Occupied the Microsoft's spot in Forrester Wave report 2009This is an interesting news on Microsoft PPS.<br /><br />As we all know, Microsoft entered into Business Performance Solutions or Corporate Performance solutions or Enter prise solutions space by introducing PPS (Performance Point Server) a few years ago. In the last Forrester Wave report (2007 ) report, Microsoft was also considered as a leader in this space.<br /><br />But in the latest report published, Microsoft is not listed in the report and Host Analytics (only SaaS CPM Vendor (<a href="http://www.hostanalytics.com/">www.hostanalytics.com</a>) took Microsoft's spot on this report.<br /><br />FYI - Forrester Wave report is produced once in 2 years and it profiles major software product vendors world wide in each solution vertical.AshokDugaputihttp://www.blogger.com/profile/14921707193483360012noreply@blogger.com0tag:blogger.com,1999:blog-8609672693854716141.post-47110807903254420872009-12-09T03:28:00.000-08:002009-12-09T03:30:49.091-08:00Implementing Best Practices For Large Analysis Services DatabasesAn Excellent demo by Scalability Experts & Microsoft on Implementing Best Practices For Large Analysis Services Databases. I really like this presentation as it suggests best practices at RDBMS level as well as at SSAS level.<br /><a href="http://www.microsoft.com/winme/0905/1000759/default.html">http://www.microsoft.com/winme/0905/1000759/default.html</a>AshokDugaputihttp://www.blogger.com/profile/14921707193483360012noreply@blogger.com0tag:blogger.com,1999:blog-8609672693854716141.post-27697590043526176562009-11-10T20:06:00.000-08:002009-11-10T20:09:11.374-08:00The OLAP Report is now The BI VerdictOLAP Report is now named as BI Verdict. May be this is to conduct the research on entire BI not just OLAP market place ??<br />New URL: <a href="http://www.bi-verdict.com/">http://www.bi-verdict.com/</a>AshokDugaputihttp://www.blogger.com/profile/14921707193483360012noreply@blogger.com0tag:blogger.com,1999:blog-8609672693854716141.post-19423297067429115992009-10-18T21:41:00.000-07:002009-10-18T21:52:50.569-07:00Configuring HTTP Access to SQL Server 2008 Analysis Services on Microsoft Windows Server 2008A good article from Mike's blog on configuring HTTP Access to SQL Server 2008 Analysis Services on Microsoft Windows Server 2008.<br /><a href="http://bloggingabout.net/blogs/mglaser/archive/2008/08/15/configuring-http-access-to-sql-server-2008-analysis-services-on-microsoft-windows-server-2008.aspx">http://bloggingabout.net/blogs/mglaser/archive/2008/08/15/configuring-http-access-to-sql-server-2008-analysis-services-on-microsoft-windows-server-2008.aspx</a><br /><br />This article is very detailed & self explanatory.AshokDugaputihttp://www.blogger.com/profile/14921707193483360012noreply@blogger.com0tag:blogger.com,1999:blog-8609672693854716141.post-27814858494601122192009-09-27T08:12:00.000-07:002009-09-27T08:54:15.004-07:00Analysis Services Roadmap<span style="font-family:arial;">A good presentation by Siva Kumar Harinath (Senior Test lead from Microsoft) Analysis Services builds confidence that SSAS 2008 Scales better than SSAS 2005.</span><br /><span style="font-family:Arial;"></span><br /><a href="http://download.microsoft.com/download/4/9/0/4906f81b-eb1a-49c3-bb05-ff3bcbb5d5ae/SQL%20SERVER%202008%20-%20BUSINESS%20INTELLIGENCE%20AND%20ADMIN/Analysis%20Services%20redefined%20with%20SQL%20Server%202008%20-%20Sivakumar%"><span style="font-size:78%;">http://download.microsoft.com/download/4/9/0/4906f81b-eb1a-49c3-bb05-ff3bcbb5d5ae/SQL%20SERVER%202008%20-%20BUSINESS%20INTELLIGENCE%20AND%20ADMIN/Analysis%20Services%20redefined%20with%20SQL%20Server%202008%20-%20Sivakumar%</span></a><a href="http://download.microsoft.com/download/4/9/0/4906f81b-eb1a-49c3-bb05-ff3bcbb5d5ae/SQL%20SERVER%202008%20-%20BUSINESS%20INTELLIGENCE%20AND%20ADMIN/Analysis%20Services%20redefined%20with%20SQL%20Server%202008%20-%20Sivakumar%20Harinath.pdf"><span style="font-size:78%;">Harinath.pdf</span></a><br /><br />I am excited to see the below slide, which provides high level Road map of SSAS.<br /><br /><br /><a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEj7CmtZgKW2DAFFam0kWjrQz0L7rFGF6HU81SfYt27bI4673gfau85KjqP5FVEZSYY2UBrAaAPxrUTVSWw3nc2Twr9Q07Un6-82hTik9F1nlN1SBLDMK5CI8e1hyphenhyphenOe7Ux2WXSjwxH7pnv84/s1600-h/SSASredefined2008.png"><img style="WIDTH: 520px; HEIGHT: 394px; CURSOR: hand" id="BLOGGER_PHOTO_ID_5386168411299920690" border="0" alt="" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEj7CmtZgKW2DAFFam0kWjrQz0L7rFGF6HU81SfYt27bI4673gfau85KjqP5FVEZSYY2UBrAaAPxrUTVSWw3nc2Twr9Q07Un6-82hTik9F1nlN1SBLDMK5CI8e1hyphenhyphenOe7Ux2WXSjwxH7pnv84/s320/SSASredefined2008.png" /></a><br /><br /><br /><br />In the above image they mentioned that the mission of SQL 2005 has been completed in SSAS 2008. This seems to be true with the below information.<br /><br />Microsoft Adcenter orignally built their Business Intelligence solution for their online advertising compaings on Microsoft Bing. They shifted their current application from SSAS 2005 to SSAS 2008 and noticed significant performance improvements without any changes and they got more opportunity to tune it further. Following URL contains the complete information on the same.<br /><br /><a href="http://sqlcat.com/whitepapers/archive/2009/09/19/accelerating-microsoft-adcenter-with-microsoft-sql-server-2008-analysis-services.aspx">http://sqlcat.com/whitepapers/archive/2009/09/19/accelerating-microsoft-adcenter-with-microsoft-sql-server-2008-analysis-services.aspx</a><br /><br /><br /><br />Below Image clearly confirms that shifting their current app from SSAS 2005 to SSAS 2008 cut down their half oftheir processing time.<br /><br /><br /><br /><br /><a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEilaa-iogRa8tbXL6jjOa6tpuUALdU2SATcbL2h3kYyhEZPjuYOiTwfgiF3pXdEhBnvYdo4W7yQj1laSewN22SZkxkGZit4UcZxr8rNXwEcMs2nHkAR_ZEq8Bg2qem6Yk0Z0ZmlY2MEnwsL/s1600-h/SSAS2008ProcessingSpeed.png"><img style="WIDTH: 520px; HEIGHT: 414px; CURSOR: hand" id="BLOGGER_PHOTO_ID_5386170755556690738" border="0" alt="" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEilaa-iogRa8tbXL6jjOa6tpuUALdU2SATcbL2h3kYyhEZPjuYOiTwfgiF3pXdEhBnvYdo4W7yQj1laSewN22SZkxkGZit4UcZxr8rNXwEcMs2nHkAR_ZEq8Bg2qem6Yk0Z0ZmlY2MEnwsL/s320/SSAS2008ProcessingSpeed.png" /></a>AshokDugaputihttp://www.blogger.com/profile/14921707193483360012noreply@blogger.com0tag:blogger.com,1999:blog-8609672693854716141.post-55217151621703704092009-09-22T03:24:00.000-07:002009-09-22T03:33:22.703-07:00Parent-Child dimension hierarchy in SSRS 2008<div><font size="2" face="arial">When I first worked on SQL Server Reporting Server 2000 about 4 years ago. I did not like 2 items.<br />1) Ability to develop a report on Parent-Child dimension (Ragged hierarchy) </font></div><div><font size="2" face="arial"><br />2) Ability to type the MDX and see the output in RS report immediately (Without need to get the field list). </font></div><font size="2" face="arial"><div><br />Though my 2nd item is still not possible, but SSRS 2008 allowed flexibility to achieve #1. </div><div><br />Usage of Parent child dimension for Chart of Account or Employee or Company dimension is a very common case. But unfortunately there was no direct method to get the Parent-child dimension hierarchy displayed in single column in SSRS 2000. It is hard to build balanced hierarchy all the time and group them to have drill down from column1 (level 1) to columns 2 (level 2) I am excited to see this feature SSRS 2008 ( I guess 2005 as well) . </div><div> </div><div> </div><div> </div><div> </div><div> </div><div></font></div><font size="2" face="arial"><strong>Here is the OUTPUT </strong></font><font size="2" face="arial"><br /><a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEjBZGqoSYqPItmaBjnseH0g3VQ_JfYYSuIrRd6IkjqttnlfuZ_xBg0lziH9ECbSkc2GQkP5Id9tlr4zcBCKk1A4kbxh8GpFGQI0HfMItdvIR_supHBGWBCvGBAn5Pl3r035Qf4KH1ZCtjv4/s1600-h/RecursiveHierarchy.png"><img style="WIDTH: 200px; HEIGHT: 136px; CURSOR: hand" id="BLOGGER_PHOTO_ID_5384237573150491218" border="0" alt="" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEjBZGqoSYqPItmaBjnseH0g3VQ_JfYYSuIrRd6IkjqttnlfuZ_xBg0lziH9ECbSkc2GQkP5Id9tlr4zcBCKk1A4kbxh8GpFGQI0HfMItdvIR_supHBGWBCvGBAn5Pl3r035Qf4KH1ZCtjv4/s200/RecursiveHierarchy.png" /></a></font>AshokDugaputihttp://www.blogger.com/profile/14921707193483360012noreply@blogger.com0tag:blogger.com,1999:blog-8609672693854716141.post-64995201331550873152009-08-27T04:21:00.000-07:002009-08-27T04:25:34.319-07:00About MDXMissingMemberMode in SSAS<div><span style="font-family:arial;">I just wanted to write about the important Dimension property called “<strong>MDXMissingMemberMode</strong>” in SSAS 2005 and 2008.</span></div><span style="font-family:arial;"><div><br />In SSAS 2000, it was raising “Can’t find dimension member” errors. It used to be a difficult task to handle those errors. The only workaround was to use ISERROR function. Even this function can’t be used in many cases.</div><br /><div>Starting from SSAS 2005, new property is introduced to ignore the errors or to raise the errors. </div><div></div><br /><div>We’ve to be very careful in utilizing this property. Because it is very common tendency to set the property to set to ignore the errors and this case is valid only when you are running the reports.<br />But this case again bothers you if you have any interface to setup MDX formula. As a common practice in any MDX formula editors or setup interface, Syntax needs to be checked. If it is ignoring the Missing key errors you can’t image the impact on accuracy of results. Because it does not even show you the MDX syntax errors. Hardest part is that anything that you type is considered is taken as the correct MDX.</div><br /><div>Example is that instead of [Account].[Net Income] even if you put [Account].[Net Income Net income] or even if you give [Account].Net Income] (Starting bracket is missing) it would accept the MDX and does not raise the error.</div><br /><div><br />I’ve seen so many complaints around this and end user can’t image what’s going on with this.<br />This property can be setup in 2 places.</div><br /><div><br /><strong>1. At Dimension level ( as shown below)</strong> <img style="TEXT-ALIGN: center; MARGIN: 0px auto 10px; WIDTH: 200px; DISPLAY: block; HEIGHT: 146px; CURSOR: hand" id="BLOGGER_PHOTO_ID_5374602786925234306" border="0" alt="" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEgd09-ntO1v1EH_vhVkldULTWEEFcV9adh9XtuinrNwzip1Tq-rp5vDYIqB_MaXDm0tDUVVQf4sQgelmzUT1jOffX2U8KVJ9sKATTTNOfmtlLUGKksi6bY0tBYiSNBH-U93JZbEb7SHP72v/s200/MissingMDXMemberMode.png" /><br />If you setup the MDX Missing Member Mode property at dimension level then it ignores the errors only on this dimension.<br /><br /><strong>2. As a part of connection string</strong><br />If you specify the below property in your OLAP connection string then it would raise the errors related to missing keys / formula errors in all the dimensions.<br />MDX Missing Member Mode=Error;</div><br /><div><br />So the best practice is that always setup the Missing member mode property as a part of connection string. In addition to this, wherever you are allowing for formula setup – By default set to raise the error in connection string (Not at dimension level).<br />Wherever you are displaying the reports – By default set it to ignore the errors but provide flexibility in your OLAP clients or reporting interface to user to click on that option and see if there are any cells which are having formula syntax errors or missing keys.<br /><br />Hope fully this is helpful.</span></div>AshokDugaputihttp://www.blogger.com/profile/14921707193483360012noreply@blogger.com2tag:blogger.com,1999:blog-8609672693854716141.post-50781557075533425572009-08-25T02:57:00.001-07:002009-08-25T03:18:01.643-07:00Strategy AnalyzerI got to review one of the SSAS reporting application called Strategy Analyzer. Strategy Analyzer was developed to provide reporting capabilities on SSAS 2005 / 2008 with a very good User Interface. I would say it is a great product for generating OLAP reports. Mainly for Adhoc analysis and to create good looking Dashboards.<br /><img style="TEXT-ALIGN: center; MARGIN: 0px auto 10px; WIDTH: 200px; DISPLAY: block; HEIGHT: 137px; CURSOR: hand" id="BLOGGER_PHOTO_ID_5373840697835786354" border="0" alt="" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEiQvhTpu7WYP7sYMzVi838Mo3k81JHjDTL1cSDHzBHM4RDL1HXSooEoTPWnM6_uWt0GWt6vOgDPd8PvKA6fyRSDkBZ45rMOoHEIyX2ZgoeXNSSWapnYFteW-PAEjtlZHYbuLShp79TPySMY/s200/strategyanalyzer.png" />This product is available in 3 modes.<br />1) On-premise version<br />2) SaaS version<br />3) OEM/Web Services version<br /><br />Those who are looking for buying a OLAP client within their corporate and use it for reporting, this is a good option. Those who do not want to spend the upfront cost & manage the Infrastructure then SaaS option is better. For the vendors who are looking to integrate this application in their application (Involves programming) , OEM version is better.<br /><br />Usually we do not find the applications, which offer Dashboard setup & rendering in the same version of the application. Mostly Dashboard setup is available in desktop versions and rendering is provided in web version. But they have an excellent UI to do perform both the steps.<br /><br />They are supporting 5 report types<br />1.Pivot table (Abilty to generate Charts on top of it)<br />2. KPI Report (KPI Viewer to see SSAS KPIs)<br />3. Intelligent Map ( Map based reports along with its performance shown in indicators)<br />4. External web link can be added to its dashboard and get the output Ex: company stock news.<br />5. External Reporting services link to render the reporting services report in Dashboard.<br />6. Process Diagram to attach KPIS in a scorecard.<br /><br />Other technical highlights are:<br /><p>1. Support to Load Balancing (Uses Sticky session to ensure tha same server is contacted)</p><p>2. Support for Multi-language: It supports around 10 languages now.</p><p>3. Read SSAS 2005 functionality such as Perspectives, Measure groups and KPIs</p><p>4. Authentication (Anonymous or Basic or Integrated authentication).</p>5. Attach documents to KPIS.<br /><br />Thanks,<br />AshokAshokDugaputihttp://www.blogger.com/profile/14921707193483360012noreply@blogger.com1tag:blogger.com,1999:blog-8609672693854716141.post-66360167231784551282009-08-22T00:14:00.000-07:002009-08-22T01:26:03.767-07:00Calculation difference betwen SSAS 2000 and SSAS 2005&2008<div align="left"><span style="font-family:verdana;">I've noticed calculation difference betwen SSAS 2000 and SSAS 2005&2008.<br />Basically in a parent-child dimesinon, when child member refers its parents in Custom Memebrs<br />It is returning null in SSAS 2005 & 2008. But it works fine in SSAS 2000.<br /></span><span style="font-family:verdana;"><strong></strong></span></div><div align="left"><span style="font-family:verdana;"><strong>Case #1 :</strong> Created a new record in [AdventureWorksDW2008].dbo.[DimAccount] table with name called “Check This” and its Account Key is 100 (Identify column)<br /></div></span><div align="left"><span style="font-family:verdana;">a) Updated the Rollup Operator to ‘~’ (tilde) character<br /></span></div><div align="left"><span style="font-family:verdana;">b) Updated the Custom Members field to refer to Notes payable member<br /><br />Update DimAccount<br />Set CustomMembers='[Account].[Accounts].&[28]'<br />Where AccountKey='100'<br />Here Member formula '[Account].[Accounts].&[28]' refers to Notes Payable<br /></span></div><div align="left"><span style="font-family:verdana;">c) Processed Account dimension (Full Process) & Adventure Works cube (Full Process)<br /><strong></strong></span></div><div align="left"><span style="font-family:verdana;"><strong>Case #1 Result:<br /></strong>a) As expected it displayed the amount of Notes payable against my new member called ‘CheckThis’<br /><strong>Case #2:</strong> Updated the Custom Members field to refer to its parent member called ‘Current Liabilities’<br />Update DimAccount<br />Set CustomMembers='[Account].[Accounts].&[27]'<br />Where AccountKey='100'<br />Here Member formula '[Account].[Accounts].&[27]' refers to ‘Current Liabilities’<br /><br /><strong>Case #2 Result:</strong> You would notice that it returned Null value against my member ‘CheckThis’. I am expecting Amount $3,588,650.00<br /><br />Issue that I've posted & corresponding Microsoft response can be seen from below URL </span></div><br /><div align="left"><br /><a href="https://connect.microsoft.com/SQLServer/feedback/ViewFeedback.aspx?FeedbackID=436036"><span style="font-family:verdana;">https://connect.microsoft.com/SQLServer/feedback/ViewFeedback.aspx?FeedbackID=436036</span></a><span style="font-family:verdana;"> </span></div>AshokDugaputihttp://www.blogger.com/profile/14921707193483360012noreply@blogger.com0tag:blogger.com,1999:blog-8609672693854716141.post-75780592715768179352009-08-18T06:49:00.000-07:002009-08-18T07:01:53.999-07:00Expert Cube Development with Microsoft SQL Server 2008 Analysis Services<a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEgatsqT1C13Kx-PJPo_rNGoxDyA5YWd8IrTgZ2JNlS_LTqQeaVU1sQHeJZYD9kKB3tPjASXHcONsyI0zDkC2f3dVbdsJ98XaIMVcPBn-KFK-PpbMF8M4itkzQGSO1zX5QTKBW0UbjFuQ0HP/s1600-h/ExpertCubeDevelopment.png"><img id="BLOGGER_PHOTO_ID_5371303636268627826" style="WIDTH: 168px; CURSOR: hand; HEIGHT: 200px" alt="" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEgatsqT1C13Kx-PJPo_rNGoxDyA5YWd8IrTgZ2JNlS_LTqQeaVU1sQHeJZYD9kKB3tPjASXHcONsyI0zDkC2f3dVbdsJ98XaIMVcPBn-KFK-PpbMF8M4itkzQGSO1zX5QTKBW0UbjFuQ0HP/s200/ExpertCubeDevelopment.png" border="0" /></a><br /><div>I got to read the newly published book called "Expert Cube Development with Microsoft SQL Server 2008 Analysis Services " by Marco Russo, Alberto Ferrari, Chris Webb".<br /><br />This is a very good book. this book does not talk about SSAS 2008 concepts but practical experience & expertise of Marco Russo, Alberto Ferrari, Chris Webb.<br />Most interestingly, they discussed about Calculation dimension to provide flexibility to define the MDX expressions and use rest of the all the dimensions on rows/columns or pages. This is something that I have been using for a long time in my Reporting cube and I am exicited to see about the same in their book.<br />Another intesting topic is that with SSAS 2005 , it is quite confusing whether to use "CustomRollupColumn" or "MDX Scripts" for custom members. They have niced touched based about the same.<br />Planing to read further and planning to update the blog with some more intesting things that I find.</div>AshokDugaputihttp://www.blogger.com/profile/14921707193483360012noreply@blogger.com0tag:blogger.com,1999:blog-8609672693854716141.post-49553584357184954982009-07-24T02:11:00.000-07:002009-07-24T02:30:21.342-07:00Display currency symbol based on countryLast week, I got to do a little bit research on displaying different currency symbols based on a country or currency member.<br /><br />I learnt that SSAS 2005 supports Language () MDX function, which takes care of displaying measures database based on language (currency) settings that are setup in SCOPE() Expression. <strong></strong><br /><strong></strong><br /><strong>1) Hard code language to locale mapping</strong><br /><span style="font-size:85%;">Using BIDS, I've opened my cube and navigated to Calculation tab Switch to "Script View" and put the following expression<br /><br />Optin #1: Using Scope expression, in this approach you can include multiple company members.<br /><br />SCOPE ([Company].&[Germany]);<br />Language(This) = 2067;<br />END SCOPE;<br /><br />SCOPE ([Company].&[UK]);<br />Language(This) = 2057;<br />END SCOPE;<br /><br />Optin #1: Without using Scope expression</span><br />In this approach you can specify the locale to language mapping for only one company.<br />Language([Company].&[Germany]) = 2067;<br />Language([Company].&[UK]) = 2057;<br /><br /><strong>Option 2: Dynamically provide mapping between language to locale</strong><br />Option #1 can be made dynamic by creating an attribute on company dimension and storing the Locale ID.<br /><br />Create an attribute on Company and assign the attribute to Language expression in Calc script<br /><br />Language(This) = [Company].[Currency Locale ID];<br /><br />Or<br />Language(This) = [Company].Currentmember.Properties("Currency Locale ID");AshokDugaputihttp://www.blogger.com/profile/14921707193483360012noreply@blogger.com0tag:blogger.com,1999:blog-8609672693854716141.post-34353104406112439782009-04-30T03:46:00.000-07:002009-04-30T03:48:52.884-07:00MDX to get the Lineage and All ancestorsFollowing is the MDX written to get the Lineage (Parents path in terms of key separated by slash) and parent path with name of all members in the dimension.<br /><br />WITH <br /> MEMBER [Measures].[AncestorNames] AS <br /> Generate <br /> ( <br /> Ascendants([Employees].CurrentMember) <br /> ,[Employees].CurrentMember.Name <br /> ,"->" <br /> ) <br /> MEMBER [Measures].[Lineage] AS <br /> Generate <br /> ( <br /> Ascendants([Employees].CurrentMember) <br /> ,[Employees].CurrentMember.Properties("key") <br /> ,"/" <br /> ) <br />SELECT <br /> { <br /> [Measures].[AncestorNames] <br /> ,[Measures].[Lineage] <br /> } ON COLUMNS <br />,{[Employee].[Employees].MEMBERS} ON ROWS <br />FROM [Adventure Works];<br /><br /><a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEheVcDafIzslDycSt4LB2nb5MVZwt8eLC68gq2EYJQgbLmK54NR7rECAlXyxhWcHpHZ6OUwCH-1yBD1hTwwsx1urB6ckvLThW-v2IKdAcy4R_D55CpvGQIhIMB4vJUN_pBP4yyD1QyQD0xJ/s1600-h/clip_image002.jpg"><img style="cursor:pointer; cursor:hand;width: 320px; height: 138px;" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEheVcDafIzslDycSt4LB2nb5MVZwt8eLC68gq2EYJQgbLmK54NR7rECAlXyxhWcHpHZ6OUwCH-1yBD1hTwwsx1urB6ckvLThW-v2IKdAcy4R_D55CpvGQIhIMB4vJUN_pBP4yyD1QyQD0xJ/s320/clip_image002.jpg" border="0" alt=""id="BLOGGER_PHOTO_ID_5330434485100861314" /></a>AshokDugaputihttp://www.blogger.com/profile/14921707193483360012noreply@blogger.com0tag:blogger.com,1999:blog-8609672693854716141.post-27206686011230684332009-04-29T09:16:00.000-07:002009-04-29T09:48:29.798-07:00MDX for searching dimensions membersI wrote a simple MDX to list out the Employees whose name contains 'RA' and get the corresponding Parent Key as well.<br />*************************************************************************<br />Select<br />Generate({Filter({[Employee].[Employees].members},Instr([Employee].[Employees].currentmember.Name,"RA")>0)} ,Ascendants( [Employee].[Employees].currentmember))<br />DIMENSION PROPERTIES [Employee].[PARENT_UNIQUE_NAME] on rows,<br />{} on columns<br />From [Adventure Works]<br />*************************************************************************AshokDugaputihttp://www.blogger.com/profile/14921707193483360012noreply@blogger.com0