Re: On what OLAP can and what OLAP can't - A little problem

[reposted because used count instead of sum]

 Alexandr Savinov ha scritto:

> I am afraid that it is too general formulation. Are you sure that
> numerous existing approaches to data modeling cannot do what you
need?

 All decent reporting programs are able to deal with any database. Since
 I was thinking to create as an exercise a reporting tool, I was trying  to understand how to do that. Programs must be able to deal with  anything.

 A relatively naive reporting program would probably spit out something  like:

SELECT

       C.[Name] AS [Name],
       sum(T1.Amount) AS [Tot_A],
       sum(T.Amount) AS [Tot_B],
       T.ItemB AS [ItemB],
       T1.ItemA AS [ItemA]
FROM
       Trans_A T1,
       Client C,
       Trans_B T
WHERE
       C.[Name] = T1.[Name] AND
       C.[Name] = T.[Name]
GROUP BY
       C.[Name],
       T.ItemB,
       T1.ItemA;

 where instead, if I understand your suggestion, it would be better to  give:

SELECT

       C.[Name] AS [Name],
       sum(T1.Amount) AS [Tot_A],
       NULL AS [Tot_B],
       NULL AS [ItemB],
       T1.ItemA AS [ItemA]
FROM
       Trans_A T1,
       Client C
WHERE
       C.[Name] = T1.[Name]
GROUP BY
       C.[Name],
       T1.ItemA;

       C.[Name] AS [Name],
       NULL AS [Tot_A],
       sum(T.Amount) AS [Tot_B],
       T.ItemB AS [ItemB],
       NULL AS [ItemA]
FROM
       Trans_B T,
       Client C
WHERE
       C.[Name] = T.[Name]
GROUP BY
       C.[Name],
       T.ItemB;

 or perhaps the possibility to execute the 2 subqueries separately.

>
> Dimension is the same as degree of freedom. It is the number of
paths
> from bottom to top if you order your tables accordingly. This order
then
> is important for all the operations with data and its semantics. In
my
> diagram if table A references table B then it is drawn below:
>
> B
> |
> A
>
> it is always many-to-one relationship, i.e., many a in A reference
one b
> in B. This ordering can be then used for operations you mentioned:
> aggregation (higher levels are general while lower levels are
specific),
> relationships (a common subtable is a relationship for its
supertables)
> and so on...
 

 Thanks, it's nice to learn that.  

> http://conceptoriented.com
Received on Wed Sep 13 2006 - 16:35:53 CEST