Calculating number of subscriptions in time


hi all,

i'm new sql , mdx in general i'm trying build following , maybe guys can me.

i have snowflake database table containing subscription. each subscription has date/time @ starts , date/time @ terminated:

subscription:

id start_date end_date ....
100 10-jul-2005 17:00 10-aug-2005 11:00 ...
101 11-jun-2005 17:00 12-dec-2006 18:00 ...
102 1-may-2005 17:00 17-jan-2007 13:00 ...
103 10-apr-2005 17:00 10-sep-2005 15:00 ...

and there dimension-table called datetime:
id year month ....
10-jul-2005 17:00 2005 06 ...
10-jul-2005 18:00 2005 06 ...
... .. .. ...
11-jul-2005 17:00 2005 06 ...
11-jul-2005 18:00 2005 06 ...


as generate count-measures start-date , end-date (e.g. number of subscriptions started @ given moment: 1 started @ 10-jul-2005 17:00). want know how many subscription active @ given moment (e.g. 0 @ 1-jan-2005 12:00, 2 @ 1-jan-2006 12:00 , 4 @ 1-aug-2005 12:00). in sql i produce 'select count(*) subscription measuredate between start_date , end_date' but how create in as using mdx?

tia,
ferdinand

well, in report there 1 date number of subscriptions, there isn't start_date , end_date. assume means number of active subscriptions @ date. standard way of doing maintaining in fact table measure "number of subscriptions", , put 1 when there activation , -1 when there termination. implementing running sum calculation on lowest level of date dimension - need. this problem same as classic inventory management problem - can read more here: http://www.sqlserveranalysisservices.com/olappapers/inventorymanagement%20in%20as2005v2.htm

 



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