union - Fast MDX query for one row per-hierarchy on shared dimension -


working on ssas multidimensional cube includes usergroups dimension made of multiple hierarchies. each hierarchy classifies users belonging particular group or not. users can belong more 1 group.

trying generate table 1 row each hierarchy in dimension. best approach have come following:

with      member [measures].[group name]         case             when [usergroups].[group a].currentmember.membervalue <> 'all' "group a"             when [usergroups].[group b].currentmember.membervalue <> 'all' "group b"             -- more user groups...             when [usergroups].[group t].currentmember.membervalue <> 'all' "group t"             else "error"         end select  {[members].[group name], [measures].[user count], [measures].[revenue]} on 0,  order(     union(          [usergroups].[group a].&[true] *         {[usergroups].[group b].[all]} *         -- more user groups...         {[usergroups].[group t].[all]},            [usergroups].[group a].[all] *         {[usergroups].[group b].&[true]} *         -- more user groups...         {[usergroups].[group t].[all]},          -- bunch more blocks there 1 each row.           [usergroups].[group a].[all] *         {[usergroups].[group b].[all]} *         -- more user groups...         {[usergroups].[group t].&[true]}     ), ) on 1  [finances];

this succeeds in generating expected table:

                    group name  user count  revenue   ... true  group t     90          1800 ...  ...  ... ...   ...         ...         ...  true ...   group b     20          400 true  ...   group     10          100

however, query slow. given row can calculated in isolation in 30 seconds. when combining rows using union statement, each additional row seems result in exponential increase in computation time rather desired linear increase in computation time.

  • why increase in time observed?
  • is there way instruct underlying engine compute each row in isolation?
  • is there better way issue query?

i guess you've experimented rearranging query - if take union(...) out of select , make named set in clause help?

with     set [rowset]        union(          [usergroups].[group a].&[true] *         {[usergroups].[group b].[all]} *         -- more user groups...         {[usergroups].[group t].[all]},            [usergroups].[group a].[all] *         {[usergroups].[group b].&[true]} *         -- more user groups...         {[usergroups].[group t].[all]},          -- bunch more blocks there 1 each row.           [usergroups].[group a].[all] *         {[usergroups].[group b].[all]} *         -- more user groups...         {[usergroups].[group t].&[true]}     )     member [measures].[group name]         case             when [usergroups].[group a].currentmember.membervalue <> 'all' "group a"             when [usergroups].[group b].currentmember.membervalue <> 'all' "group b"             -- more user groups...             when [usergroups].[group t].currentmember.membervalue <> 'all' "group t"             else "error"         end select    {[members].[group name], [measures].[user count], [measures].[revenue]} on 0,    order(     [rowset], ) on 1 [finances];

what purpose of order function? cannot see ordering by?

again shot in dark think i've noticed differences in efficiency between using union , using + operator - option:

with     set [rowset]        {          [usergroups].[group a].&[true] *         {[usergroups].[group b].[all]} *         -- more user groups...         {[usergroups].[group t].[all]}        }       +        {          [usergroups].[group a].[all] *         {[usergroups].[group b].&[true]} *         -- more user groups...         {[usergroups].[group t].[all]}        }          -- bunch more blocks there 1 each row.       +        {          [usergroups].[group a].[all] *         {[usergroups].[group b].[all]} *         -- more user groups...         {[usergroups].[group t].&[true]}        }     member [measures].[group name]       ...       ...

apologies don't have definites - above options might lucky!! best discuss in detail can greg able give solid advise on possible amendments cube design.


Comments

Post a Comment

Popular posts from this blog

c# - Update a combobox from a presenter (MVP) -

i using mvp in project, new in mvp. have 2 comboboxes. when select option in combobox, combobox should filled new data. this action in presenter. view 'view1' in presenter, , introduced combobox1 , combobox2 properties in 'view1', because need 'datasource', 'displaymember', 'valuemember' , 'refresh()' in method below. but, when using pattern, enough send property like public string combobox2 { { return combobox1.selectedvalue.tosstring(); } } into presenter not whole combobox. how can solve problem? public void onselectedindexchangedcombobox1() { if (view1.combobox1.selectedindex == -1) { return; } datatable dt = tools.getdatatable("a path"); var query = (from o in dt.asenumerable() o.field<string>("afield") == farmerview.combobox1.selectedvalue.tostring() orderby o.field<string>("anotherfield") select...
Read more

How to understand 2 main() functions after using uftrace to profile the C++ program? -

i trying uftrace profile following simple c++ program: #include <iostream> class { public: a() {std::cout << "a created" << std::endl;} ~a() {std::cout << "a destroyed" << std::endl;} }; int main() { a; return 0; } the profile result this: # uftrace a.out created destroyed # duration tid function 2.026 [ 4828] | __cxa_atexit(); [ 4828] | main() { [ 4828] | __static_initialization_and_destruction_0() { 89.397 [ 4828] | std::ios_base::init::init(); 0.768 [ 4828] | __cxa_atexit(); 93.029 [ 4828] | } /* __static_initialization_and_destruction_0 */ 94.425 [ 4828] | } /* main */ [ 4828] | main() { [ 4828] | a::a() { 11.104 [ 4828] | std::operator <<(); 10.825 [ 4828] | std::basic_ostream::operator <<(); 24.514 [ 4828] | } /* a::a */ [ 4828] | a::~a() { 0.978 [ 4828] | ...
Read more

How to put a lock and transaction on table using spring 4 or above using jdbcTemplate and annotations like @Transactional? -

i trying put lock on table while writing on table , if happened in between roll-back . trying convert below code lock table test_g1 read; lock table test_g write; -- begin; start transaction; insert test_g1 values(143); insert test_g values(145); select * test_g1; select * test_g; rollback; select * test_g; unlock tables; how convert above code @transactional spring jdbctemplate code? @transactional(rollbackfor={dataaccessexception.class}) public void test(){ jdbctemplate.execute("insert test1 (id, nam) values (4, 'a')"); throw new dataaccessexception("error") { }; } here trying throw error, insert statement should rollback it's not happening . thanks edit-1 attaching code , doing in jdbcdaoimpl.java , mentioned problem comment above test() . app.java package com.cgiri.javabrains.spring4; import org.springframework.beans.factory.annotation.autowired; import org.springframework.context.applic...
Read more