ssas - Last Available value MDX -


i have requirement in extract data cube, within ssrs dataset using query builder ,with time dimension in result set, across range of dates. conditions

  1. the measures displayed each day of date range.
  2. the sub total row should have last available measures value time range.
  3. there time filter (currently single date filter multi select option).

my mdx below.

  1. the measure has 'sum' aggregation type.

  2. i have calculated measure scope defined below.

     scope([measures].[measure1]);        scope([date].[date].members);                    = tail(existing ([date].[date].members),1).item(0)  ;                 end scope;    end scope;

this above scope statement works perfectly. however, when select in more 1 date member query slows wayyyyyyy down. performance numbers

  1. across 1 date - 4 seconds
  2. across 2 dates - 22 minutes
  3. across 3 dates - unknown (in hours)

this drastic degradation in performance goes away if remove scope statement, makes me thing there should better way same. final report query below.

select   non empty     {[measures].[measure1]} on columns     ,non empty     { [dimension1].[dimension1].[dimension1].allmembers*           [dimension2].[dimension2].[dimension2].allmembers*          [dimension3].[dimension3].[dimension3].allmembers*          [date].[date].[date].allmembers       } on rows (       select {[date].[date].&[2014-06-13t00:00:00]               ,[date].[date].&[2014-06-16t00:00:00] } on columns       [cube]       )

so question again is, is there way last available value part of scope statement have better performance? also, if there way write final mdx performance?.

please let me know if there anythings unclear regarding question.

thanks srikanth

the first optimization step change query to

select   non empty     {[measures].[measure1]} on columns     ,non empty     { [dimension1].[dimension1].[dimension1].allmembers*           [dimension2].[dimension2].[dimension2].allmembers*          [dimension3].[dimension3].[dimension3].allmembers*          {[date].[date].&[2014-06-13t00:00:00], [date].[date].&[2014-06-16t00:00:00] }     } on rows [cube]

furthermore, not sure why added scope([date].[date].memeber); (probably date].[date].members, actually). maybe helps omit , corresponding end scope.


Comments

Post a Comment

Popular posts from this blog

javascript - RequestAnimationFrame not working when exiting fullscreen switching space on Safari -

request animation frame stops working when exiting fullscreen clicking on original space's safari window. fine if fullscreen mode canceled escape key or calling cancelfullscreen(). steps reproduce: open https://dl.dropboxusercontent.com/u/769042/prezi/safari-fullscreen.html click "draw raf", kittie appears click "fullscreen", go fullscreen click "draw raf", kittie appears go space original safari was, showing "click exit fullscreen mode", click anywhere, out fullscreen click "draw raf", nothing happens what handling click calling: window.requestanimationframe(draw); which draws on canvas context: function draw() { ctx.drawimage(img, math.random()*500|0, math.random()*400|0, 100, 100); } i checked .hidden , .visibilitystate, updated correctly. tested on osx 10.9.3, safari 7.0.4 (9537.76.4). has workaround/solution other switching old settimeout? this sounds webkit bug 88940 : using reque
Read more

Python ctypes access violation with const pointer arguments -

i have api i'm trying wrap in python (2.7.6 on win7) code using ctypes. here's api: client_dllfunc bool clientapi search_exporttoclipcopy(clienthsearch handle, int channel, lpctstr filename, const time_t& from, const time_t& to, const bool* cameras, int length, bool usepassword, lpctstr password, bool includetextin = false, bool excludeplayer = false); the issue i'm having cameras argument; , i've had issue other const pointer arguments well. here's how i'm wrapping api: def search_exporttoclipcopy(self, hsearch, csearch, filename, tfrom, tto, cameras, length, usepassword, password, includetextin=true, excludeplayer=false): exporttoclipcopy = self.sdkdll.search_exporttoclipcopy exporttoclipcopy.argtypes = [c_int, c_int, c_wchar_p, c_long, c_long, pointer(c_bool), c_int, c_bool, c_wchar_p, c_bool, c_bool] exporttoclipcopy.restype = c_bool exporttoclipcopy(hsearch, csearch, filename, tfrom, tto, cameras, length, usepassword, pass
Read more