Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums  SQL Server 2000 Forums  Analysis Services (2000)  Can't find aggreg method

Author  Topic  skif Starting Member 1 Post Posted - 2002-05-14 : 12:44:12 A project of a cube reduces to the standard analysis task “publishers” -> “authors”. In a data base there are tables “time” (as dimension) and “publishers” (as dimension). In both Dimensions level [ALL] exists. There is table “authors”. Authors collaborate with publishers. Each author can collaborate with different publishers at the same time. That author can collaborate with same publisher through different contracts. An author can pause collaborating with a publisher, at which time the status becomes the status becomes as “passive” (at renewal that collaboration the status becomes as “active”). When an author breaks off contracts that author is struck off list of links with publisher and the amount of authors has to be reduced by 1. The tasks: 1. Count up amount of authors. 2. Count up amount of “active” authors. To solve the first task we prepare a table of facts: auth_id time publ 0000001 xxxx xxxx When we create the Measure “amount” is Distinct Count by auth_id. At that we get exact values in any view. To look at the cube we see: Time... 2000 All 250 1th quarter All 100 January All 30 01 2 02 3 That would be nice if somebody suggests how to place there increasing values to have another view: .... January All 30 01 2 02 5 (2+3) To solve the second task this schema is not appropriate. To do this we change the principle of counting the amount of authors. We add a new schema to the table of facts. auth_id time publ activity 0000001 xxxx xxxx 1 -> when the contract is signed; 0000001 xxxx xxxx -1 -> when the contract is paused/broken off; When we create measures “amount of active authors” we use SUM by “activity”. SUM exactly, not COUNT because COUNT never gives reduction of number. Everything is fine but what would be if the author has contracts with two or more publishers? In this case in publisher’s view everything is fine, but in the summation there are problems. Here the condition “one author can have several contracts with one publisher” shows in any publisher’s case could be a mistake. For example: auth_id time publ activity 0000001 xxxx 01 1 -> when the contract 01 was signed 0000001 xxxx 01 1 -> when the contract 02 was signed As a result the count increases by one. At the first signing event an Activity is written as 1, the next event would be 0, which will not increase the number of authors. When an author pauses or stops a contract the situation will be: auth_id time publ activity contr_type_id 0000001 xxxx 01 1 01 -> when the contract 01 was signed 0000001 xxxx 01 0 02 -> when the contract 02 was signed 0000001 xxxx 01 0 02 -> when the contract 02 was paused we don’t reduce the amount, it wasn’t broken off 0000001 xxxx 01 -1 01 -> when the contract 01 was paused In this case, if prepare the table in a special way we have exact result of amount of active authors in general, but not as in views by contr_type_id (mathematics, english)… Here I see just one way – leave just “time” as dimension. In this case we can not fix the authors activity but it is not good. I see one more chance: Create additional Dimension “author”. For example there are author’s ID and it is hidden in the cube. Then create there a Property (“Status”). But something is wrong anyway because the status property constantly changes in real time. And we have to use the “slowly changing dimensions” technology although the statuses is “often changed”. That increases the Dimension by 2,3 times and it is bad when there are 5-10 millions authors. But in other case here appears some kind of distinctness in searching “active” authors. I can try COUNT( FILTER([Author].members, [Measure].[activity] = 1)), although it is not correct because it is necessary daily insert data and confirm statuses for this solution. Maybe I have to try CrossJoin. What do you think about that? Thank you in advance.