Hello and welcome to our latest blog post here on BIDA Brains.
I am pleased you have come back to see our latest blog post.
Thank you.
In our last blog post we talked about why is Meta5 so easy to use and why BIDA is promoting Meta5.
In this blog post we just want to give you a small example.
In this blog post we will show you how it is possible for a business person to create a plan vs actual mini fact table for an Excel PowerPivot Model in Meta5
Creating a plan vs actual mini fact table is a very common process in Excel dashboarding.
One of the most basic dashboards is “Plan vs Actual”.
In our case we are adopting and promoting the Stephen Few Bullet charts.
So as well as “Plan vs Actual” we also use “lower limit” and “upper limit” to give more context to the target value.
Of course, plans are usually stored in spreadsheets and are held at monthly level.
They are usually held at a fairly high level on the product hierarchy as well, such as product category.
We propose to help customers create sales plans at much lower levels so that product order processing can be optimized to reduce the amount of product that is held in stock.
So, let us consider the following case in our example.
Plans are held at monthly level.
Actual sales are held at detailed level and summarized along many dimensions to both daily and monthly levels.
Therefore, historically, actual sales are available at common levels such as day, week, month.
Products are available at common levels such as Product Group and Product Category.
Any business person who is proficient with Excel can build the following capsule given a relatively modest amount of training.
The only component a business users might need help with is the “Function” icon.
So please review the capsule and we will explain what each icon is doing below.
This capsule would be passed a parameter called “Reporting Date”.
This is the date that the reported is considered to be for.
Usually it will be yesterdays date since we will usually have data complete at close of business prior day.
In this example we are using 2017-08-15 as the Reporting Date because we are using old customer data in testing.
Planned Sales LTM is querying the database to fetch the planned sales for the “Last Twelve Months” prior to the current month of the “Reporting Date”.
Actual Sales LTM is querying the database to fetch the actual sales for the Last Twelve Months prior to the current month of the “Reporting Date”.
We join the two queries and we send the result to a spreadsheet.
The join is an outer join.
This spreadsheet will have all plan records and all sales records for the Last Twelve Months.
Then we need the plans vs actuals for this month.
However, the plan is at the monthly level and the actuals are at the daily level.
So what we propose is that we will pro-rate the plan by the number of days that have passed in the month in relation to the number of days in the month.
Now, we all know this is not the greatest way to pro rate a plan, but it is the simplest way and makes for a good demonstration.
We retrieve the Planned Sales This Month and send them into the join.
We retrieve the Report Date This Month.
This will include retrieving the month level key, the number of days elapsed so far in the month as per the reporting date, and the number of days in the month.
The join will produce a spreadsheet with the planned sales, the number of the day in the month, and total number of days in the month.
This data will flow through the Function icon.
Now, we are prepared to accept that maybe a proficient excel user would not be able to use the Function icon.
But please allow us to show you what s/he has to put into this icon and you can be the judge.
Here are the controls for the Function icon.
The business user has to enter the formulas you see separated by commas.
$A, $B, $C, $D means that these columns will go forward unchanged.
The next 8 formulas separated by commas means that each column will be multiplied by the number of the day in the month then divided by the number of days in the month.
We would argue that good Excel users can enter a formula like e * k / l .
That is not too much of a stretch if you ask us.
Even so, you can see that there would be “someone around” who could help out with such formulas.
After the data flows out of the function icon the plans have now been pro-rated by the number of the day in month for the reporting date divided by the number of days in the month.
You can see an icon to fetch the Actual Sales for this month.
It will fetch these sales at the daily level and summarise them up to the monthly level.
The plans and actuals for this month are then joined.
And then they are appended to the plans vs actuals for the prior 12 months.
The result is put into a spreadsheet and sent “out” of the capsule.
If you do not want to take our word that a business users proficient in Excel can build such a capsule?
We have a free evaluation service for qualified prospective customers.
You are welcome to try this out for yourself if you wish.
Now, the magic of Meta5 is that the business users can perform their own analytics.
We take the position that by empowering business users to answer their own questions this is “better” than requiring them to go to IT to ask their questions.
There are many reasons why we take this position.
So given that a business user can use Meta5 to answer his/her own questions?
We claim that is inherently better than having to get anyone from IT involved in a piece of analysis work.
The SQL that is generated by each of the query icons is very simple.
The trick is that this data can be joined in Meta5, can have calculations performed on it such as pro-rating, and can be forwarded to Excel very easily.
Of course, SQL programmers would say:
“But I can do this in SQL!”
And they can.
Please click the button below and you will be able to download the SQL equivalent of this capsule as a zip file.
You can unzip it and open it in your favorite text editor.
The spacing is likely to be out because we use tabs in the writing of such code.
In the SQL file you can see we have included quite detailed documentation.
If you can read SQL you can see that it is performing 4 sub queries and then summarizing those sub queries.
It does not need to perform a separate query to get the day number in month and number of days in the month as this can be done with just a left join.
You can see that yes, indeed, this SQL delivers the exact same functionality as the capsule above.
We can load this SQL into the bida_sql_statements table.
We can fetch it from that table using Meta5.
We can then send it into the data warehouse to execute.
It will return the exact same results as the capsule above.
And yes, we did test it to prove that it did.
However, and it is a BIG HOWEVER.
No business person can write the sql you are reading.
In fact it took our Professional Services Manager about 4 hours to get that fragment of SQL to work exactly the same as the capsule.
It took him less time to build the capsule in the first place.
So even if the person creating the capsule CAN create the SQL equivalent?
It is faster to create the capsule.
Business users can not create SQL like this.
But they CAN create capsules like this.
And that is the magic and beauty of Meta5.
Business users can create their own capsules, do their own analysis, get data directly from where ever they are authorized to get it.
They don’t need to go to IT and explain what it is they want.
As long as they are authorized to query the data they need they can get it for themselves.
No matter what companies say about “self service data analysis”.
Meta5 remains the king of the “self service data analysis” products in our humble opinion.
But don’t take my word for it.
You can take Meta5 for a free evaluation spin yourself if you work for a qualified prospective customer.
We have created two very detailed training videos to give prospective customers the chance to learn how to use Meta5 and to take it for a detailed “spin”.
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.
You are welcome to try your hand.
You can review these two video training courses by clicking the button below.
In Summary
In this blog post we wanted to show you the difference between using Meta5 to create a plan vs actual mini fact table to be sent into Excels Power Pivot model versus using SQL.
We wanted to give you a very clear example of how Meta5 can empower business users to create their own analysis versus needing to go to IT and ask someone to please get the data from the data warehouse into Excel to do the same.
The plan vs actual is a good example because it is such a typical example of comparing data that comes from two different places across a set of common dimensions.
This is very standard work for business users who are doing data analysis.
We hope you liked this example.
We hope you liked this blog post.
If you have any questions or comments please feel free to get in touch with us.
My details are on the contacts page.
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.
