Very Computer

I'm a beginner with OLAP and I've got a problem that I'm not able to solve.

This is the situation:

I've got a set of documents. Every document belongs to one or more SubArea.
Every SubArea belongs to an Area.

There are people that read these documents. People belongs to one or more
factory, the factory belongs to one or more "groups", the "groups" to one
"section".

There is a log that contains (Name of person, Document, date).

The target is:  create a cube with 3 dimensions : "Document", "People",
"Time" and a measure that counts the number of reading.

I've created the cube BUT  the problem is: the number of reading of an Area
is the sum of the reading of all its subareas. That's not what I want. I'd
like it to be The sum of the reading of all distinct files that belongs to
the Area. For example:

DIM AREA              #READ      What I'd like

Area1                          15                12

    SubArea1.1             10                10
        File1                    2                    2
        File2                    5                    5
        File3                    3                    3

    SubArea1.2             5                    5
        File3                    3                    3
        File4                    2                    2

Area2                        20                    18
    SubArea2.1           2                      2
        File2                  2                      2
    SubArea2.2          18                     18
        File2                  2                      2
        FIle6                 8                      8
        File7                  8                      8

It's possible to do this with only one cube?

Please HELP ME!!!!!!!!!!!!!!
Vanessa

Have you tried using DISTINCT COUNT rather than COUNT.

Aggregate Function for your READ measure of "Distinct Count" (Basic tab in
Cube Editor)

~Andrew.

Quote:> I'm a beginner with OLAP and I've got a problem that I'm not able to
solve.

> This is the situation:

> I've got a set of documents. Every document belongs to one or more
SubArea.
> Every SubArea belongs to an Area.

> There are people that read these documents. People belongs to one or more
> factory, the factory belongs to one or more "groups", the "groups" to one
> "section".

> There is a log that contains (Name of person, Document, date).

> The target is:  create a cube with 3 dimensions : "Document", "People",
> "Time" and a measure that counts the number of reading.

> I've created the cube BUT  the problem is: the number of reading of an
Area
> is the sum of the reading of all its subareas. That's not what I want. I'd
> like it to be The sum of the reading of all distinct files that belongs to
> the Area. For example:

> DIM AREA              #READ      What I'd like

> Area1                          15                12

>     SubArea1.1             10                10
>         File1                    2                    2
>         File2                    5                    5
>         File3                    3                    3

>     SubArea1.2             5                    5
>         File3                    3                    3
>         File4                    2                    2

> Area2                        20                    18
>     SubArea2.1           2                      2
>         File2                  2                      2
>     SubArea2.2          18                     18
>         File2                  2                      2
>         FIle6                 8                      8
>         File7                  8                      8

> It's possible to do this with only one cube?

> Please HELP ME!!!!!!!!!!!!!!
> Vanessa

If I use Distinct count I obtain the number of distinct files that belongs
to an Area.

With the following assumptions, I can think of a possible solution:

1) The Document name is unique, so the sample data below should have
"File5" instead of "File2" for Area2.

2) A File can belong to multiple SubAreas, but just to a single Area
(though the approach below can be extended if Files can also belong to
multiple Areas)

3) The "DimArea" dimension is built from a star schema table or view,
with the columns: "Area", "SubArea", "Document". The cube raw measure
is "NumberRead".

The approach is based on marking just the first instance of a Document
under a given Area tree (the instance with the lowest SubArea name in
this case). Then
only the NumberRead for the marked instance is counted for the Area or
higher:

- Define a SQL view on the DimArea table, with the new column:
"AddToArea"
create view DimAreavw as
select Area, SubArea, Document,
case (select count(*) from DimArea da2
      where da2.Document = da1.Document
      and da2.SubArea < da1.SubArea)
when 0 then 1 else 0 end as AddToArea
from DimArea da1

- Build the "DimArea" dimension from above view, and define
"AddToArea" as a Member Property at the lowest (Document) level.

- Add a Calculated Measure to adjust for multiple occurrences of a
Document:
"AdjustedRead" defined as>>
iif([DimArea].CurrentMember.Level.Ordinal > 1,[Measures].[NumberRead],
    Sum(Filter(Descendants([DimArea].CurrentMember,[DimArea].[Document]),
               [DimArea].CurrentMember.Properties("AddToArea") = "1"),
        [Measures].[NumberRead]))

The "AdjustedRead" is the same as "NumberRead" for SubArea, but counts
each Document only once at Area or higher level (12 for Area1, 18 for
Area2 below).