Semi-Additive Measure Aggregation


 

hi,

 

i'm tearing hair (what's left of it!) out trying achieve in ssas 2005, , wonder if can before go mad! basic scenario i'm trying model is thus:

 

funds, made of number of:

investments, made of number of:

allocations, represent unit holdings.

 

now there may many allocations per investment, , each of these allocations (hence unit holdings) may change @ various points in time. fact table i'm using has entries representing current unit holding @ given date. example of fact table i'm using:

 

date            fund id  investmentid   allocationid   unitholding

2008-08-01        1                1                     1                     10

2008-08-03        1                1                     1                     20 (representing 10 unit holding increase)

2008-08-07        1                1                     1                     15 (representing 5 unit holding decrease)

2008-08-01        1                1                     7                     30

2008-08-15        1                1                     7                     10 (representing 20 unit holding decrease)

2008-08-21        1                2                     9                     15

2008-08-25        1                2                     9                     10 (representing a 5 unit holding decrease)

 

hence, if i wish find unit holdings given day, interested in lastnonempty value unitholding measure. instance, if want see unit holding allocation 1 on, say, 2008-08-10, value of 15. fine. however, want achieve, if want see unitholding @ lower level of granularity, investment, hope see sum of lastnonempty values each constituent allocation. in example, investmentid1 on 2008-08-20, hope see unitholding of 15 + 10 = 25. seeing, value of 10, since, guess, lastnonempty value investmentid 1. likewise upward levels of granularity.

 

so, in summary, i'm trying achieve measure uses lastnonempty aggregation @ highest level of granularity, these values summed @ lower granularities in hierarchy. have thought pretty standard functionality (say aggregating number of items held in stock across number of sites @ point in time, say), i'm feeling bit mr. thicky having particularly thick day @ moment , getting fast, amazingly welcome!

 

many thanks,

 

rob

 

if correct, trying have different aggregations measure @ different levels of dimension. can't think of solution except using calculated member.


SQL Server  >  SQL Server Analysis Services



Comments

Post a Comment

Popular posts from this blog

SQL server replication error Cannot find the dbo or user defined function........

hi there, i ran problem while deploying sql 2008 transactional replication between 2 servers lets assume server , b. actually created  both distributor , publisher on server , after created subscriber pointed server b, launch replication monitor , found big red cross. when click on tab "distributor subscriber history" error says "cannot find either column "dbo" or user defined-function or aggregate "dbo.ufnleadingzeros", or name ambiguous". can body regarding issue. regards, ishuv hi, you need compare data , others reseved, index size, unused should not compared. differs. here server - data - 87896 kb server b - data - 78152 kb server data size includes publication tables, can't compare directly. need check article article know exact data size. SQL Server  >  ...
Read more

BIT Version

  hi,     knows how find out ther bit version in sql  , windows os (32 or 64)   regards, s.balavenkatesh   microsoft sql server 2005 - 9.00.1399.06 (intel x86) oct 14 2005 00:33:37 copyright (c) 1988-2005 microsoft corporation standard edition on windows nt 5.2 (build 3790: service pack 2)     intel x86)   -- says sql server 32 bit   madhu SQL Server  >  SQL Server Tools
Read more

Admin Permissions

hi need create new 'role' following permissions, 1. access sql server logs. 2. access maintenance plans ( can avoid access) 3. access activity monitor 4. normal access product databases , not system db´s need guidance on how it, can point me in right direction please? thanks 1.grant  execute on xp_readerrorlog [domainname\ad user or group]; 2. http://technet.microsoft.com/en-us/library/ms191002.aspx 3.view server state permission 4. make user member of dbo_owner database role. best regards,uri dimant sql server mvp, http://sqlblog.com/blogs/uri_dimant/ ms sql optimization: ms sql development , optimization ms sql consulting: large scale of database , data cleansing remote dba services: improves ms sql database performance sql server integration services: business intelligence SQL Server  >  ...
Read more