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

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],
       count(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],
       count(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:29:19 CEST