HD0013 – How Do I Query Multi Level Dimensional Models

0
110

Please watch the video in 1080p because it is recorded in High Resolution.
Please note the script of the presentation is below so you can read along with the demonstration if you like.

Hello and welcome back to our latest, how do I, blog post.

We are happy that you’ve come back to see our latest blog post.

My name is Sofi, and I’m your BIDA AI assistant.

I will be your host for this demonstration today.

In today’s demonstration we are going to show you how to navigate multi level summary data in a dimensional model.

We will show you how to do this in the product that Ralph Kimball invented, called Meta five.

This sort of multi level data model was first invented by Ralph Kimball way back in the early days of metaphor.

As our readers know, Ralph Kimball became quite famous for the dimensional model.

What you’re going to see in today’s demonstration is pretty much exactly what Ralph Kimball invented all those years ago.

We have made one small update.

And with that?

It is on with the demonstration!

Here we are on a Meta five desktop.

This is one of our development machines, so we’re only going to show you the portion of the desktop that we would like you to see.

We have a query icon which returns actual sales for last 12 months.

This is from one of our applications.

It is a real query icon for a real query.

The query icon is getting data from a development server of our development partner.

This server sits in France.

We will open up the query icon.

You can see it is going to produce a period key, a currency key, a retail outlet key, and a product category key.

It will also produce the sum of the sales amount and the sum of sales units.

This query returns sales value, and volume, by time, currency, outlet and product category.

This is as simple a query as you’re going to build in any dashboard.

The Query Control window shows you what the query looks like.

The concept that Ralph invented many years ago, was to put a level column onto each dimension table, that could be multi level.

In this query the sales period can be multi-level, and the product category can be multi-level.

But if we go down to currency.

Currency, of course, can’t be multi level.

It makes no sense to add different currencies together.

In this particular query we are saying we want the query at the outlet level, so we don’t have a level description in the outlet table.

Now we will option the level column description in the sales measures period table.

We will click show all choices.

We see the levels of day, week, month, quarter, year and total.

The total level has a special meaning.

We will explain that in another video.

We will option the product table.

We will click on show all choices.

We see we have product, product group, product category and total levels.

This query is going to ask for results, at the month level, and the product group level.

In the days of Ralph Kimball, this is what they used to do.

They used to set the level for each dimension.

Then they would join the dimension tables to the multi level fact table and get the right answers.

The database that Ralph Kimball invented, called Red Brick, did this multi-level data navigation automatically.

We have created a slight improvement on this idea.

We have introduced the idea of a fact table level dimension table.

We can look at the fact table name, in the fact table dimension table, and we can option it.

We can click on show all choices.

We see we have 7 fact tables from which we can choose.

The fact table we want to query is called, retail outlet sales measures.

But we want to know what levels are available to us.

We option the fact table name.

We click show all choices.

We select Retail Outles Sales Measures.

We click apply.

We have constrained this query to only show us levels available for the Retail Outles Sales Measures table.

For this table we can ask, what are the number of fields in the summary levels?

There are only three.

We can ask for the first one.

We click on show constrained choices.

We see we have day and month levels.

If we click on show all choices, then we will see we have day, week, month, quarter and year levels.

This fact table is only available at day and month levels.

We want month.

So, we will click on month and click on apply.

For the second dimension level description column the only option is Retail Outlet.

So, we will select that and click apply.

For the third dimension level we click on show constrained choices.

We see that we only have the Product Group Level available at the same level as the month and retail outlet levels.

We have to select Product Group Level even though we want the parent level of Product Category.

We know that this table is telling us there is a fact table summary for month, outlet, product group.

And that’s what we want.

The next query we ask is we go to the fact table level column in the fact table level table.

We option it.

We click on show constrained choices.

The number returned is 509.

So, the unique number of the summary level we want is 509.

Now, we could leave this in the query, right?

There is no harm in leaving this in the query.

It will query the right level of the fact table.

But what we can do is this.

We can go to the fact table.

Let me remove the existing constraint of 509 just for demonstration purposes.

We can go to the fact tale level column.

We can click on options.

We can type 509 in value.

Now we know we are going to go to the right fact table level.

Further, because we know we want the levels of month, retail outlet and product group, we can set the levels on these columns in the dimension tables.

The reason we do this is as follows.

If we set the levels on the dimension tables that are multi level, then we get better performance.

Now that we’ve looked at the fact table level table, and we’ve looked at what levels are available, and we’ve set our levels on the dimension tables, and we’ve set our summary level number on the fact table.

We can actually remove the fact table level table from the query.

Then we click show query.

Meta5 will redraw the query.

Once we’ve got the right number for the fact table level, we can remove the fact table level dimension table.

That’s pretty cool.

Now this is just a SQL Server development machine we’re going to query.

It’s pretty small.

I will click on show data.

We will not pause the video.

You will see data start to come back.

It will take a little while to come back because the SQL development server is in France and the Meta five development server is in Romania.

You can see 43,221 rows came back.

That is the number of rows for the last 12 months sales for all the outlets, for all the currencies, for all the product categories.

In Meta five you can capture the SQL of a query.

We have run this query previously and we captured the SQL.

Then I formatted it properly so you can read it easily.

I will put it up on the video for you.

You can hit pause and read the SQL in detail.

But I will show you the highlights.

Obviously, we’ve got a select statement getting data for the period key, the currency key, the retail outlet key, the product category key.

We also have the sum of sale extended amount, and the sum of sale units.

When we captured this, we put the fact table level in just so you could see it.

Then you have the where clause, and all the joins for the star join.

Then we have a feature which we will cover in another video.

This is where the report indicator one is set to one.

You can see that the sales measures multi level column description is month.

You can see the V M product multi level column description is product group.

Most importantly you can see the V F retail outlet sales measures multi level detail key, dot V M fact table level, equals 509.

In BIDA data models every fact table level has just one number.

So right across your data warehouse system, every summary in the data warehouse system has its own unique summary level number.

You put that fact table level number into the query, and you are guaranteed not to get any double counting.

Of course, we then have the GROUP BY clause.

You then have the ORDER BY clause.

That is what the SQL generated looks like.

It’s quite easy to see how the Query icon has its contents generated into SQL.

To summarize.

That is what I wanted to show you today.

I wanted to show you that there was a way of identifying the fact table level, uniquely identifying number, for every fact table summary level.

I wanted to show you how to then put it into a query icon.

As you can see.

It is very easy to find this number and put into the query.

You just look up the fact table level dimension table.

Look up the fact table.

Look up the levels that are available.

And then use the levels that are available.

The reason for the introduction of the dimension table is, if you don’t know this number, you can spend some time going around the dimension tables trying to find the right combination of levels.

Or you can get incorrect results, with double counting, if you don’t set your query this way.

With that I’d like to say thank you very much for listening.

We really appreciate your time and attention.

I wish you a nice day.