{"id":30,"date":"2007-08-26T20:53:15","date_gmt":"2007-08-27T01:53:15","guid":{"rendered":"http:\/\/www.ssas-info.com\/VidasMatelisBlog\/30_ssas-2008-katmai-mdx-changes"},"modified":"2008-03-17T21:03:26","modified_gmt":"2008-03-18T02:03:26","slug":"ssas-2008-katmai-mdx-changes","status":"publish","type":"post","link":"http:\/\/www.ssas-info.com\/VidasMatelisBlog\/30_ssas-2008-katmai-mdx-changes","title":{"rendered":"SSAS 2008 Katmai &#8211; MDX Changes"},"content":{"rendered":"<p>Mosha Pasumansky just posted blog entry about <a target=\"_blank\" href=\"http:\/\/sqljunkies.com\/WebLog\/mosha\/archive\/2007\/08\/24\/dynamic_named_sets.aspx\">dynamic named sets<\/a>\u00a0in SSAS 2008. In his post Mosha\u00a0listed 4 SSAS changes that were included in June CTP release:<\/p>\n<ul>\n<li>Dynamic named sets<\/li>\n<li>CREATE MEMBER statement extension to allow specify display folder and associated measure group<\/li>\n<li>New CREATE KPI statement<\/li>\n<li>Ability to change calculated member &#8220;on the fly&#8221;<\/li>\n<\/ul>\n<p>As Mosha focused on changes\u00a0with Dynamic named sets,\u00a0I decided to do a quick test on other 3 changes. To be honest, just from Mosha&#8217;s blog entry I realised that these MDX changes are already in CTP. I knew that some of these changes were planned, but from webcasts and chat sessions I was under impression that these changes will be available just in the future releases. Is it possible that these changes were included just in July CTP?<\/p>\n<p>So here are my tests on other changes.\u00a0<\/p>\n<p><!--more-->CREATE MEMBER now allows to specify Display folder (property DISPLAY_FOLDER) and associated measures group (property ASSOCIATED_MEASURE_GROUP). It is very simple\u00a0but very welcome change. All of my calculated measures were always associated with\u00a0measure group and most of calculated measures were placed in the folders. In SSAS 2005 I was doing assignments manually and any calculated measure named changes required manual reassignment. In SSAS 2008 folder and measure group assignment is now part of the create code and it is much easier to maintain. Here is a example of how to use new properties:<\/p>\n<blockquote><p>CREATE<font size=\"2\"> <\/font><font size=\"2\" color=\"#0000ff\">MEMBER<\/font><font size=\"2\"> <\/font><font size=\"2\" color=\"#0000ff\">CurrentCube<\/font><font size=\"2\">.[Measures].[Calculated Internet Sales Amount] <\/font><font size=\"2\" color=\"#0000ff\">AS<br \/>\n<\/font><font size=\"2\">[Measures].[Internet Sales Amount]<br \/>\n, <\/font><font size=\"2\" color=\"#0000ff\">NON_EMPTY_BEHAVIOR<\/font><font size=\"2\"> = {[Internet Sales Amount]}<br \/>\n, <\/font><font size=\"2\" color=\"#0000ff\">DISPLAY_FOLDER<\/font><font size=\"2\"> = <\/font><font size=\"2\" color=\"#800000\">&#8216;NET Amounts&#8217;<br \/>\n<\/font><font size=\"2\">, <\/font><font size=\"2\" color=\"#0000ff\">ASSOCIATED_MEASURE_GROUP<\/font><font size=\"2\"> = <\/font><font size=\"2\" color=\"#800000\">&#8216;Internet Sales&#8217;<\/font><font size=\"2\">;<\/font><\/p><\/blockquote>\n<p>Note: if you specify non existing\u00a0measure group name for ASSOCIATED_MEASURE_GROUP property, you will get error during deployment: &#8220;The\u00a0Internet Sales 2\u00a0measure group was not found&#8221;.<\/p>\n<p>There is\u00a0new CREATE KPI\u00a0statement that allows to\u00a0move KPI definitions into common calculation tab area. I do like having my all calculations in one single place, as to me KPI is just another calculated measure that has more properties (value, goal, status, trend, etc). This statement is well documented in BOL, and here is example:<\/p>\n<blockquote><p>CREATE<font size=\"2\"> <\/font><font size=\"2\" color=\"#0000ff\">KPI<\/font><font size=\"2\"> [Adventure Works].[Internet Sales KPI]<br \/>\n<\/font><font size=\"2\" color=\"#0000ff\">AS<\/font><font size=\"2\"> ([Measures].[Internet Sales Amount], [Date].[Calendar Year].<\/font><font size=\"2\" color=\"#800000\">LastChild<\/font><font size=\"2\">)<br \/>\n, <\/font><font size=\"2\" color=\"#0000ff\">GOAL<\/font><font size=\"2\"> = ([Measures].[Internet Sales Amount], [Date].[Calendar Year].<\/font><font size=\"2\" color=\"#800000\">LastChild<\/font><font size=\"2\">.<\/font><font size=\"2\" color=\"#800000\">PrevMember<\/font><font size=\"2\">) * 1.10 <\/font><font size=\"2\" color=\"#008000\">&#8212; 3% goal is 3% over previous year<br \/>\n<\/font><font size=\"2\">, <\/font><font size=\"2\" color=\"#0000ff\">STATUS<\/font><font size=\"2\"> = <\/font><font size=\"2\" color=\"#800000\">IIF<\/font><font size=\"2\">(([Measures].[Internet Sales Amount], [Date].[Calendar Year].<\/font><font size=\"2\" color=\"#800000\">LastChild<\/font><font size=\"2\">)<br \/>\n\u00a0\u00a0 &gt;= ([Measures].[Internet Sales Amount], [Date].[Calendar Year].<\/font><font size=\"2\" color=\"#800000\">LastChild<\/font><font size=\"2\">.<\/font><font size=\"2\" color=\"#800000\">PrevMember<\/font><font size=\"2\">) * 1.10<br \/>\n\u00a0\u00a0\u00a0\u00a0 , 1<br \/>\n\u00a0\u00a0\u00a0\u00a0 , <\/font><font size=\"2\" color=\"#800000\">IIF<\/font><font size=\"2\">( ([Measures].[Internet Sales Amount], [Date].[Calendar Year].<\/font><font size=\"2\" color=\"#800000\">LastChild<\/font><font size=\"2\">)<br \/>\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 &gt;= ([Measures].[Internet Sales Amount], [Date].[Calendar Year].<\/font><font size=\"2\" color=\"#800000\">LastChild<\/font><font size=\"2\">.<\/font><font size=\"2\" color=\"#800000\">PrevMember<\/font><font size=\"2\">) * 1.10 * 0.9<br \/>\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 , 0, -1 ) )<br \/>\n, <\/font><font size=\"2\" color=\"#0000ff\">TREND<\/font><font size=\"2\"> = <\/font><font size=\"2\" color=\"#800000\">IIF<\/font><font size=\"2\">(([Measures].[Internet Sales Amount], [Date].[Calendar Year].<\/font><font size=\"2\" color=\"#800000\">LastChild<\/font><font size=\"2\">)<br \/>\n\u00a0\u00a0\u00a0\u00a0 &gt;= ([Measures].[Internet Sales Amount], [Date].[Calendar Year].<\/font><font size=\"2\" color=\"#800000\">LastChild<\/font><font size=\"2\">.<\/font><font size=\"2\" color=\"#800000\">PrevMember<\/font><font size=\"2\">) * 1.10<br \/>\n\u00a0\u00a0\u00a0 , 1<br \/>\n\u00a0\u00a0\u00a0 <\/font><font size=\"2\">, <\/font><font size=\"2\" color=\"#800000\">IIF<\/font><font size=\"2\">( ([Measures].[Internet Sales Amount], [Date].[Calendar Year].<\/font><font size=\"2\" color=\"#800000\">LastChild<\/font><font size=\"2\">)<br \/>\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 &gt;= ([Measures].[Internet Sales Amount], [Date].[Calendar Year].<\/font><font size=\"2\" color=\"#800000\">LastChild<\/font><font size=\"2\">.<\/font><font size=\"2\" color=\"#800000\">PrevMember<\/font><font size=\"2\">) * 1.10 * 0.9<br \/>\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 , 0, -1))<br \/>\n, <\/font><font size=\"2\" color=\"#0000ff\">STATUS_GRAPHIC<\/font><font size=\"2\"> = <\/font><font size=\"2\" color=\"#800000\">&#8216;Traffic Light&#8217;<\/font><font size=\"2\"> <\/font><font size=\"2\" color=\"#008000\">&#8212; Shapes (3), Traffic Light (3), Road Signs (3), Gauge (3), Reversed Gauge (5), Thermometer (3), Cylinder (3), Faces (3), Variance arrow (3)<br \/>\n<\/font><font size=\"2\">, <\/font><font size=\"2\" color=\"#0000ff\">TREND_GRAPHIC<\/font><font size=\"2\"> = <\/font><font size=\"2\" color=\"#800000\">&#8216;Standard Arrow&#8217;<\/font><font size=\"2\"> <\/font><font size=\"2\" color=\"#008000\">&#8212; Standard Arrow (3), Status Arrow (3), Reversed Arrow (3), Reversed status arrow (5), Faces (3)<br \/>\n\/\/ , WEIGHT = 1<br \/>\n<\/font><font size=\"2\">, <\/font><font size=\"2\" color=\"#0000ff\">CURRENT_TIME_MEMBER<\/font><font size=\"2\"> = [Date].[Date].<\/font><font size=\"2\" color=\"#800000\">LastChild<br \/>\n<\/font><font size=\"2\">, <\/font><font size=\"2\" color=\"#0000ff\">PARENT_KPI<\/font><font size=\"2\"> = <\/font><font size=\"2\" color=\"#800000\">&#8221;<br \/>\n<\/font><font size=\"2\">, <\/font><font size=\"2\" color=\"#0000ff\">CAPTION<\/font><font size=\"2\"> = <\/font><font size=\"2\" color=\"#800000\">&#8216;Internet Sales KPI&#8217;<br \/>\n<\/font><font size=\"2\">, <\/font><font size=\"2\" color=\"#0000ff\">DISPLAY_FOLDER<\/font><font size=\"2\"> = <\/font><font size=\"2\" color=\"#800000\">&#8216;KPIs&#8217;<br \/>\n<\/font><font size=\"2\">, <\/font><font size=\"2\" color=\"#0000ff\">ASSOCIATED_MEASURE_GROUP<\/font><font size=\"2\"> = <\/font><font size=\"2\" color=\"#800000\">&#8216;Internet Sales&#8217;<br \/>\n<\/font><font size=\"2\">;<\/font><\/p><\/blockquote>\n<p>There is also new DROP KPI statement that allows to delete KPI.<\/p>\n<p>UPDATE MEMBER is a new statement that you can use to change calculated member value. From BOL: &#8220;The UPDATE MEMBER statement updates an existing calculated member while preserving the relative precedence of this member with respect to other\u00a0 calculations. Therefore, you cannot use the UPDATE MEMBER statement to change SOLVEORDER.\u00a0An UPDATE MEMBER statement cannot be specified in the MDX script for a cube.&#8221;<\/p>\n<p>For some reasons my tests to use UPDATE MEMBER statement kept failing with error &#8220;The member &#8216;MyCalcMeasure&#8217; was not found in the cube when the string, [Measures].[MyCalcMeasure], was parsed.&#8221;<\/p>\n<p>Update: <a target=\"_blank\" href=\"http:\/\/www.sqljunkies.com\/WebLog\/mosha\/\">Mosha<\/a> pointed out why my UPDATE MEMBER statement kept failing in comments below: &#8220;<em>That\u2019s because you probably tried to change calculated member defined inside MDX Script. You can only change the calculated member defined in the session scope by using the session UPDATE MEMBER command.<\/em>&#8221; So I tried this new statement on session calculated member and now this worked:<\/p>\n<blockquote><p><font size=\"2\" color=\"#0000ff\">CREATE<\/font><font size=\"2\"> <\/font><font size=\"2\" color=\"#0000ff\">CALCULATED<\/font><font size=\"2\"> <\/font><font size=\"2\" color=\"#0000ff\">MEMBER<\/font><font size=\"2\"> [Adventure Works].[Measures].[Calculated Internet Sales Amount] <\/font><font size=\"2\" color=\"#0000ff\">AS<\/font><font size=\"2\"> [Measures].[Internet Sales Amount];<br \/>\n<\/font><font size=\"2\" color=\"#0000ff\">SELECT<\/font><font size=\"2\"> [Measures].[Calculated Internet Sales Amount] <\/font><font size=\"2\" color=\"#0000ff\">ON<\/font><font size=\"2\"> 0<br \/>\n<\/font><font size=\"2\" color=\"#0000ff\">\u00a0 FROM<\/font><font size=\"2\"> [Adventure Works];<br \/>\n<\/font><font size=\"2\" color=\"#008000\">&#8212; Result: $29,358,677.22<br \/>\n<\/font><font size=\"2\" color=\"#0000ff\">UPDATE<\/font><font size=\"2\"> <\/font><font size=\"2\" color=\"#0000ff\">MEMBER<\/font><font size=\"2\"> [Adventure Works].[Measures].[Calculated Internet Sales Amount] <\/font><font size=\"2\" color=\"#0000ff\">AS<\/font><font size=\"2\"> 10;<br \/>\n<\/font><font size=\"2\" color=\"#0000ff\">SELECT<\/font><font size=\"2\"> [Measures].[Calculated Internet Sales Amount] <\/font><font size=\"2\" color=\"#0000ff\">ON<\/font><font size=\"2\"> 0<br \/>\n<\/font><font size=\"2\" color=\"#0000ff\">\u00a0 FROM<\/font><font size=\"2\"> [Adventure Works];<br \/>\n<\/font><font size=\"2\" color=\"#008000\">&#8211;Result: 10<br \/>\n<\/font><font size=\"2\" color=\"#0000ff\">DROP<\/font><font size=\"2\"> <\/font><font size=\"2\" color=\"#0000ff\">CALCULATED<\/font><font size=\"2\"> <\/font><font size=\"2\" color=\"#0000ff\">MEMBER<\/font><font size=\"2\"> [Adventure Works].[Measures].[Calculated Internet Sales Amount];<br \/>\n<\/font><\/p><\/blockquote>\n<p>While I was testing these new statements, I realized how much I miss &#8220;Deploy MDX&#8221; option that BIDS Helper utility gives to SSAS 2005 environment. I added suggestion to include &#8220;Deploy MDX&#8221; functionality in SSAS 2008, please vote it you believe this is important to you too:<\/p>\n<p><a href=\"https:\/\/connect.microsoft.com\/SQLServer\/feedback\/ViewFeedback.aspx?FeedbackID=295178\">https:\/\/connect.microsoft.com\/SQLServer\/feedback\/ViewFeedback.aspx?FeedbackID=295178<\/a><\/p>\n<p>I compiled a list of\u00a0known to me SSAS 2008 changes <a target=\"_blank\" href=\"http:\/\/www.ssas2008-info.com\/ssas_2008_change_summary.html\">here<\/a>.<\/p>\n","protected":false},"excerpt":{"rendered":"<p>Mosha Pasumansky just posted blog entry about dynamic named sets\u00a0in SSAS 2008. In his post Mosha\u00a0listed 4 SSAS changes that were included in June CTP release: Dynamic named sets CREATE MEMBER statement extension to allow specify display folder and associated measure group New CREATE KPI statement Ability to change calculated member &#8220;on the fly&#8221; As [&hellip;]<\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":[],"categories":[4,6],"tags":[],"aioseo_notices":[],"_links":{"self":[{"href":"http:\/\/www.ssas-info.com\/VidasMatelisBlog\/wp-json\/wp\/v2\/posts\/30"}],"collection":[{"href":"http:\/\/www.ssas-info.com\/VidasMatelisBlog\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"http:\/\/www.ssas-info.com\/VidasMatelisBlog\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"http:\/\/www.ssas-info.com\/VidasMatelisBlog\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"http:\/\/www.ssas-info.com\/VidasMatelisBlog\/wp-json\/wp\/v2\/comments?post=30"}],"version-history":[{"count":0,"href":"http:\/\/www.ssas-info.com\/VidasMatelisBlog\/wp-json\/wp\/v2\/posts\/30\/revisions"}],"wp:attachment":[{"href":"http:\/\/www.ssas-info.com\/VidasMatelisBlog\/wp-json\/wp\/v2\/media?parent=30"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"http:\/\/www.ssas-info.com\/VidasMatelisBlog\/wp-json\/wp\/v2\/categories?post=30"},{"taxonomy":"post_tag","embeddable":true,"href":"http:\/\/www.ssas-info.com\/VidasMatelisBlog\/wp-json\/wp\/v2\/tags?post=30"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}