Hello and welcome to our latest blog post here on BIDA Brains.
I am Mihai Neacsu, and I am the Business Development Manager here at BIDA.
I am pleased you have come back to see our latest blog post.
Thank you.
This is quite a long and detailed post.
So if you would like to get a cup of tea or coffee now would be a good time to do that.
A lot of people are asking me why BIDA is promoting Meta5.
The short answer is that we know if we can persuade our prospective customers to give Meta5 to the five smartest business analysts in their company they will sustainably grow their profits.
We have seen the results that Meta5 can achieve for other companies and we want Romanian companies to avail of the same profit improvements Meta5 can deliver.
Meta5 was first designed for business people to use.
At one point Meta5 was the words number 2 data analysis software only behind SAS Software.
The difference being business people could use Meta5 and it took IT people to use SAS Software.
Even today, Meta5 still stands head and shoulders above other tool purporting to provide “self service data analysis”.
At least, that is our opinion and we are sticking to it.
Today I will show you one small example of why Meta 5 is so easy to use and so useful for business analysts.
Consider the case that you want some head office sales dashboard that shows you the following items.
Plans versus actuals using sparklines and Stephen Fews bullet charts.
Actuals this month to date, quarter to date, year to date, along with all the variances against prior periods.
And you want this by the product categories, by region manager, city, outlet and currency over the last 12 months.
That is about as standard a Head Office Dashboard as you are ever going to be asked for.
But this Head Office Dashboard will take most people a surprisingly long time to create because there are some little “gochas” in there.
The only tool available on the marketplace today that does this sort of Dashboard very easily is MicroStrategy.
We are also very familiar with the features of MicroStrategy and we quite like it.
But it is expensive and not easily sold in Romania.
Not to mention one of our partners already sell Microstrategy here in Romania.
Calculating such measures as variances this month to date to last month to date, this month to date vs this month last year in Excel require the use of such tools as SSAS or Essbase.
These sorts of measures are not generally created in Excel in the Power Pivot models.
So in this blog post we are going to show you how Meta5 gets the data for such a dashboard into the Excel Power Pivot model.
We will show you some of how it is so simple and easy to use that business users can create such dashboards.
We will also show you the SQL that is generated.
We will remove customer identifying information from all images.
So, the first picture is to show you a portion of the overall capsule.
This is our very first Business Central capsule so it is numbered BC01001 and called “Head Office Report”.
You can see from the image that we are going to retrieve the following data for dimension tables that will be put into the power pivot models.
- Period.
- Outlet.
- Product Category.
- Currency.
These are very standard dimensions for a head office dashboard.
Then you can see we are going to retrieve the following information for the fact tables that will be put into the power pivot model.
- Plans versus Actuals By Month
- Sales for Month, Quarter and Year to Date.
- Sales for Last Periods.
This last one is the one that is most interesting and will be the subject of most comments.
Now, we won’t show you that much about the dimensions.
You are assumed to be fairly technical because this post is on BIDA Brains.
So let’s start with Plan Versus Actual by Month.
Here is the picture of what is in the capsule.
You can see there is a query icon to retrieve the planned sales.
The SQL it generates is as follows:
Planned Sales
select
dbo.vm_sale_plan_period_ml.pk_vm_sale_plan_period_ml
,dbo.vm_currency.pk_vm_currency
,dbo.vm_ret_outlet_rep.pk_vm_ret_outlet_rep
,dbo.vm_product_ml.dk_vm_product_category
,sum(dbo.vf_ret_outlet_sale_plan_ml.sale_extended_amount_plan)
,sum(dbo.vf_ret_outlet_sale_plan_ml.sale_units_plan)
,sum(dbo.vf_ret_outlet_sale_plan_ml.sale_extended_amount_lower)
,sum(dbo.vf_ret_outlet_sale_plan_ml.sale_units_lower)
,sum(dbo.vf_ret_outlet_sale_plan_ml.sale_extended_amount_upper)
,sum(dbo.vf_ret_outlet_sale_plan_ml.sale_units_upper)
from dbo.vm_sale_plan_period_ml
,dbo.vf_ret_outlet_sale_plan_ml
,dbo.vm_currency
,dbo.vm_product_ml
,dbo.vm_ret_outlet_rep
where
dbo.vm_currency.pk_vm_currency=dbo.vf_ret_outlet_sale_plan_ml.dk_vm_currency
and dbo.vm_ret_outlet_rep.pk_vm_ret_outlet_rep=dbo.vf_ret_outlet_sale_plan_ml.dk_vm_ret_outlet
and dbo.vm_product_ml.pk_vm_product_ml=dbo.vf_ret_outlet_sale_plan_ml.dk_vm_product
and dbo.vf_ret_outlet_sale_plan_ml.dk_vm_sale_period=dbo.vm_sale_plan_period_ml.pk_vm_sale_plan_period_ml
and (dbo.vm_sale_plan_period_ml.sale_plan_period BETWEEN '2016-08-01' and '2017-08-01')
and (dbo.vm_sale_plan_period_ml.level_col_sdesc = '2-Month')
and (dbo.vf_ret_outlet_sale_plan_ml.current_plan_flag = 'Y')
and (dbo.vm_product_ml.level_col_sdesc = '1-ProdGroup')
and (dbo.vf_ret_outlet_sale_plan_ml.dk_vm_fact_table_level = 609)
group by
dbo.vm_sale_plan_period_ml.pk_vm_sale_plan_period_ml
, dbo.vm_currency.pk_vm_currency
, dbo.vm_ret_outlet_rep.pk_vm_ret_outlet_rep
, dbo.vm_product_ml.dk_vm_product_category
order by
dbo.vm_sale_plan_period_ml.pk_vm_sale_plan_period_ml
,dbo.vm_currency.pk_vm_currency
,dbo.vm_ret_outlet_rep.pk_vm_ret_outlet_rep
,dbo.vm_product_ml.dk_vm_product_category
You can see that we are using data from 2016 and 2017.
This is actual old customer production data we are using for testing.
So we have to remove all references to anything in the data.
The SQL is easy enough to understand.
We design multi-level models as made popular by Ralph Kimball.
So you can see this query is at the month level and the product group level.
Where you see a table with the suffix “ml” that means this table is a “multi-level” table and some constraint on the level of the table must be supplied in the query.
Because we are querying at the outlet reporting level we don’t need to mention the level of the outlets.
The code
dbo.vf_ret_outlet_sale_plan_ml.dk_vm_fact_table_level = 609
means that the multi level fact table for outlet plans will be queried at level number 609.
In our data models every summary level of every table is assigned a unique number.
This is part of the design process that we use for multi-level summary fact tables.
Please note that we not only have plans defined, we also have “lower” and “upper” amounts defined for extended sales (total revenue) and units.
These “lower” and “upper” amounts are needed to create the Stephen Few style bullet charts.
Actual Sales
The next icon retrieves actual sales.
In this case we are retrieving actual sales at the month level so if we have set the “report date” in the past then we will get the monthly level plans and sales for the whole month of the month of the report date.
We believe this is fair and reasonable because in another portion of the capsule we will get “month to date” using the maximum date of the report date.
select
dbo.vm_sale_meas_period_ml.pk_vm_sale_meas_period_ml
,dbo.vm_currency.pk_vm_currency
,dbo.vm_ret_outlet_rep.pk_vm_ret_outlet_rep
,dbo.vm_product_ml.dk_vm_product_category
,sum(dbo.vf_ret_outlet_sale_meas_ml.sale_extended_amount)
,sum(dbo.vf_ret_outlet_sale_meas_ml.sale_units)
from dbo.vm_sale_meas_period_ml
,dbo.vf_ret_outlet_sale_meas_ml
,dbo.vm_product_ml
,dbo.vm_ret_outlet_rep
,dbo.vm_currency
where
dbo.vm_currency.pk_vm_currency=dbo.vf_ret_outlet_sale_meas_ml.dk_vm_currency
and dbo.vm_sale_meas_period_ml.pk_vm_sale_meas_period_ml=dbo.vf_ret_outlet_sale_meas_ml.dk_vm_sale_period
and dbo.vm_ret_outlet_rep.pk_vm_ret_outlet_rep=dbo.vf_ret_outlet_sale_meas_ml.dk_vm_ret_outlet
and dbo.vm_product_ml.pk_vm_product_ml=dbo.vf_ret_outlet_sale_meas_ml.dk_vm_product
and (dbo.vf_ret_outlet_sale_meas_ml.dk_vm_fact_table_level = 509)
and (dbo.vm_product_ml.level_col_sdesc = '1-ProdGroup')
and (dbo.vm_sale_meas_period_ml.level_col_sdesc = '2-Month')
and (dbo.vm_sale_meas_period_ml.sale_meas_period BETWEEN '2016-08-01' and '2017-08-15')
group by dbo.vm_sale_meas_period_ml.pk_vm_sale_meas_period_ml
, dbo.vm_currency.pk_vm_currency
, dbo.vm_ret_outlet_rep.pk_vm_ret_outlet_rep
, dbo.vm_product_ml.dk_vm_product_category
order by
dbo.vm_sale_meas_period_ml.pk_vm_sale_meas_period_ml
,dbo.vm_ret_outlet_rep.pk_vm_ret_outlet_rep
,dbo.vm_product_ml.dk_vm_product_category
It is simple SQL.
It is retrieving the sales amount at month level between ‘2016-08-01’ and ‘2017-08-15’.
As you can see in the above capsule the two sets of data are then joined in the join icon.
This is an “outer join” and row that is not matched left or right will have it’s values set to zero.
The join icons supports all the standard join types and there are options for how to set values that are not present.
I don’t want to go into the details on those options.
What I want to focus on is that it is that simple to retrieve plans from one table and actuals from another table and to create a mini-fact table which has plans vs actuals next to each other for the month, currency, outlet, product category.
Just getting plans versus actuals next to each other in one table in a power pivot model is much more difficult than you have just seen.
Meta5’s ability to perform joins, including up to 5 way joins, means the business users can join data without any knowledge of SQL.
Next we will move on to Sales for year to date, quarter to date, and month to date.
In this case, in the image, you can see the use of a multi-join.
A multi-join can be used to join up to 5 data streams at once.
However, it can be heavy on memory and so you have to only use it when you know the 5 data streams will not be large.
When the data streams are large it is better to use the join icon a number of times over.
We will also show you that in this demonstration.
So let us look at the SQL Generated for each of these three query icons.
Sales Year To Date
select
dbo.vm_currency.pk_vm_currency
,dbo.vm_ret_outlet_rep.pk_vm_ret_outlet_rep
,dbo.vm_product_ml.pk_vm_product_ml
,sum(dbo.vf_ret_outlet_sale_meas_ml.sale_extended_amount)
,sum(dbo.vf_ret_outlet_sale_meas_ml.sale_units)
from dbo.vf_ret_outlet_sale_meas_ml
,dbo.vm_currency
,dbo.vm_product_ml
,dbo.vm_ret_outlet_rep
,dbo.vm_sale_meas_period_ml
where
dbo.vm_sale_meas_period_ml.pk_vm_sale_meas_period_ml=dbo.vf_ret_outlet_sale_meas_ml.dk_vm_sale_period
and dbo.vm_currency.pk_vm_currency=dbo.vf_ret_outlet_sale_meas_ml.dk_vm_currency
and dbo.vm_ret_outlet_rep.pk_vm_ret_outlet_rep=dbo.vf_ret_outlet_sale_meas_ml.dk_vm_ret_outlet
and dbo.vm_product_ml.pk_vm_product_ml=dbo.vf_ret_outlet_sale_meas_ml.dk_vm_product
and (dbo.vm_sale_meas_period_ml.sale_meas_period BETWEEN '2017-01-01' and '2017-08-15')
and (dbo.vm_product_ml.level_col_sdesc = '2-ProdCategory')
and (dbo.vf_ret_outlet_sale_meas_ml.dk_vm_fact_table_level = 510)
and (dbo.vm_sale_meas_period_ml.level_col_sdesc = '0-Day')
group by
dbo.vm_currency.pk_vm_currency
, dbo.vm_ret_outlet_rep.pk_vm_ret_outlet_rep
, dbo.vm_product_ml.pk_vm_product_ml
order by
dbo.vm_currency.pk_vm_currency
,dbo.vm_ret_outlet_rep.pk_vm_ret_outlet_rep
,dbo.vm_product_ml.pk_vm_product_ml
Sales Quarter To Date
select
dbo.vm_currency.pk_vm_currency
,dbo.vm_ret_outlet_rep.pk_vm_ret_outlet_rep
,dbo.vm_product_ml.pk_vm_product_ml
,sum(dbo.vf_ret_outlet_sale_meas_ml.sale_extended_amount)
,sum(dbo.vf_ret_outlet_sale_meas_ml.sale_units)
from dbo.vf_ret_outlet_sale_meas_ml
,dbo.vm_currency
,dbo.vm_product_ml
,dbo.vm_ret_outlet_rep
,dbo.vm_sale_meas_period_ml
where
dbo.vm_sale_meas_period_ml.pk_vm_sale_meas_period_ml=dbo.vf_ret_outlet_sale_meas_ml.dk_vm_sale_period
and dbo.vm_currency.pk_vm_currency=dbo.vf_ret_outlet_sale_meas_ml.dk_vm_currency
and dbo.vm_ret_outlet_rep.pk_vm_ret_outlet_rep=dbo.vf_ret_outlet_sale_meas_ml.dk_vm_ret_outlet
and dbo.vm_product_ml.pk_vm_product_ml=dbo.vf_ret_outlet_sale_meas_ml.dk_vm_product
and (dbo.vm_product_ml.level_col_sdesc = '2-ProdCategory')
and (dbo.vf_ret_outlet_sale_meas_ml.dk_vm_fact_table_level = 510)
and (dbo.vm_sale_meas_period_ml.level_col_sdesc = '0-Day')
and (dbo.vm_sale_meas_period_ml.sale_meas_period BETWEEN '2017-06-01' and '2017-08-15')
group by
dbo.vm_currency.pk_vm_currency
, dbo.vm_ret_outlet_rep.pk_vm_ret_outlet_rep
, dbo.vm_product_ml.pk_vm_product_ml
order by
dbo.vm_currency.pk_vm_currency
,dbo.vm_ret_outlet_rep.pk_vm_ret_outlet_rep
,dbo.vm_product_ml.pk_vm_product_ml
Sales Month To Date
select
dbo.vm_currency.pk_vm_currency
,dbo.vm_ret_outlet_rep.pk_vm_ret_outlet_rep
,dbo.vm_product_ml.pk_vm_product_ml
,sum(dbo.vf_ret_outlet_sale_meas_ml.sale_extended_amount)
,sum(dbo.vf_ret_outlet_sale_meas_ml.sale_units)
from dbo.vf_ret_outlet_sale_meas_ml
,dbo.vm_currency
,dbo.vm_product_ml
,dbo.vm_ret_outlet_rep
,dbo.vm_sale_meas_period_ml
where
dbo.vm_sale_meas_period_ml.pk_vm_sale_meas_period_ml=dbo.vf_ret_outlet_sale_meas_ml.dk_vm_sale_period
and dbo.vm_currency.pk_vm_currency=dbo.vf_ret_outlet_sale_meas_ml.dk_vm_currency
and dbo.vm_ret_outlet_rep.pk_vm_ret_outlet_rep=dbo.vf_ret_outlet_sale_meas_ml.dk_vm_ret_outlet
and dbo.vm_product_ml.pk_vm_product_ml=dbo.vf_ret_outlet_sale_meas_ml.dk_vm_product
and (dbo.vm_sale_meas_period_ml.sale_meas_period BETWEEN '2017-08-01' and '2017-08-15')
and (dbo.vf_ret_outlet_sale_meas_ml.dk_vm_fact_table_level = 510)
and (dbo.vm_product_ml.level_col_sdesc = '2-ProdCategory')
and (dbo.vm_sale_meas_period_ml.level_col_sdesc = '0-Day')
group by
dbo.vm_currency.pk_vm_currency
, dbo.vm_ret_outlet_rep.pk_vm_ret_outlet_rep
, dbo.vm_product_ml.pk_vm_product_ml
order by
dbo.vm_currency.pk_vm_currency
,dbo.vm_ret_outlet_rep.pk_vm_ret_outlet_rep
,dbo.vm_product_ml.pk_vm_product_ml
In each of the above queries you can see that the starting date is the date of the beginning of the year, quarter, or month.
The end date is the “reporting date” which has been set to 2017-08-15 for this demonstration.
So you can see that even if this report was run historically it would only report up to the 15th of August.
This is why the level is set to the Day level.
Of course, a lot more rows have to be read if the data is stored at the day level as opposed to month level.
This is the trade off of being able to set the reporting date historically and to get the data that we consider “to date” from the data warehouse.
The beginning of the months, quarters and years is being filled in by parameters that are provided to the capsule.
Now, this is one way of getting the “days to date”.
To have a query go to the data base and get the first day of the month, quarter, year and put it into the query as a variable.
We have an alternative way of doing this and we will show you that in the next section.
Now we come to the most interesting of the three capsules.
This is the capsule to fetch the data for Sales Last Periods.
You can see the capsule in the image below.
So please allow me to explain each of the queries.
They are as follows:
- Sales TMLY – Sales This Month Last Year
- Sales LMTY – Sales Last Month This Year
- Sales TMTD – Sales This Month To Date
- Sales LMTD – Sales Last Month To Date
- Sales TMLYTD – Sales This Month Last Year To Date
So, why on earth would we want these five sets of numbers by comparison to the current “report date”?
Because comparing amounts period to last periods can show you where you have problems, or better than expected results.
This is especially so if you do not have plan tables.
Most companies do not do good detailed level planning for future sales.
In those cases looking at variances for this period versus prior periods is one way of finding problems in an “apples to apples” comparison.
So, again, we will cut and paste the SQL for each of these into the blog post.
Sales TMLY – Sales This Month Last Year
Select
dbo.vm_month.pk_vm_month
,dbo.vm_currency.pk_vm_currency
,dbo.vm_ret_outlet_rep.pk_vm_ret_outlet_rep
,dbo.vm_product_ml.dk_vm_product_category
,sum(dbo.vf_ret_outlet_sale_meas_ml.sale_extended_amount)
,sum(dbo.vf_ret_outlet_sale_meas_ml.sale_units)
from dbo.vf_ret_outlet_sale_meas_ml
,dbo.vm_month
,dbo.vm_currency
,dbo.vm_product_ml
,dbo.vm_ret_outlet_rep
where
dbo.vm_month.dk_vm_month_ly=dbo.vf_ret_outlet_sale_meas_ml.dk_vm_sale_period
and dbo.vm_currency.pk_vm_currency=dbo.vf_ret_outlet_sale_meas_ml.dk_vm_currency
and dbo.vm_ret_outlet_rep.pk_vm_ret_outlet_rep=dbo.vf_ret_outlet_sale_meas_ml.dk_vm_ret_outlet
and dbo.vm_product_ml.pk_vm_product_ml=dbo.vf_ret_outlet_sale_meas_ml.dk_vm_product
and (dbo.vm_month.first_day_of_month = '2017-08-01')
and (dbo.vf_ret_outlet_sale_meas_ml.dk_vm_fact_table_level = 509)
and (dbo.vm_product_ml.level_col_sdesc = '1-ProdGroup')
group by
dbo.vm_month.pk_vm_month
, dbo.vm_currency.pk_vm_currency
, dbo.vm_ret_outlet_rep.pk_vm_ret_outlet_rep
, dbo.vm_product_ml.dk_vm_product_category
order
by dbo.vm_month.pk_vm_month
,dbo.vm_currency.pk_vm_currency
,dbo.vm_ret_outlet_rep.pk_vm_ret_outlet_rep
,dbo.vm_product_ml.dk_vm_product_category
Now, it may seem to you that this looks exactly like other queries and you may be wondering:
“How exactly do we get this month last year from this query?”
Notice the line highlighted in red.
It is not joining the dk_vm_month field on the vm_month table.
It is joining the dk_vm_month_ly field which is the integer key for this month last year.
It’s a neat little trick that is not widely known, but it is very effective.
MicroStrategy consultants know about this because this is how MicroStrategy does this sort of thing.
We put on our time dimension tables the integer keys for the prior periods for the period of the row we are on.
We do this at day, week, month, quarter level.
We are quite surprised to not see these fields in implemented models we see.
They are very useful.
Sales LMTY – Sales Last Month This Year
select
dbo.vm_month.pk_vm_month
,dbo.vm_currency.pk_vm_currency
,dbo.vm_ret_outlet_rep.pk_vm_ret_outlet_rep
,dbo.vm_product_ml.dk_vm_product_category
,sum(dbo.vf_ret_outlet_sale_meas_ml.sale_extended_amount)
,sum(dbo.vf_ret_outlet_sale_meas_ml.sale_units)
from dbo.vf_ret_outlet_sale_meas_ml
,dbo.vm_month
,dbo.vm_currency
,dbo.vm_product_ml
,dbo.vm_ret_outlet_rep
where
dbo.vm_month.dk_vm_month_lm=dbo.vf_ret_outlet_sale_meas_ml.dk_vm_sale_period
and dbo.vm_currency.pk_vm_currency=dbo.vf_ret_outlet_sale_meas_ml.dk_vm_currency
and dbo.vm_ret_outlet_rep.pk_vm_ret_outlet_rep=dbo.vf_ret_outlet_sale_meas_ml.dk_vm_ret_outlet
and dbo.vm_product_ml.pk_vm_product_ml=dbo.vf_ret_outlet_sale_meas_ml.dk_vm_product
and (dbo.vm_month.first_day_of_month = '2017-08-01')
and (dbo.vf_ret_outlet_sale_meas_ml.dk_vm_fact_table_level = 509)
and (dbo.vm_product_ml.level_col_sdesc = '1-ProdGroup')
group by
dbo.vm_month.pk_vm_month
, dbo.vm_currency.pk_vm_currency
, dbo.vm_ret_outlet_rep.pk_vm_ret_outlet_rep
, dbo.vm_product_ml.dk_vm_product_category
order by
dbo.vm_month.pk_vm_month
,dbo.vm_currency.pk_vm_currency
,dbo.vm_ret_outlet_rep.pk_vm_ret_outlet_rep
,dbo.vm_product_ml.dk_vm_product_category
Again, the highlighted line shows that we are joining dk_vm_month_lm, last month, to the dk_vm_sale_period in the retail outlet sales measures table.
This gets us last months sales even if we constrain by the first day of this month as 2017-08-01.
Sales TMTD – Sales This Month To Date
select
dbo.vm_day_full.dk_vm_month
,dbo.vm_currency.pk_vm_currency
,dbo.vm_ret_outlet_rep.pk_vm_ret_outlet_rep
,dbo.vm_product_ml.pk_vm_product_ml
,sum(dbo.vf_ret_outlet_sale_meas_ml.sale_extended_amount)
,sum(dbo.vf_ret_outlet_sale_meas_ml.sale_units)
from dbo.vm_currency
,dbo.vm_product_ml
,dbo.vm_ret_outlet_rep
,dbo.vm_month_to_date_01
,dbo.vm_day_full
,dbo.vf_ret_outlet_sale_meas_ml
where dbo.vf_ret_outlet_sale_meas_ml.dk_vm_sale_period=dbo.vm_month_to_date_01.dk_vm_mtd_day
and dbo.vf_ret_outlet_sale_meas_ml.dk_vm_ret_outlet=dbo.vm_ret_outlet_rep.pk_vm_ret_outlet_rep
and dbo.vf_ret_outlet_sale_meas_ml.dk_vm_product=dbo.vm_product_ml.pk_vm_product_ml
and dbo.vf_ret_outlet_sale_meas_ml.dk_vm_currency=dbo.vm_currency.pk_vm_currency
and dbo.vm_month_to_date_01.dk_vm_day=dbo.vm_day_full.pk_vm_day
and (dbo.vf_ret_outlet_sale_meas_ml.dk_vm_fact_table_level = 510)
and (dbo.vm_product_ml.level_col_sdesc = '2-ProdCategory')
and (dbo.vm_day_full.day_date = '2017-08-15')
group by
dbo.vm_day_full.dk_vm_month
, dbo.vm_currency.pk_vm_currency
, dbo.vm_ret_outlet_rep.pk_vm_ret_outlet_rep
, dbo.vm_product_ml.pk_vm_product_ml
order by
dbo.vm_day_full.dk_vm_month
,dbo.vm_currency.pk_vm_currency
,dbo.vm_ret_outlet_rep.pk_vm_ret_outlet_rep
,dbo.vm_product_ml.pk_vm_product_ml
In this query we are showing you another trick we like to use.
You can see the full date table is set to ‘2017-08-15’ as we are demonstrating a report as of that date.
Just above that you can see that the pk_vm_day key in vm_day_full is joined to vm_month_to_date_01.dk_vm_day.
And above that you can see that the dk_vm_sale_period field on the retail outlet sales measures table is joined to vm_month_to_date_01.dk_vm_mtd_day.
We have a series of tables called:
- vm_week_to_date_01
- vm_month_to_date_01
- vm_quarter_to_date_01
- vm_year_to_date_01
These tables keep the relationship between a date, such as ‘2017-08-01’ and the dates that are included in the sets of week to date, month to date, quarter to date, and year to date.
These can be calendar dates or 4-5-4 months that retailers like to use.
These tables are found in pretty much all MicroStrategy implementations because this is how MicroStrategy does periods to date.
We decided we would show MicroStrategy the respect of copying this idea as we think it’s a nice way of solving this problem.
Nicer than having queries look up these dates and set them as parameters.
In this case you can see you only have to provide the “report date” and you do not have to calculate the start date of the week, month, quarter or year.
Sales LMTD – Sales Last Month To Date
select
dbo.vm_day_full.dk_vm_month
,dbo.vm_currency.pk_vm_currency
,dbo.vm_ret_outlet_rep.pk_vm_ret_outlet_rep
,dbo.vm_product_ml.pk_vm_product_ml
,sum(dbo.vf_ret_outlet_sale_meas_ml.sale_extended_amount)
,sum(dbo.vf_ret_outlet_sale_meas_ml.sale_units)
from dbo.vm_currency
,dbo.vm_product_ml
,dbo.vm_ret_outlet_rep
,dbo.vm_month_to_date_01
,dbo.vm_day_full
,dbo.vf_ret_outlet_sale_meas_ml
where
dbo.vf_ret_outlet_sale_meas_ml.dk_vm_sale_period=dbo.vm_month_to_date_01.dk_vm_mtd_day
and dbo.vf_ret_outlet_sale_meas_ml.dk_vm_ret_outlet=dbo.vm_ret_outlet_rep.pk_vm_ret_outlet_rep
and dbo.vf_ret_outlet_sale_meas_ml.dk_vm_product=dbo.vm_product_ml.pk_vm_product_ml
and dbo.vf_ret_outlet_sale_meas_ml.dk_vm_currency=dbo.vm_currency.pk_vm_currency
and dbo.vm_month_to_date_01.dk_vm_day=dbo.vm_day_full.dk_vm_day_lm
and (dbo.vf_ret_outlet_sale_meas_ml.dk_vm_fact_table_level = 510)
and (dbo.vm_day_full.day_date = '2017-08-15')
and (dbo.vm_product_ml.level_col_sdesc = '2-ProdCategory')
group by
dbo.vm_day_full.dk_vm_month
, dbo.vm_currency.pk_vm_currency
, dbo.vm_ret_outlet_rep.pk_vm_ret_outlet_rep
, dbo.vm_product_ml.pk_vm_product_ml
order by
dbo.vm_day_full.dk_vm_month
,dbo.vm_currency.pk_vm_currency
,dbo.vm_ret_outlet_rep.pk_vm_ret_outlet_rep
,dbo.vm_product_ml.pk_vm_product_ml
Sales TMLYTD – Sales This Month Last Year To Date
select
dbo.vm_day_full.dk_vm_month
,dbo.vm_currency.pk_vm_currency
,dbo.vm_ret_outlet_rep.pk_vm_ret_outlet_rep
,dbo.vm_product_ml.pk_vm_product_ml
,sum(dbo.vf_ret_outlet_sale_meas_ml.sale_extended_amount)
,sum(dbo.vf_ret_outlet_sale_meas_ml.sale_units)
from dbo.vm_currency
,dbo.vm_product_ml
,dbo.vm_ret_outlet_rep
,dbo.vm_month_to_date_01
,dbo.vm_day_full
,dbo.vf_ret_outlet_sale_meas_ml
where
dbo.vm_month_to_date_01.dk_vm_mtd_day=dbo.vf_ret_outlet_sale_meas_ml.dk_vm_sale_period
and dbo.vf_ret_outlet_sale_meas_ml.dk_vm_ret_outlet=dbo.vm_ret_outlet_rep.pk_vm_ret_outlet_rep
and dbo.vf_ret_outlet_sale_meas_ml.dk_vm_product=dbo.vm_product_ml.pk_vm_product_ml
and dbo.vf_ret_outlet_sale_meas_ml.dk_vm_currency=dbo.vm_currency.pk_vm_currency
and dbo.vm_month_to_date_01.dk_vm_day=dbo.vm_day_full.dk_vm_day_ly
and (dbo.vm_day_full.day_date = '2017-08-15')
and (dbo.vm_product_ml.level_col_sdesc = '2-ProdCategory')
and (dbo.vf_ret_outlet_sale_meas_ml.dk_vm_fact_table_level = 510)
group by
dbo.vm_day_full.dk_vm_month
, dbo.vm_currency.pk_vm_currency
, dbo.vm_ret_outlet_rep.pk_vm_ret_outlet_rep
, dbo.vm_product_ml.pk_vm_product_ml
order by
dbo.vm_day_full.dk_vm_month
,dbo.vm_currency.pk_vm_currency
,dbo.vm_ret_outlet_rep.pk_vm_ret_outlet_rep
,dbo.vm_product_ml.pk_vm_product_ml
From these 5 queries you can see they are returning the month key for 2017-08-01, the currency key, the outlet key and the product category key.
You might be a little confused about how the pk_vm_product_ml field is the category key.
It is the category key because we are querying at the product category level in the vm_product_ml table.
That means the key that is returned will actually be the product category level key.
To show you what you get in a spreadsheet when you run these five queries and then join them ,as is done in the capsule above?
This is the result you get.
The figures have been removed for confidentiality purposes.
This sort of query is not possible to do inside Excel because it requires access to sales data at the day level back to the prior year.
Excel can not do this for companies with any significant number of sales.
This is why products like SSAS and Essbase are required to make these sorts of calculations possible in Excel.
However, when you do these calculations in SSAS or Essbase you have to create another layer of data and that new layer of data has to be maintained.
We come from the school of thought that says data should be stored in databases.
And given that our databases are getting faster and faster, these “extra databases” are really only necessary when you just can not squeeze the performance you want our of your underlying data warehouse database.
By putting the results into the Excel Powerpivot Cube we remove a lot of the need for products like SSAS and Essbase.
We do not remove the need for them entirely.
But we remove the need to use them all the time for every report.
Our view is this.
If the report the business user needs can be delivered with the data inside Excel in the Power Pivot model and it meets the users needs and s/he is satisfied with it?
That’s a better way to go than using SSAS or Essbase for that report.
This turns out to be the majority of reports in most small to medium sized companies.
With Meta5 doing the calculations against the data warehouse and delivering the joined results to the spreadsheet?
This makes such reports possible for business users to create for themselves as long as they have access to the sales data in the data warehouse.
Now we will show you what this looks like in the Excel Power Pivot Model.
In the diagram below you can see the four dimension tables at the top.
Please click the image for a full sized version that is clearer.
These are “normal” and “standard”.
You can see that there are three fact tables.
You can see the fields that are in each of the tables.
You can see that Meta5 has summarized and delivered the data that is needed for this head office report to Excel.
There is no live connection needed back to SSAS or Essbase or any other database.
Meta5 allows you to have the option of creating quite sophisticated excel dashboards without the need for the live connection.
This is by delivering the already summarized and joined data in to Excel Power Pivot models.
If you love Excel, and tens of millions of people do, and you want your reports in Excel?
Meta5 provides a better way of getting your reports delivered to you daily.
These reports contain just your data in the Power Pivot models.
These reports require no live connection back to your data warehouse server.
And if you, or someone in your department is good at building Excel reports?
They can certainly learn how to build them using Meta5.
Of course, if you would like us to help you build such reports?
We would be only too pleased to help you.
In Summary
In this blog post we wanted to show you how Meta5 can be used to create quite sophisticated dashboards by delivering summarised and prejoined data to the excel power pivot models.
You can see from the blog post is that there are 4 dimension tables and three fact tables that will be delivered to the final Excel power pivot model.
The way that Meta5 works is that you create a capsule for each of the tables to be produced.
That capsule is responsible for querying the data warehouse, performing joins, and doing any other work necessary, to deliver the data to the “collect data” meta5 spreadsheet.
The data will then be sent from the “collect data” spreadsheet to the Excel Power Pivot Models.
If you would like to take Meta5 for a spin yourself?
We have a free evaluation service for qualified prospects.
We have training materials that will let you build two dashboards for yourself using Meta5.
These are the:
- The Adventure Works Demo Dashboard
- The Demographics Dashboard
If you are an “Excel Person” who wants to evaluate Meta5?
These two Dashboards take 2 days and 3 days to create respectively.
So you could easily spend approximately one week taking Meta5 for a spin to see how you like it before you make any commitment.
Of course, after a week learning how to build such dashboards you will have all the knowledge you need to make your decision whether to invest in Meta5 to increase your companies profit.
We are confident that if you take Meta5 for a serious spin you are going to want to use it in your daily work.
The training materials for these two dashboards are on the “How Do I” section of our main web site.
You can review these videos by clicking on the button below.
You are welcome to “look them over” to see if you want to inquire about our free evaluation service.
Of course, please just contact me if you want to take advantage of our free evaluation service for qualified prospects.
And with that?
I would like to say thank you very much for your time and attention.
We really appreciate you dropping by to read our blogs.
Best Regards.
Mihai Neacsu
Business Development Manager.
The BIDA Team.
Ask Us Anything
You are welcome to ask us any question you would like below on our contact form.
We will be emailing our subscribers useful and
valuable information regarding business intelligence.
Please subscribe here to be on our general emailing list.
