Mondrian: Modeling multivalued dimension attributes

Links to the original text: https://diethardsteiner.github.io/mondrian/2017/03/20/Mondrian-Multivalued-Dimension-Attribute.html

Aggregation type: average

A lively reader named Bruno recently contacted me after reading my article on bridge tables: he was looking for a solution to a challenging scenario, with the task of creating a model for students and achieving results for the courses they attended. Students may have one or more hobbies. One of the analysis questions is: What is the average score of X-loving students in Y course? This has aroused my interest and I would like to know how to achieve this with Mondrian.

In our example, we use the following data:

  • Student Bob took a math course and got seven marks. He also took a physics course and scored three points. Bob's hobbies are games and reading.
  • Student Lilian took a math course and got eight marks. Her hobbies are games, jogging and writing.

Although I have some ideas, only a conversation with Nelson Sousa can find a workable solution: he suggests creating two separate fact tables, one for each student's grade in each course (of course you will have an appointment, but we ignore it for simplicity). Another fact table only stores student and hobby relationships. The latter does not necessarily have to have a measure in the fact table itself, but for completeness I added one (constant value 1). Finally, there is a student dimension as a link between the two fact tables:

multivalued.dim_student:

student_tk student_name
1 Short hair
2 Lillian

multivalued.fact_grades:

student_tk course_name grade
1 Mathematics 7
1 Mathematics 4
1 Physics 3
2 Mathematics 8

multivalued.fact_student_hobbies:

student_tk hobby_name cnt
1 gambling 1
1 read 1
2 gambling 1
2 Run 1
2 writing 1

Create standard cubes to answer simple questions

The important point here is that we want to use aggregation functions of average type (because results cannot be summed):

Cube definition:

<span style="color:#2c3e50"><span style="color:#333333"><code><span style="color:#000080"><Schema</span> <span style="color:#008080">name=</span><span style="color:#d01040">"Multivalued Dimension Attribute"</span><span style="color:#000080">></span>
  <span style="color:#000080"><Dimension</span> <span style="color:#008080">type=</span><span style="color:#d01040">"StandardDimension"</span> <span style="color:#008080">name=</span><span style="color:#d01040">"Student"</span><span style="color:#000080">></span>
    <span style="color:#000080"><Hierarchy</span> <span style="color:#008080">name=</span><span style="color:#d01040">"Student Name"</span> <span style="color:#008080">hasAll=</span><span style="color:#d01040">"true"</span> <span style="color:#008080">primaryKey=</span><span style="color:#d01040">"student_tk"</span><span style="color:#000080">></span>
      <span style="color:#000080"><Table</span> <span style="color:#008080">name=</span><span style="color:#d01040">"dim_student"</span> <span style="color:#008080">schema=</span><span style="color:#d01040">"multivalued"</span><span style="color:#000080">/></span>
      <span style="color:#000080"><Level</span> <span style="color:#008080">name=</span><span style="color:#d01040">"Student Name"</span> <span style="color:#008080">column=</span><span style="color:#d01040">"student_name"</span> <span style="color:#008080">type=</span><span style="color:#d01040">"String"</span> <span style="color:#008080">uniqueMembers=</span><span style="color:#d01040">"true"</span> <span style="color:#008080">levelType=</span><span style="color:#d01040">"Regular"</span><span style="color:#000080">/></span>
    <span style="color:#000080"></Hierarchy></span>
  <span style="color:#000080"></Dimension></span>
  <span style="color:#000080"><Dimension</span> <span style="color:#008080">type=</span><span style="color:#d01040">"TimeDimension"</span> <span style="color:#008080">name=</span><span style="color:#d01040">"Date"</span><span style="color:#000080">></span>
    <span style="color:#000080"><Hierarchy</span> <span style="color:#008080">name=</span><span style="color:#d01040">"Date"</span> <span style="color:#008080">hasAll=</span><span style="color:#d01040">"true"</span> <span style="color:#008080">primaryKey=</span><span style="color:#d01040">"date_tk"</span><span style="color:#000080">></span>
      <span style="color:#000080"><Table</span> <span style="color:#008080">name=</span><span style="color:#d01040">"dim_date"</span> <span style="color:#008080">schema=</span><span style="color:#d01040">"multivalued"</span><span style="color:#000080">/></span>
      <span style="color:#000080"><Level</span> <span style="color:#008080">name=</span><span style="color:#d01040">"Date"</span> <span style="color:#008080">column=</span><span style="color:#d01040">"the_date"</span> <span style="color:#008080">type=</span><span style="color:#d01040">"Date"</span> <span style="color:#008080">uniqueMembers=</span><span style="color:#d01040">"true"</span> <span style="color:#008080">levelType=</span><span style="color:#d01040">"TimeDays"</span><span style="color:#000080">/></span>
    <span style="color:#000080"></Hierarchy></span>
  <span style="color:#000080"></Dimension></span>
  <span style="color:#000080"><Cube</span> <span style="color:#008080">name=</span><span style="color:#d01040">"Grades"</span> <span style="color:#008080">cache=</span><span style="color:#d01040">"true"</span> <span style="color:#008080">enabled=</span><span style="color:#d01040">"true"</span><span style="color:#000080">></span>
    <span style="color:#000080"><Table</span> <span style="color:#008080">name=</span><span style="color:#d01040">"fact_grades"</span> <span style="color:#008080">schema=</span><span style="color:#d01040">"multivalued"</span><span style="color:#000080">/></span>
    <span style="color:#000080"><Dimension</span> <span style="color:#008080">type=</span><span style="color:#d01040">"StandardDimension"</span> <span style="color:#008080">name=</span><span style="color:#d01040">"Course Name"</span><span style="color:#000080">></span>
      <span style="color:#000080"><Hierarchy</span> <span style="color:#008080">name=</span><span style="color:#d01040">"Course Name"</span> <span style="color:#008080">hasAll=</span><span style="color:#d01040">"true"</span><span style="color:#000080">></span>
        <span style="color:#000080"><Level</span> <span style="color:#008080">name=</span><span style="color:#d01040">"Course Name"</span> <span style="color:#008080">column=</span><span style="color:#d01040">"course_name"</span> <span style="color:#008080">type=</span><span style="color:#d01040">"String"</span> <span style="color:#008080">uniqueMembers=</span><span style="color:#d01040">"false"</span> <span style="color:#008080">levelType=</span><span style="color:#d01040">"Regular"</span><span style="color:#000080">></span>
        <span style="color:#000080"></Level></span>
      <span style="color:#000080"></Hierarchy></span>
    <span style="color:#000080"></Dimension></span>
    <span style="color:#000080"><DimensionUsage</span> <span style="color:#008080">source=</span><span style="color:#d01040">"Student"</span> <span style="color:#008080">name=</span><span style="color:#d01040">"Student"</span> <span style="color:#008080">foreignKey=</span><span style="color:#d01040">"student_tk"</span><span style="color:#000080">/></span>
    <span style="color:#000080"><Measure</span> <span style="color:#008080">name=</span><span style="color:#d01040">"Grade"</span> <span style="color:#008080">column=</span><span style="color:#d01040">"grade"</span> <span style="color:#008080">datatype=</span><span style="color:#d01040">"Integer"</span> <span style="color:#008080">aggregator=</span><span style="color:#d01040">"avg"</span><span style="color:#000080">/></span>
  <span style="color:#000080"></Cube></span>
  <span style="color:#000080"><Cube</span> <span style="color:#008080">name=</span><span style="color:#d01040">"Hobbies"</span> <span style="color:#008080">cache=</span><span style="color:#d01040">"true"</span> <span style="color:#008080">enabled=</span><span style="color:#d01040">"true"</span><span style="color:#000080">></span>
    <span style="color:#000080"><Table</span> <span style="color:#008080">name=</span><span style="color:#d01040">"fact_student_hobbies"</span> <span style="color:#008080">schema=</span><span style="color:#d01040">"multivalued"</span><span style="color:#000080">/></span>
    <span style="color:#000080"><Dimension</span> <span style="color:#008080">type=</span><span style="color:#d01040">"StandardDimension"</span> <span style="color:#008080">name=</span><span style="color:#d01040">"Hobby Name"</span><span style="color:#000080">></span>
      <span style="color:#000080"><Hierarchy</span> <span style="color:#008080">name=</span><span style="color:#d01040">"Hobby Name"</span> <span style="color:#008080">hasAll=</span><span style="color:#d01040">"true"</span><span style="color:#000080">></span>
        <span style="color:#000080"><Level</span> <span style="color:#008080">name=</span><span style="color:#d01040">"Hobby Name"</span> <span style="color:#008080">column=</span><span style="color:#d01040">"hobby_name"</span> <span style="color:#008080">type=</span><span style="color:#d01040">"String"</span> <span style="color:#008080">uniqueMembers=</span><span style="color:#d01040">"false"</span> <span style="color:#008080">levelType=</span><span style="color:#d01040">"Regular"</span><span style="color:#000080">></span>
        <span style="color:#000080"></Level></span>
      <span style="color:#000080"></Hierarchy></span>
    <span style="color:#000080"></Dimension></span>
    <span style="color:#000080"><DimensionUsage</span> <span style="color:#008080">source=</span><span style="color:#d01040">"Student"</span> <span style="color:#008080">name=</span><span style="color:#d01040">"Student"</span> <span style="color:#008080">foreignKey=</span><span style="color:#d01040">"student_tk"</span><span style="color:#000080">/></span>
    <span style="color:#000080"><Measure</span> <span style="color:#008080">name=</span><span style="color:#d01040">"Count Hobbies"</span> <span style="color:#008080">column=</span><span style="color:#d01040">"cnt"</span> <span style="color:#008080">datatype=</span><span style="color:#d01040">"Integer"</span> <span style="color:#008080">formatString=</span><span style="color:#d01040">"#,###"</span> <span style="color:#008080">aggregator=</span><span style="color:#d01040">"sum"</span><span style="color:#000080">/></span>
  <span style="color:#000080"></Cube></span>
<span style="color:#000080"></Schema></span>
</code></span></span>

We created a cube for each fact table to answer specific questions:

Question: What is the average score of the students?

<span style="color:#2c3e50"><span style="color:#333333"><code><span style="color:#000000"><strong>SELECT</strong></span>
  [Student.Student Name].Children <span style="color:#000000"><strong>ON</strong></span> <span style="color:#000000"><strong>ROWS</strong></span>
  , [Measures].[Grade] <span style="color:#000000"><strong>ON</strong></span> COLUMNS
<span style="color:#000000"><strong>FROM</strong></span> [Grades]
</code></span></span>

Result:

Name of student Average grade
Short hair 4.667
Lillian 8

Question: What is the average score of the course?

<span style="color:#2c3e50"><span style="color:#333333"><code><span style="color:#000000"><strong>SELECT</strong></span>
  [Course Name].Children <span style="color:#000000"><strong>ON</strong></span> <span style="color:#000000"><strong>ROWS</strong></span>
  , [Measures].[Grade] <span style="color:#000000"><strong>ON</strong></span> COLUMNS
<span style="color:#000000"><strong>FROM</strong></span> [Grades]
</code></span></span>

Result:

Course Name Average grade
Mathematics 6.333
Physics 3

Question: How many students regard games as their hobbies?

<span style="color:#2c3e50"><span style="color:#333333"><code><span style="color:#000000"><strong>SELECT</strong></span>
  NON EMPTY [Student.Student Name].Members <span style="color:#000000"><strong>ON</strong></span> <span style="color:#000000"><strong>ROWS</strong></span>
  , [Measures].[<span style="color:#000000"><strong>Count</strong></span> Hobbies] <span style="color:#000000"><strong>ON</strong></span> COLUMNS
<span style="color:#000000"><strong>FROM</strong></span> [Hobbies]
<span style="color:#000000"><strong>WHERE</strong></span>
  [Hobby Name].[Gaming]
</code></span></span>

Result:

Name of student Count's hobby
All students. Name of student 2
Short hair 1
Lillian 1

Create virtual cubes to answer complex questions

Then we can create a virtual cube to answer questions spanning two fact tables. We call this cube Grades and Hobbies.

I recommend reading Virtual Multidimensional data set upper Official Mondrian Docu Learn how to build virtual cubes.

Virtual cubes: How to add basic cubes

Use global (conformity) dimensions for this purpose. In our example, the Student dimension links two fact tables and defines them as global dimensions.

<span style="color:#2c3e50"><span style="color:#333333"><code><span style="color:#000080"><VirtualCubeDimension</span> <span style="color:#008080">name=</span><span style="color:#d01040">"Student"</span><span style="color:#000080">/></span>
</code></span></span>

Important Note: For common dimensions, we do not define cubeName attribute values.

Question: Does this actually need to be defined, Cube Usage, because in this case the VirtualCube Dimension element is not cube Name? Specified value? Otherwise, how does Mondrian know that this global dimension exists in two underlying cubes and can therefore be used to join the underlying cubes?

A: Cube Usage is optional, not necessary. If you are referring to a global virtual cube, Mondrian will examine the underlying data virtualization measures defined to see that the global level is referring to the underlying cube.

Complete virtual cube definition:

<span style="color:#2c3e50"><span style="color:#333333"><code><span style="color:#000080"><VirtualCube</span> <span style="color:#008080">name=</span><span style="color:#d01040">"Grades and Hobbies"</span> <span style="color:#008080">enabled=</span><span style="color:#d01040">"true"</span><span style="color:#000080">></span>
  <span style="color:#999988"><em><!-- common dimensions --></em></span>
  <span style="color:#000080"><VirtualCubeDimension</span> <span style="color:#008080">name=</span><span style="color:#d01040">"Student"</span><span style="color:#000080">/></span>
  <span style="color:#999988"><em><!-- specific dimensions --></em></span>
  <span style="color:#000080"><VirtualCubeDimension</span> <span style="color:#008080">name=</span><span style="color:#d01040">"Course Name"</span> <span style="color:#008080">cubeName=</span><span style="color:#d01040">"Grades"</span><span style="color:#000080">/></span>
  <span style="color:#000080"><VirtualCubeDimension</span> <span style="color:#008080">name=</span><span style="color:#d01040">"Hobby Name"</span> <span style="color:#008080">cubeName=</span><span style="color:#d01040">"Hobbies"</span><span style="color:#000080">/></span>
  <span style="color:#000080"><VirtualCubeMeasure</span> <span style="color:#008080">name=</span><span style="color:#d01040">"[Measures].[Count Hobbies]"</span> <span style="color:#008080">cubeName=</span><span style="color:#d01040">"Hobbies"</span><span style="color:#000080">/></span>
  <span style="color:#000080"><VirtualCubeMeasure</span> <span style="color:#008080">name=</span><span style="color:#d01040">"[Measures].[Grade]"</span> <span style="color:#008080">cubeName=</span><span style="color:#d01040">"Grades"</span><span style="color:#000080">/></span>
<span style="color:#000080"></VirtualCube></span>
</code></span></span>

Let's create a list of students with hobbies and average grades:

<span style="color:#2c3e50"><span style="color:#333333"><code><span style="color:#000000"><strong>SELECT</strong></span>
  [Student.Student Name].Children <span style="color:#000000"><strong>ON</strong></span> <span style="color:#000000"><strong>ROWS</strong></span>
  , <span style="color:#a61717">{</span>[Measures].[Grade], [Measures].[<span style="color:#000000"><strong>Count</strong></span> Hobbies]<span style="color:#a61717">}</span> <span style="color:#000000"><strong>ON</strong></span> COLUMNS
<span style="color:#000000"><strong>FROM</strong></span> [Grades <span style="color:#000000"><strong>and</strong></span> Hobbies]
</code></span></span>
Name of student grade Count's hobby
Short hair 4.667 2
Lillian 8 3

The following results show a very interesting result: the number of hobbies is available only at all levels, but at all levels:

<span style="color:#2c3e50"><span style="color:#333333"><code><span style="color:#000000"><strong>SELECT</strong></span>
    [Student.Student Name].Members <span style="color:#000000"><strong>*</strong></span> [Course Name].Members <span style="color:#000000"><strong>ON</strong></span> <span style="color:#000000"><strong>ROWS</strong></span>
  , <span style="color:#a61717">{</span>[Measures].AllMembers<span style="color:#a61717">}</span> <span style="color:#000000"><strong>ON</strong></span> COLUMNS
<span style="color:#000000"><strong>FROM</strong></span> [Grades <span style="color:#000000"><strong>and</strong></span> Hobbies]
</code></span></span>
Name of student Course Name Count's hobby grade
All students. Name of student Names of all courses Five 5.5
  Mathematics   6.333
  Physics   3
Short hair Names of all courses 2 4.667
  Mathematics   5.5
  Physics   3
Lillian Names of all courses 3 8
  Mathematics   8
  Physics    

This makes sense, because the number of hobbies can only be provided at an All Courses level, because of course we don't store them!

Let's get a list of students whose hobbies are games:

<span style="color:#2c3e50"><span style="color:#333333"><code><span style="color:#000000"><strong>SELECT</strong></span>
  FILTER(
  [Student.Student Name].Children
  , (
      [Hobby Name].[Gaming]
      , [Measures].[<span style="color:#000000"><strong>Count</strong></span> Hobbies] 
    ) <span style="color:#000000"><strong>></strong></span> <span style="color:#009999">0</span>
) <span style="color:#000000"><strong>ON</strong></span> <span style="color:#000000"><strong>ROWS</strong></span>
  , <span style="color:#a61717">{}</span> <span style="color:#000000"><strong>ON</strong></span> COLUMNS
<span style="color:#000000"><strong>FROM</strong></span> [Grades <span style="color:#000000"><strong>and</strong></span> Hobbies]
</code></span></span>

Result:

Name of student
Short hair
Lillian

Q: What is Hobby's average score?

<span style="color:#2c3e50"><span style="color:#333333"><code><span style="color:#000000"><strong>SELECT</strong></span>
 [Course Name].Members <span style="color:#000000"><strong>ON</strong></span> <span style="color:#000000"><strong>ROWS</strong></span>
  , <span style="color:#a61717">{</span>[Measures].[Grade]<span style="color:#a61717">}</span> <span style="color:#000000"><strong>ON</strong></span> COLUMNS
<span style="color:#000000"><strong>FROM</strong></span> [Grades <span style="color:#000000"><strong>and</strong></span> Hobbies]
</code></span></span>
Course Name grade
Mathematics 6.333
Physics 3

Learning exercises

Let's go back to the basics: Note the slicer () in the query below WHERE:

<span style="color:#2c3e50"><span style="color:#333333"><code><span style="color:#000000"><strong>WITH</strong></span> <span style="color:#000000"><strong>SET</strong></span> STUDENTS <span style="color:#000000"><strong>AS</strong></span>
FILTER(
  [Student.Student Name].Children
  , (
      [Hobby Name].[Reading]
      , [Measures].[<span style="color:#000000"><strong>Count</strong></span> Hobbies] 
    ) <span style="color:#000000"><strong>></strong></span> <span style="color:#009999">0</span>
)
<span style="color:#000000"><strong>SELECT</strong></span>
  STUDENTS <span style="color:#000000"><strong>ON</strong></span> <span style="color:#000000"><strong>ROWS</strong></span>
  , <span style="color:#a61717">{</span>[Measures].[Grade]<span style="color:#a61717">}</span> <span style="color:#000000"><strong>ON</strong></span> COLUMNS
<span style="color:#000000"><strong>FROM</strong></span> [Grades <span style="color:#000000"><strong>and</strong></span> Hobbies]
<span style="color:#000000"><strong>WHERE</strong></span> [Course Name].[Course Name].[Math]
</code></span></span>

Interestingly, we didn't get any records! But if we move the restriction from the slicer to one of the axes, then everything is fine:

<span style="color:#2c3e50"><span style="color:#333333"><code>WITH SET STUDENTS AS
FILTER(
  [Student.Student Name].Children
  , (
      [Hobby Name].[Reading]
      , [Measures].[Count Hobbies] 
    ) > 0
)
SELECT
  STUDENTS *  [Course Name].[Course Name].[Math] ON ROWS
  , {[Measures].[Grade]} ON COLUMNS
FROM [Grades and Hobbies]
</code></span></span>

Result:

Name of student Course Name grade
Short hair Mathematics 5.5

The reason for this is that the slicer also directly affects the computational members and sets, and if we move the constraints onto one of the axes, the computational members and sets will be evaluated without such constraints.

Alternatively, you can add All members to the filter to override constraints in the slicer:

<span style="color:#2c3e50"><span style="color:#333333"><code><span style="color:#000000"><strong>WITH</strong></span> <span style="color:#000000"><strong>SET</strong></span> STUDENTS <span style="color:#000000"><strong>AS</strong></span>
FILTER(
  [Student.Student Name].Children
  , (
      [Hobby Name].[Reading]
     , [Course Name].[<span style="color:#000000"><strong>All</strong></span> Course <span style="color:#000000"><strong>Names</strong></span>]
     , [Measures].[<span style="color:#000000"><strong>Count</strong></span> Hobbies] 
    ) <span style="color:#000000"><strong>></strong></span> <span style="color:#009999">0</span>
)
<span style="color:#000000"><strong>SELECT</strong></span>
  STUDENTS <span style="color:#000000"><strong>ON</strong></span> <span style="color:#000000"><strong>ROWS</strong></span>
  , <span style="color:#a61717">{</span>[Measures].[Grade]<span style="color:#a61717">}</span> <span style="color:#000000"><strong>ON</strong></span> COLUMNS
<span style="color:#000000"><strong>FROM</strong></span> [Grades <span style="color:#000000"><strong>and</strong></span> Hobbies]
<span style="color:#000000"><strong>WHERE</strong></span> [Course Name].[Course Name].[Math]
</code></span></span>

This will produce exactly the same results.

Next let's look at the original question:

Question: What is the average score of the students who take math courses and take games as their hobbies?

This query returns all students interested in games and taking math courses:

<span style="color:#2c3e50"><span style="color:#333333"><code><span style="color:#000000"><strong>WITH</strong></span>
<span style="color:#000000"><strong>SET</strong></span> STUDENTS <span style="color:#000000"><strong>AS</strong></span>
FILTER(
  [Student.Student Name].Children
  , (
      [Hobby Name].[Gaming]
      , [Measures].[<span style="color:#000000"><strong>Count</strong></span> Hobbies] 
    ) <span style="color:#000000"><strong>></strong></span> <span style="color:#009999">0</span>
)
<span style="color:#000000"><strong>SELECT</strong></span>
  STUDENTS <span style="color:#000000"><strong>*</strong></span>  [Course Name].[Course Name].[Math] <span style="color:#000000"><strong>ON</strong></span> <span style="color:#000000"><strong>ROWS</strong></span>
  , <span style="color:#a61717">{</span>[Measures].[Grade]<span style="color:#a61717">}</span> <span style="color:#000000"><strong>ON</strong></span> COLUMNS
<span style="color:#000000"><strong>FROM</strong></span> [Grades <span style="color:#000000"><strong>and</strong></span> Hobbies]
</code></span></span>
Name of student Course Name grade
Short hair Mathematics 5.5
Lillian Mathematics 8

We are now trying to answer the original question:

<span style="color:#2c3e50"><span style="color:#333333"><code><span style="color:#000000"><strong>WITH</strong></span>
<span style="color:#000000"><strong>SET</strong></span> STUDENTS <span style="color:#000000"><strong>AS</strong></span>
FILTER(
  [Student.Student Name].Children
  , (
      [Hobby Name].[Gaming]
      , [Course Name].[<span style="color:#000000"><strong>All</strong></span> Course <span style="color:#000000"><strong>Names</strong></span>]
      , [Measures].[<span style="color:#000000"><strong>Count</strong></span> Hobbies] 
    ) <span style="color:#000000"><strong>></strong></span> <span style="color:#009999">0</span>
)
<span style="color:#000000"><strong>SELECT</strong></span>
  STUDENTS <span style="color:#000000"><strong>ON</strong></span> <span style="color:#000000"><strong>ROWS</strong></span>
  , <span style="color:#a61717">{</span>[Measures].[Grade]<span style="color:#a61717">}</span> <span style="color:#000000"><strong>ON</strong></span> COLUMNS
<span style="color:#000000"><strong>FROM</strong></span> [Grades <span style="color:#000000"><strong>and</strong></span> Hobbies]
<span style="color:#000000"><strong>WHERE</strong></span> [Course Name].[Course Name].[Math]
</code></span></span>

The trick here is to get a list of students who have a particular hobby before getting a course grade. Also, notice that we move the constraints of Math course into the slicer (WHERE clause). Since this also affects the computational set, we must explicitly add [Course Name].[All Course Names].

Result:

Name of student grade
Short hair 5.5
Lillian 8

Aggregation type: SUM

In this changing theme, we imagine that we are a company that sells all kinds of goods. This customer has different interests, how much revenue we want to see, and the user interest we generate. We don't want to weigh benefits - we distribute all our income to each benefit. The goal is to find out which interest generates most of the revenue (well, we won't do that here, but you'll get the idea).

multivalued.dim_client:

<span style="color:#2c3e50"><span style="color:#333333"><code> client_tk | client_name 
-----------+-------------
         1 | Joe
         2 | Susan
         3 | Tim
</code></span></span>

multivalued.fact_client_interests:

<span style="color:#2c3e50"><span style="color:#333333"><code> client_tk | interest_name | cnt 
-----------+---------------+-----
         1 | Fishing       |   1
         1 | Photography   |   1
         1 | Cooking       |   1
         2 | Cooking       |   1
         2 | Biology       |   1
         3 | Geography     |   1
         3 | Photography   |   1
         3 | Cooking       |   1
</code></span></span>

multivalued.dim_product:

<span style="color:#2c3e50"><span style="color:#333333"><code> product_tk | product_name | unit_price 
------------+--------------+------------
          1 | AAA          |       2.00
          2 | BBB          |       3.00
          3 | CCC          |       1.40
</code></span></span>

multivalued.dim_date:

<span style="color:#2c3e50"><span style="color:#333333"><code> date_tk  |  the_date
----------+------------
 20170324 | 2017-03-24
</code></span></span>

multivalued.fact_sales:

<span style="color:#2c3e50"><span style="color:#333333"><code> date_tk  | client_tk | product_tk | no_of_units | amount_spent 
----------+-----------+------------+-------------+--------------
 20170324 |         1 |          1 |           2 |            4
 20170324 |         2 |          1 |           3 |            6
 20170324 |         1 |          2 |           4 |           12
 20170324 |         2 |          2 |           2 |            6
 20170324 |         3 |          2 |           3 |            9
 20170324 |         2 |          3 |           2 |          2.8
 20170324 |         3 |          3 |           1 |          1.4
</code></span></span>

Cube Definition: In this case, the client is our global dimension, which links two cubes (sales and interest). The setup is simple, so I won't elaborate on it.

<span style="color:#2c3e50"><span style="color:#333333"><code><span style="color:#000080"><Schema</span> <span style="color:#008080">name=</span><span style="color:#d01040">"Multivalued Dimension Attribute"</span><span style="color:#000080">></span>
  <span style="color:#000080"><Dimension</span> <span style="color:#008080">type=</span><span style="color:#d01040">"TimeDimension"</span> <span style="color:#008080">name=</span><span style="color:#d01040">"Date"</span><span style="color:#000080">></span>
    <span style="color:#000080"><Hierarchy</span> <span style="color:#008080">name=</span><span style="color:#d01040">"Date"</span> <span style="color:#008080">hasAll=</span><span style="color:#d01040">"true"</span> <span style="color:#008080">primaryKey=</span><span style="color:#d01040">"date_tk"</span><span style="color:#000080">></span>
      <span style="color:#000080"><Table</span> <span style="color:#008080">name=</span><span style="color:#d01040">"dim_date"</span> <span style="color:#008080">schema=</span><span style="color:#d01040">"multivalued"</span><span style="color:#000080">/></span>
      <span style="color:#000080"><Level</span> <span style="color:#008080">name=</span><span style="color:#d01040">"Date"</span> <span style="color:#008080">column=</span><span style="color:#d01040">"the_date"</span> <span style="color:#008080">type=</span><span style="color:#d01040">"Date"</span> <span style="color:#008080">uniqueMembers=</span><span style="color:#d01040">"true"</span> <span style="color:#008080">levelType=</span><span style="color:#d01040">"TimeDays"</span><span style="color:#000080">/></span>
    <span style="color:#000080"></Hierarchy></span>
  <span style="color:#000080"></Dimension></span>
  <span style="color:#000080"><Dimension</span> <span style="color:#008080">type=</span><span style="color:#d01040">"StandardDimension"</span> <span style="color:#008080">name=</span><span style="color:#d01040">"Product"</span><span style="color:#000080">></span>
    <span style="color:#000080"><Hierarchy</span> <span style="color:#008080">name=</span><span style="color:#d01040">"Product Name"</span> <span style="color:#008080">hasAll=</span><span style="color:#d01040">"true"</span> <span style="color:#008080">primaryKey=</span><span style="color:#d01040">"product_tk"</span><span style="color:#000080">></span>
      <span style="color:#000080"><Table</span> <span style="color:#008080">name=</span><span style="color:#d01040">"dim_product"</span> <span style="color:#008080">schema=</span><span style="color:#d01040">"multivalued"</span><span style="color:#000080">/></span>
      <span style="color:#000080"><Level</span> <span style="color:#008080">name=</span><span style="color:#d01040">"Product Name"</span> <span style="color:#008080">column=</span><span style="color:#d01040">"product_name"</span> <span style="color:#008080">type=</span><span style="color:#d01040">"String"</span> <span style="color:#008080">uniqueMembers=</span><span style="color:#d01040">"true"</span> <span style="color:#008080">levelType=</span><span style="color:#d01040">"Regular"</span><span style="color:#000080">/></span>
    <span style="color:#000080"></Hierarchy></span>
  <span style="color:#000080"></Dimension></span>
  <span style="color:#000080"><Dimension</span> <span style="color:#008080">type=</span><span style="color:#d01040">"StandardDimension"</span> <span style="color:#008080">name=</span><span style="color:#d01040">"Client"</span><span style="color:#000080">></span>
    <span style="color:#000080"><Hierarchy</span> <span style="color:#008080">name=</span><span style="color:#d01040">"Client Name"</span> <span style="color:#008080">hasAll=</span><span style="color:#d01040">"true"</span> <span style="color:#008080">primaryKey=</span><span style="color:#d01040">"client_tk"</span><span style="color:#000080">></span>
      <span style="color:#000080"><Table</span> <span style="color:#008080">name=</span><span style="color:#d01040">"dim_client"</span> <span style="color:#008080">schema=</span><span style="color:#d01040">"multivalued"</span><span style="color:#000080">/></span>
      <span style="color:#000080"><Level</span> <span style="color:#008080">name=</span><span style="color:#d01040">"Client Name"</span> <span style="color:#008080">column=</span><span style="color:#d01040">"client_name"</span> <span style="color:#008080">type=</span><span style="color:#d01040">"String"</span> <span style="color:#008080">uniqueMembers=</span><span style="color:#d01040">"true"</span> <span style="color:#008080">levelType=</span><span style="color:#d01040">"Regular"</span><span style="color:#000080">/></span>
    <span style="color:#000080"></Hierarchy></span>
  <span style="color:#000080"></Dimension></span>
  <span style="color:#000080"><Cube</span> <span style="color:#008080">name=</span><span style="color:#d01040">"Sales"</span> <span style="color:#008080">cache=</span><span style="color:#d01040">"true"</span> <span style="color:#008080">enabled=</span><span style="color:#d01040">"true"</span><span style="color:#000080">></span>
    <span style="color:#000080"><Table</span> <span style="color:#008080">name=</span><span style="color:#d01040">"fact_sales"</span> <span style="color:#008080">schema=</span><span style="color:#d01040">"multivalued"</span><span style="color:#000080">/></span>
    <span style="color:#000080"><DimensionUsage</span> <span style="color:#008080">source=</span><span style="color:#d01040">"Date"</span> <span style="color:#008080">name=</span><span style="color:#d01040">"Date"</span> <span style="color:#008080">foreignKey=</span><span style="color:#d01040">"date_tk"</span><span style="color:#000080">/></span>
    <span style="color:#000080"><DimensionUsage</span> <span style="color:#008080">source=</span><span style="color:#d01040">"Client"</span> <span style="color:#008080">name=</span><span style="color:#d01040">"Client"</span> <span style="color:#008080">foreignKey=</span><span style="color:#d01040">"client_tk"</span><span style="color:#000080">/></span>
    <span style="color:#000080"><DimensionUsage</span> <span style="color:#008080">source=</span><span style="color:#d01040">"Product"</span> <span style="color:#008080">name=</span><span style="color:#d01040">"Product"</span> <span style="color:#008080">foreignKey=</span><span style="color:#d01040">"product_tk"</span><span style="color:#000080">/></span>
    <span style="color:#000080"><Measure</span> <span style="color:#008080">name=</span><span style="color:#d01040">"Number of Units"</span> <span style="color:#008080">column=</span><span style="color:#d01040">"no_of_units"</span> <span style="color:#008080">datatype=</span><span style="color:#d01040">"Integer"</span> <span style="color:#008080">formatString=</span><span style="color:#d01040">"#,###"</span> <span style="color:#008080">aggregator=</span><span style="color:#d01040">"sum"</span><span style="color:#000080">/></span>
    <span style="color:#000080"><Measure</span> <span style="color:#008080">name=</span><span style="color:#d01040">"Revenue"</span> <span style="color:#008080">column=</span><span style="color:#d01040">"amount_spent"</span> <span style="color:#008080">datatype=</span><span style="color:#d01040">"Numeric"</span> <span style="color:#008080">formatString=</span><span style="color:#d01040">"#,###.00"</span> <span style="color:#008080">aggregator=</span><span style="color:#d01040">"sum"</span><span style="color:#000080">/></span>
  <span style="color:#000080"></Cube></span>
  <span style="color:#000080"><Cube</span> <span style="color:#008080">name=</span><span style="color:#d01040">"Interests"</span> <span style="color:#008080">cache=</span><span style="color:#d01040">"true"</span> <span style="color:#008080">enabled=</span><span style="color:#d01040">"true"</span><span style="color:#000080">></span>
    <span style="color:#000080"><Table</span> <span style="color:#008080">name=</span><span style="color:#d01040">"fact_client_interests"</span> <span style="color:#008080">schema=</span><span style="color:#d01040">"multivalued"</span><span style="color:#000080">/></span>
    <span style="color:#000080"><Dimension</span> <span style="color:#008080">type=</span><span style="color:#d01040">"StandardDimension"</span> <span style="color:#008080">name=</span><span style="color:#d01040">"Interest Name"</span><span style="color:#000080">></span>
      <span style="color:#000080"><Hierarchy</span> <span style="color:#008080">name=</span><span style="color:#d01040">"Interest Name"</span> <span style="color:#008080">hasAll=</span><span style="color:#d01040">"true"</span><span style="color:#000080">></span>
        <span style="color:#000080"><Level</span> <span style="color:#008080">name=</span><span style="color:#d01040">"Interest Name"</span> <span style="color:#008080">column=</span><span style="color:#d01040">"interest_name"</span> <span style="color:#008080">type=</span><span style="color:#d01040">"String"</span> <span style="color:#008080">uniqueMembers=</span><span style="color:#d01040">"true"</span> <span style="color:#008080">levelType=</span><span style="color:#d01040">"Regular"</span><span style="color:#000080">/></span>
      <span style="color:#000080"></Hierarchy></span>
    <span style="color:#000080"></Dimension></span>
    <span style="color:#000080"><DimensionUsage</span> <span style="color:#008080">source=</span><span style="color:#d01040">"Client"</span> <span style="color:#008080">name=</span><span style="color:#d01040">"Client"</span> <span style="color:#008080">foreignKey=</span><span style="color:#d01040">"client_tk"</span><span style="color:#000080">/></span>
    <span style="color:#000080"><Measure</span> <span style="color:#008080">name=</span><span style="color:#d01040">"Count Interests"</span> <span style="color:#008080">column=</span><span style="color:#d01040">"cnt"</span> <span style="color:#008080">datatype=</span><span style="color:#d01040">"Integer"</span> <span style="color:#008080">formatString=</span><span style="color:#d01040">"#,###"</span> <span style="color:#008080">aggregator=</span><span style="color:#d01040">"sum"</span><span style="color:#000080">/></span>
  <span style="color:#000080"></Cube></span>
  <span style="color:#000080"><VirtualCube</span> <span style="color:#008080">name=</span><span style="color:#d01040">"Sales and Interests"</span> <span style="color:#008080">enabled=</span><span style="color:#d01040">"true"</span><span style="color:#000080">></span>
    <span style="color:#999988"><em><!-- common dimensions --></em></span>
    <span style="color:#000080"><VirtualCubeDimension</span> <span style="color:#008080">name=</span><span style="color:#d01040">"Client"</span><span style="color:#000080">/></span>
    <span style="color:#999988"><em><!-- specific dimensions --></em></span>
    <span style="color:#000080"><VirtualCubeDimension</span> <span style="color:#008080">name=</span><span style="color:#d01040">"Date"</span> <span style="color:#008080">cubeName=</span><span style="color:#d01040">"Sales"</span><span style="color:#000080">/></span>
    <span style="color:#000080"><VirtualCubeDimension</span> <span style="color:#008080">name=</span><span style="color:#d01040">"Product"</span> <span style="color:#008080">cubeName=</span><span style="color:#d01040">"Sales"</span><span style="color:#000080">/></span>
    <span style="color:#000080"><VirtualCubeDimension</span> <span style="color:#008080">name=</span><span style="color:#d01040">"Interest Name"</span> <span style="color:#008080">cubeName=</span><span style="color:#d01040">"Interests"</span><span style="color:#000080">/></span>
    <span style="color:#000080"><VirtualCubeMeasure</span> <span style="color:#008080">name=</span><span style="color:#d01040">"[Measures].[Number of Units]"</span> <span style="color:#008080">cubeName=</span><span style="color:#d01040">"Sales"</span><span style="color:#000080">/></span>
    <span style="color:#000080"><VirtualCubeMeasure</span> <span style="color:#008080">name=</span><span style="color:#d01040">"[Measures].[Revenue]"</span> <span style="color:#008080">cubeName=</span><span style="color:#d01040">"Sales"</span><span style="color:#000080">/></span>
    <span style="color:#000080"><VirtualCubeMeasure</span> <span style="color:#008080">name=</span><span style="color:#d01040">"[Measures].[Count Interests]"</span> <span style="color:#008080">cubeName=</span><span style="color:#d01040">"Interests"</span><span style="color:#000080">/></span>
  <span style="color:#000080"></VirtualCube></span>
<span style="color:#000080"></Schema></span>
</code></span></span>

We created a cube for each fact table to answer specific questions:

Q: What is the revenue of our products?

<span style="color:#2c3e50"><span style="color:#333333"><code><span style="color:#000000"><strong>SELECT</strong></span>
  [Product.Product Name].Children <span style="color:#000000"><strong>ON</strong></span> <span style="color:#000000"><strong>ROWS</strong></span>
  , <span style="color:#a61717">{</span>[Measures].[Number <span style="color:#000000"><strong>of</strong></span> Units], [Measures].[Revenue]<span style="color:#a61717">}</span> <span style="color:#000000"><strong>ON</strong></span> COLUMNS
<span style="color:#000000"><strong>FROM</strong></span> [Sales]
</code></span></span>
Product Name Units income
AAA Five 10.00
BBB 9 27.00
CCC 3 4.20

Q: How much revenue do we bring to each user?

<span style="color:#2c3e50"><span style="color:#333333"><code><span style="color:#000000"><strong>select</strong></span> 
  [Client.Client Name].Children <span style="color:#000000"><strong>ON</strong></span> <span style="color:#000000"><strong>ROWS</strong></span>
  , <span style="color:#a61717">{</span>[Measures].[Number <span style="color:#000000"><strong>of</strong></span> Units], [Measures].[Revenue]<span style="color:#a61717">}</span> <span style="color:#000000"><strong>ON</strong></span> COLUMNS
<span style="color:#000000"><strong>FROM</strong></span> [Sales]
</code></span></span>
Customer Name Units income
Joe 6 16.00
Susan 7 14.80
Tim 4 10.40

Q: How many customers are interested in cooking?

<span style="color:#2c3e50"><span style="color:#333333"><code><span style="color:#000000"><strong>SELECT</strong></span>
  NON EMPTY [Client.Client Name].Members <span style="color:#000000"><strong>ON</strong></span> <span style="color:#000000"><strong>ROWS</strong></span>
  , [Measures].[<span style="color:#000000"><strong>Count</strong></span> Interests] <span style="color:#000000"><strong>ON</strong></span> COLUMNS
<span style="color:#000000"><strong>FROM</strong></span> [Interests]
<span style="color:#000000"><strong>WHERE</strong></span>
  [Interest Name].[Cooking]
</code></span></span>
Customer Name Number interests
All Client.Client names 3
Joe 1
Susan 1
Tim 1

We also created a virtual cube to answer questions involving two cubes.

Q: How much interest do users have in photography?

Let's first get a list of related customers:

<span style="color:#2c3e50"><span style="color:#333333"><code><span style="color:#000000"><strong>SELECT</strong></span>
  FILTER(
  [Client.Client Name].Children
  , (
      [Interest Name].[Interest Name].[Photography]
      , [Measures].[<span style="color:#000000"><strong>Count</strong></span> Interests] 
    ) <span style="color:#000000"><strong>></strong></span> <span style="color:#009999">0</span>
) <span style="color:#000000"><strong>ON</strong></span> <span style="color:#000000"><strong>ROWS</strong></span>
  , <span style="color:#a61717">{}</span> <span style="color:#000000"><strong>ON</strong></span> COLUMNS
<span style="color:#000000"><strong>FROM</strong></span> [Sales <span style="color:#000000"><strong>and</strong></span> Interests]
</code></span></span>

Then construct the final query:

<span style="color:#2c3e50"><span style="color:#333333"><code><span style="color:#000000"><strong>WITH</strong></span>
<span style="color:#000000"><strong>SET</strong></span> CLIENTS <span style="color:#000000"><strong>AS</strong></span>
FILTER(
  [Client.Client Name].Children
  , (
      [Interest Name].[Interest Name].[Photography]
      , [Product.Product Name].[<span style="color:#000000"><strong>All</strong></span> Product.Product <span style="color:#000000"><strong>Names</strong></span>]
      , [Measures].[<span style="color:#000000"><strong>Count</strong></span> Interests] 
    ) <span style="color:#000000"><strong>></strong></span> <span style="color:#009999">0</span>
)
<span style="color:#000000"><strong>SELECT</strong></span>
  CLIENTS <span style="color:#000000"><strong>ON</strong></span> <span style="color:#000000"><strong>ROWS</strong></span>
  , <span style="color:#a61717">{</span>[Measures].[Revenue]<span style="color:#a61717">}</span> <span style="color:#000000"><strong>ON</strong></span> COLUMNS
<span style="color:#000000"><strong>FROM</strong></span> [Sales <span style="color:#000000"><strong>and</strong></span> Interests]
<span style="color:#000000"><strong>WHERE</strong></span> [Product.Product Name].[Product Name].[AAA]
</code></span></span>
Customer Name income
Joe 4.00
Tim  

effective measure

In some cases, you may want to display numbers, even if they are not available in the current selection. Take this senario:

When we select Date, Client and Product Name, we can see the values Number of Units and Revenue, but not Count Interests:

 

If we want to view Count Interests numbers, we must select Client Name and Interest as dimensions, but in this case Number of Units and Revenue are empty:

 

Now let's assume that we want to show the closest Number of Units, Revenue, in this case, how we can achieve this. We know that the closest available number is the total Client Name, so basically this number will be repeated several times. If this is indeed acceptable behavior, it is worthwhile to confirm it with business users.

To do this, set the original virtual metric to invisible and use functions to create computational members in the virtual cube, which should refer to the original virtual metric. It should be like this: ValidMeasure()

<span style="color:#2c3e50"><span style="color:#333333"><code><span style="color:#000080"><VirtualCube</span> <span style="color:#008080">name=</span><span style="color:#d01040">"Sales and Interests"</span> <span style="color:#008080">enabled=</span><span style="color:#d01040">"true"</span><span style="color:#000080">></span>
  <span style="color:#999988"><em><!-- common dimensions --></em></span>
  <span style="color:#000080"><VirtualCubeDimension</span> <span style="color:#008080">name=</span><span style="color:#d01040">"Client"</span><span style="color:#000080">/></span>
  <span style="color:#999988"><em><!-- specific dimensions --></em></span>
  <span style="color:#000080"><VirtualCubeDimension</span> <span style="color:#008080">name=</span><span style="color:#d01040">"Date"</span> <span style="color:#008080">cubeName=</span><span style="color:#d01040">"Sales"</span><span style="color:#000080">/></span>
  <span style="color:#000080"><VirtualCubeDimension</span> <span style="color:#008080">name=</span><span style="color:#d01040">"Product"</span> <span style="color:#008080">cubeName=</span><span style="color:#d01040">"Sales"</span><span style="color:#000080">/></span>
  <span style="color:#000080"><VirtualCubeDimension</span> <span style="color:#008080">name=</span><span style="color:#d01040">"Interest Name"</span> <span style="color:#008080">cubeName=</span><span style="color:#d01040">"Interests"</span><span style="color:#000080">/></span>
  <span style="color:#000080"><VirtualCubeMeasure</span> <span style="color:#008080">name=</span><span style="color:#d01040">"[Measures].[Number of Units]"</span> <span style="color:#008080">cubeName=</span><span style="color:#d01040">"Sales"</span> <span style="color:#008080">visible=</span><span style="color:#d01040">"false"</span><span style="color:#000080">/></span>
  <span style="color:#000080"><VirtualCubeMeasure</span> <span style="color:#008080">name=</span><span style="color:#d01040">"[Measures].[Revenue]"</span> <span style="color:#008080">cubeName=</span><span style="color:#d01040">"Sales"</span> <span style="color:#008080">visible=</span><span style="color:#d01040">"false"</span><span style="color:#000080">/></span>
  <span style="color:#000080"><VirtualCubeMeasure</span> <span style="color:#008080">name=</span><span style="color:#d01040">"[Measures].[Count Interests]"</span> <span style="color:#008080">cubeName=</span><span style="color:#d01040">"Interests"</span><span style="color:#000080">/></span>
  <span style="color:#000080"><CalculatedMember</span> <span style="color:#008080">name=</span><span style="color:#d01040">"No of Units"</span> <span style="color:#008080">dimension=</span><span style="color:#d01040">"Measures"</span><span style="color:#000080">></span>
    <span style="color:#000080"><Formula></span>
      <span style="color:#999999"><strong><em><![CDATA[
        ValidMeasure([Measures].[Number of Units])
      ]]></em></strong></span>
    <span style="color:#000080"></Formula></span>
  <span style="color:#000080"></CalculatedMember></span>
  <span style="color:#000080"><CalculatedMember</span> <span style="color:#008080">name=</span><span style="color:#d01040">"Total Revenue"</span> <span style="color:#008080">dimension=</span><span style="color:#d01040">"Measures"</span><span style="color:#000080">></span>
    <span style="color:#000080"><Formula></span>
      <span style="color:#999999"><strong><em><![CDATA[
        ValidMeasure([Measures].[Revenue])
      ]]></em></strong></span>
    <span style="color:#000080"></Formula></span>
  <span style="color:#000080"></CalculatedMember></span>
<span style="color:#000080"></VirtualCube></span>
</code></span></span>

The report based on this new virtual cube will be as follows:

 

In Joe's case, we know that he's interested in cooking, fishing and photography. He bought six units in total, with a total income of 16.

Virtual cubes: non-normalized fact tables

Virtual cubes depend on global dimensions, but if you work with non-normalized fact tables, you may use degenerate dimensions. Does that mean you can't create virtual cubes for them? Not complete. You can deceive the system:

Simply create a view for each degenerate table (such as vw_dim_location) and use it in the global dim table reference.

Tags: Attribute github

Posted on Tue, 13 Aug 2019 23:45:42 -0700 by jburfield