Sometimes we want uniformity in our dashboards...

We want all our pages to have a common theme or useability that the customer or end-user can rely on. For me, I like my Dashboards to have a central Navigation Pane that allows the users to not only move through the different Dashboards but also provides a common slicing selection that can be used for most of my dashboards. Of course, there may be times where we need to alter this common pane to accommodate for different visualizations or slicing options, but for the most part the same structure can be used to great effect. 

To get started, create slicers for each filter selection you would like the end-user to be able to filter by. It’s important to note as well that if something I want to Slice against exists in both the Fact Table and the Dimension Table I will take it from the Dimension Table. The general rule that we want to follow is to almost always slice Facts against Dimensions. 

The slicers will make the basis of the Filter Header. 

Next, we need to construct the navigation pane that will house these slicers. I like to choose a basic setup of Navigation Arrows, one for going back one page and the other for going forward a page, a custom “Home” button and a “Q&A” button to allow the end user to ask questions directly of the data. These buttons are then placed on top of a text box to include the title of the page. If you lose your slicers behind the textbox, don’t worry we’ll cover how to edit Layer Order shortly. You may choose to go a different route, what I have found is there are a million different ways to accomplish the same task in Power Bi. If you choose not to have Navigation arrows that is ok, do what you feel will provide the required level of functionality for your dashboard. 

To create the custom “Home” button, simply navigate to Add a button and select a Blank button. This is quite a useful option as you can create a button with a custom image that is linked to an action within the dashboard. 

To link this newly created button to an action, click on the button and go to the Format button pane, the second last option is Action. 
 

 When the user clicks this button they will be taken to the PowerBi page titled “Home Page”. 

Now a bit of housekeeping

Adding some labels to the navigation arrows and changing the background to match that of the text box. 

Next, we need to change the slicer types to best fit in the navigation pane. In doing so I will also alter the year selector to only show the last 4 years as this data will be most relevant. 

Dropdowns for most of the selections will make for a clean easy to use solution, with buttons for the Years to add some variety. 

Now the issue we face is that the dropdowns have disappeared behind the yellow text box as seen below.  

This can be remedied by changing the Layer Order. This is found under the ‘View’ tab and ‘Selection’. 

Under this Selection menu, we can change the layering order of the elements we have within the Dashboard. We want to move the text box containing the title to the back and move the dropdowns to the front. This is done by clicking and dragging the elements in the side menu into the desired order. 
 

All that is left to do is to group all the navigation pane elements together to allow us to easily duplicate this to be able to copy and use this on our other dashboard pages. To do this in the Selection side menu select all elements of the navigation pane, group them and name the group to distinguish it from any other groups we may create in the future. 

We now have a complete Navigation Pane that we can utilize throughout the dashboard as we see fit. The Selection and Grouping functions are useful tools that can be mastered to create intricate, multi-layered dashboards and can save you a mountain of time when wanting to re-use large parts of a dashboard across the Power BI file or files. 
 
I hope you have learnt something new or improved your skills in Power Bi by reading this article. As always feel free to reach out on Discord if you have any questions or want to chat about Data. 

In one of my previous RallyPoint Articles, Star Schema’s and Fact vs Dimension Tables, I introduced you to the Star Schema and the importance of Dimension Tables. In case you missed it or need a refresher, a Dimension Table is a static reference table used within a Star Schema Data Model to take the load off the main Fact Table (containing specific event information).  

In order to fully maximize the use of this type of Data Model

we need multiple reference tables (Dimension Tables) spread around the Fact Table, and one of the most required references is Time. Almost every Fact Table will contain some type of “time” reference containing time-specific data on when specific event/s occurred. While Power Bi does have built-in Date Splicing Functionality, we are limited to only slicing by Year > Quarter > Month > Day. It can also become difficult to compare rows between Fact Tables if there is no common Date Dimension to slice by. 
 
This issue can be solved quickly with an imported Power Query script specifically designed to give you your desired date range with the flexibility to drilldown.  

First download the script below:

After that, choose the PowerBi field you want the Time Dimension Table added to and add a Blank Query: 

Next, in the newly opened Blank Query, navigate to the View tab and click Advanced Editor. 

Following that, open the script in a text editor (such as notepad) and copy and paste the contents into the Advanced Editor. 

The only parts that require manual input are the FromYear and ToYear. Change these dates to suit your needs and click done.  

What you are left with is an extensive Time Dimension Table that acts as a centralized time splicer and can be utilized to provide in-depth analysis of your data! This may not seem important for your simple 2 table dataset but once you start to add complexity and additional data from other sources the Time Dimension Table pays for itself! 

 
As always if you have any issues with this, or anything else data related, feel free to reach out! 

christopher-jaames.dennis@withyouwithme.com 

If you are like me and have completed the WYWM Data Analytics Pathway...

but you want to keep your skills sharp, then you need to get your hands on some data and get to work. The question is though, where are you getting your Data? All the exercises and projects within the Pathway have the Data already organized and provided for you, it is a nice luxury to have but in the real world it is up to you, the Data Analyst, to find the Data required to make that exciting, thought-provoking dashboard. 
 
I am still quite a junior in the DA world, and I am ashamed to admit that my Python skills are almost non-existent. I am trying to learn, but it is just taking a little longer than anticipated. So, when I had the idea to find some data relating to the Property Market in West Melbourne, I found myself stuck. I searched far and wide for Data Sets that would fit my needs, alas to no avail. 
 
That is when I stumbled across a solution to my problem, Web Scraping. Web Scraping is the process of reading the underlying HTML (Hypertext Markup Language) code of a website, extracting the parts that we need, then attempting to format the extracted data in such a way for us Data Analysts to go about our work. Now, this may be off-putting if you do not have any experience with HTML, but the solution I chose to use makes the process as easy as clicking a few buttons. A word of warning though, Web Scraping is not considered illegal in Australia, however, it can be in breach of the website's terms and conditions of use. As a general rule of thumb, if you are not using the data for commercial gain and only personal use, you have nothing to worry about. 
 
The solution I chose was the Web Scraper – Free Web Scraping plugin for Chrome. This is a little-to-no code solution that allows us to set up our scrape by selecting which HTML elements we want the information from. 
 
The example I will be using is the West Melbourne Property Data, scraping from a real estate website. Some websites are quite protective of their data, evident by restrictions they put in place to stop people from extracting a complete dataset. The main issue I faced was that I could not view beyond 50 pages of property data and each page only contained 25 properties. To work around this, I set up multiple scrapes of more narrowed searches. For example, instead of searching for multiple suburbs, I would search for each individual suburb and do the scrape, then change the “sort” function and re-scrape to try and get a wider set of data. 

To get started

Head over to the Chrome Web Store and install the plugin here. After installing, navigate to the page you want to scrape and hit F12 on windows or to open the Developer Tools and click on Web Scraper. 

For the first step, let’s create the Sitemap by giving it a title and copying in the URL of the website that we want to scrape. Since we want to scrape from multiple pages, we will find the page number and replace it with square brackets and a range e.g. [1-50]. Since I know that Real Estate.com won’t allow us to search beyond 50 pages, I changed the URL to search for pages 1 to 50. 

Once the Sitemap is good to go, we then need to set up our selector. The selector is the element of the HTML that we are going to have the Web Scraper take from each webpage. To do this, click the “Add new selector” button. From here, give the selector a descriptive name and select the “Type” as “Text”, this will pull the actual text that the HTML represents on the webpage. The next stage is to select the HTML that we want to scrape. You would usually make a selector for each piece of information you want to scrape and tie this back to a Parent Selector, however, in this instance due to the way that Real Estate.com has formatted their HTML, it is easiest to scrape the whole ‘residential-card’ and then separate in the data cleaning process. Upon selecting the HTML, also remove the address identifier to tell the web scraper to scrape all instances of the div.residential-card rather than just the one that we have physically selected. Select “Multiple” and hit save. You can also hit the Data Preview button to make sure that you have indeed selected the correct data as intended. 

Now it is as simple as starting the scrape. The plugin will then open a mini-window and start querying the 50 pages we have selected.  

Upon completion, simply hit refresh in the original Web Scraper window (not the pop-up window) to display the data and then export as a CSV ready to clean. This sort of data is not normalized or uniform, so you will have to be creative with your cleaning. For this data, I found that not all listings had complete data. For example, some properties did not list a land size or have garages. I challenge you to try this out for yourself and see what sort of data you can scrape! Have fun! 
 
Thanks for reading and I hope this has a useful application in your Data Journey. Feel free to try it out yourself and reach out if you have any issues! 

Reach out to any of my managers in my previous job and they will tell you that my ability to use and analyse data in SAP ERP made them distinctly uncomfortable at times. Let me tell you why.

WithYouWithMe Chief Customer Officer (and total force of nature) Tom Larter loves to deliver what he calls Truth Bombs to customers. Truth Bomb No 3 - Focus on Skills over Controls - speaks to the tendency of Big Business to rely on control heavy Big Data Management practices. It's easier to teach employees how to follow rules than it is to change their ways of working. It's also easier to segregate and control access to data than it is to embed desirable data management practices in the company culture.

Excessive organisational controls around data slow down time to decisions, increase both time to insight and customer response – slowing down the whole business. In business - as in the military - anything that slows down response times to situations can put the organisation at a distinct disadvantage.

Digitally skilled workforces reduce time to decisions, speed up response to customers (or on the battlefield) and supercharge the ability of the team to respond to insight.  In the SAP world a digitally skilled individual is referred to as a Super User.

The SAP ERP Data structure in the multinational organisation I was working for was highly segregated and tightly controlled. SAP calls it Role Based Authorisation. In the military it's called "Need to Know" - if you don't need to know it you can't see it and you aren't allowed to make decisions on it. My job was in the office doing administration - about as low as you can get - with appropriately restricted data access and management authorisations.

Normally - someone in my role would have had very limited visibility on organisational data - hence a very restricted ability to act quickly in the interests of the business. The key difference in my situation was that my composite role required direct access into most of the SAP ERP Data Silos. I could see more than I should have been able to.

In practice - all of the controls around data visibility didn't apply to me. I could see through the silo walls and I wasn't afraid to look.

In many ways I had wider and more immediate visibility on the daily tactical business data than my Managers did.

Did that worry them? You bet it did!

Did I leverage that tactical advantage in the interest of the business? You can count on it.

Did I seek permission on every data driven decision? Nope.

Did they always like it when I used that data to hold them accountable? Nearly got sacked for it.

Did I defer to their position when my military logistics/project management experience showed me a better course of action? They wish.

Did I stay in my lanes? No fun in that and no advantage to the business profitability.

Did I use that visibility to clean up data and fix business processes? Oh hell yes.

Did I use that data to cut costs and reduce wastage? What do you think?

Was I a pain in the butt? Unquestionably.

Did all that improve the bottom line?

In the millions.

One person. Using the data available.

Train your people.

Want to join our community?

It’s no secret that I am mostly self taught when it comes to SAP. 

I first encountered the system when I started at job at Holcim Humes Australia which was titled “Production Works Clerk”. It was a bottom level data entry/administration role in a small country concrete manufacturing plant which was part of a large multinational specializing in quarrying and concrete products. The Humes subsidiary was the only part of the company which manufactured finished concrete products. Holcim (now Lafarge Holcim) focusses on quarrying and bulk concrete delivery in mobile agitator trucks.  

It has only been in the last twelve months or so that I have dived into formal SAP learning. I think I received maybe 1 week on the job introduction to SAP when I started in the role. Crucially – the person handing the job over to me still operated a paper based office and was clearly uncomfortable with the database. 

I remember I was introduced somewhat tentatively to the Graphical User Interface (GUI) version of SAP that the Company was running.  There was no way the other ladies in the office were going to be able to teach me much - they were too afraid of the database and both always stayed well inside their designated lanes. I won’t lie – the interface was confronting and somewhat clunky but I applied the veteran concept of “this is the tool I’ve been given – I'd better learn how to use it”. 

So I did. 

I’ve only just recently realised that the exposure I gained in operating SAP Enterprise Resource Planning (ERP) functions in that little country plant has made me somewhat of a rare animal when it comes to SAP.  

Let me explain.  

I am an Operator – a very good one – referred to as a Super User. I am the person who enters, uses and maintains the data all day, every day.  

Normally a person operating SAP within a company will only operate in one area of specialty. The person paid to do Payroll will only do Payroll. The person doing Inventory Management will have nothing to do with Purchasing or Dispatch. The person who takes sales orders might not even be in the same location as the person doing Production Entry. SAPs role based authorization system is designed to segregate incompatible roles. More on that in another article.  

My company ran the full SAP Enterprise Resource Planning Suite to varying levels.  

The topic list for SAP’s TS410 Integrated Business Systems Course (S/4 HANA ERP) looks like this: 

1. SAP S/4 HANA Enterpise Management Overview 

2. SAP Fiori 

3. System Wide Concepts 

4. Record to Report Processing 

5. Hire to Retire Processing 

6. Source to Pay Processing 

7. Warehouse and Inventory Management 

8. Design to Operate Processing 

9. Lead to Cash Processing 

10. SAP Project Systems 

11. SAP Enterprise Asset Management 

We were a very small Plant so all of the office staff had multiple areas of responsibility and we all at one time or another swapped and shared roles. No one would have ever been able to take leave if we didn’t.  

My military training was in Army Road Transport Supply Chain Logistics and Human Resources Management. I was woefully underemployed in the role but that’s another story.  

Honestly – putting me in that job was like using a Main Battle Tank to eradicate mice in your loungeroom. Lets just say I became known (if not loved) for using the information in SAP to challenge the status quo and drive improvement. I even managed to make SAP sort of fall over more than once when I ran large scale searches for analysis.  

If you want to know how much I know about SAP ERP, or how my military experience applied to my SAP Operator roles – read on.  

• SAP S/4 HANA Enterpise Management Overview- this just talks about the training content and basic history of the system – read on.  
• SAP Fiori. Fiori is a User Interface (how the system looks and behaves for the user) product which I didn’t have access to. I wish I had. It’s a lot nicer, more attractive and easier to use than the version I learned.  
• System Wide Concepts. In the military we have a thing called an Order of Battle or ORBAT. An ORBAT is more or less a mind map of the way the business is organised. In SAP it basically means a mind map of how the Business Data is organised, how it moves, where it goes, who can see and use the data. If you understand what an ORBAT is and how it works you can understand how SAP data is organised in real life. SAP even numbers and categorizes its Units and people much like the military does.   

Enough of the general information.  

Why is my SAP skill set rare? 

Because I made myself into a Super User in four of the branches of ERP. I had experience in three others thanks to my military training. I made it my business to learn the eighth (although a lot of it still goes over my head). I more or less did the Army Officer thing of wanting to know everything that was going on around me. I didn’t stay in my lanes and was constantly asking “Why has this gone wrong?” and “How can it be done better?”.  

It almost never happens in business where one person will operate more than one branch of SAP ERP. I’ve covered all of them to some extent. 

• Record to Report Processing. On The Job Learning. I didn’t really know that much about high level strategic finance management and I still don’t. I know what general ledgers, cost centres and profit centres are and how to allocate them for cost/profit analysis. I can also conduct analysis on the data available and make recommendations.  I’ll leave the legalities of high level finance management to those better qualified. Essentially I know enough about this to know when I need to defer to an accountant or a Business Analyst.  
• Hire to Retire Processing. Direct Responsibility. I learned most of what I know about HR management as an Army Officer. I had experience running crews of people in multi-specialty logistics units both in Australia and in East Timor. One of my first jobs out of the Army was in Occupational Rehabilitation which covered any gaps in my knowledge around OH&S in the civilian sector. My only responsibility strictly speaking in SAP was the data entry of time sheets. In SAP the Cross Application Time Sheet (CATS) transaction also collects labour hours for production cost and maintenance cost tracking. CATS also tracks and manages leave and troops to task. About the only thing I didn’t do was pick who was hired, what they got paid and who got fired – but I ended up doing most of the paperwork. 
• Source to Pay Processing. Direct Responsibility. Successfully operating the Purchase to Pay Processing in SAP is a detail and deadline heavy process. When you put a trained military logistician in a purchasing role in SAP you automatically get a Logistics/Resupply manager. Source to pay in SAP also touches on inventory and fleet/asset management with more than a sprinkling of Project Management. I tracked and managed international supply chains for Just In Time Delivery and in nine years in the job I was only ever late with one delivery by one day. I also maintained perfect results in Vendor/Supplier Payment KPIS for most of my time in the role. I am not inflating anything when I say I was the best in the company. 
• Warehouse and Inventory Management. Direct Responsibility. I first learned the fundamentals of warehousing and stock control in the military. Strictly speaking my SAP Role was to enter data to administer Stocktakes. The goal was to make the inventory in SAP match the actual stock on the ground perfectly – not nearly as easy as it sounds. I couldn’t even approve stock adjustments. Attention to detail and rigid adherence to process is the name of the game here. Again – when you put a Military trained Logistician into a role like this you automatically get an Inventory Manager. In my time at the company Annual Stocktake Stock level variances in my plant went from around 20% to about 1.5% (with the right management support). Inventory Management in SAP also touches on Production Recording and Dispatch. Because my job also included Production Entry I could see and resolve every problem at every step of the process. I don’t mean change the Data to make it look good. As an ex Army Officer I would find the root cause of the problem (usually training/education) and do what I could to fix that. I spent a lot of time educating and working with Yard Operators on the importance of correct reconciliation of Picking Tickets and Delivery Dockets.  
• Design to Operate Processing. Direct Responsibility. Design to Operate is the process of making things. I thought I knew nothing about Production when I started entering daily production data into SAP. I knew a lot more than I first realised. I planned and executed all of the purchasing for production. It’s a Project heavy process so I leaned heavily on my military resource and time management experience. I conducted root cause analysis for problem solving and implemented solutions within my limited area of influence (remember – I was “only the office lady”). When you put a military trained logistics officer into a role like this you shortly get something that looks very much like an Assistant Production Planner. With the right Management support the accuracy of the production planning data and production reporting improved to within a 1.5% inventory variance tolerance - and our cost capture was top notch. 
• Lead to Cash Processing. Sales and Distribution. I didn’t operate directly in this area. I had to lean about sales but I was an Army Road Transport truckie. I knew exactly what the Dispatcher needed from the inventory data to be able to do his job – and I made sure he got it.  
• SAP Project Systems. Learned in the Military. We had the SAP Project System module but we did not use it. I spent some time several years ago exploring the capability to see if it might be appropriate to use for Capital Asset Acquisition but we did not need that level of complexity. It is my firm belief that nobody trains better project managers than the military. Troops to task, budget management, time line planning, staged implementation, cost tracking. It’s all there. Just put the appropriate data in the right box.  
• SAP Enterprise Asset Management. Learned in the Military. Transport truckie = fleet management. Not a great leap from there to Asset Management. Because I was operating procurement I picked up a pretty good working knowledge of Capital Asset Acquisition and management. If you can plan and implement preventative maintenance on fleet – you can do the same for Buildings and Capital Assets. Because of high management turnover the National Asset Manager and I worked fairly closely on the Capital Asset Maintenance in the plant – simply because I was the constant in the workforce – and I knew what a Condition Report was and what to do with it. I could also forward plan, implement and cost track maintenance services.  

The sum of my experiences make me a rare animal in the SAP ERP Space. That’s why I get to mentor the WYWM SAP Squad Training Programme.  

Got questions?

Click here to ask the WYWM SAP Community on Discord

Got Questions?

Click here to ask the WYWM SAP Community on Discord

Let’s look at how we can use moving averages to spot trends in time-series data.

Time series are one of the most common datasets that you will come across in your Data Analytics career. A time series data-set is a collection of sequential data that is recorded in time intervals.

Some of the many examples include:

• The price of a stock over a certain period of time.
• Your heart rate over the course of a run measured by a fitness tracker.
• Records of the daily number of  visitors to a ski slope.

One of the main challenges when working with a time series is that there can be strong fluctuations, or noise, in the data. This can make it difficult to spot trends and key features that would otherwise be hidden under these fluctuations.

In this video we use a simple moving average to explore the trend in a time series of historical business revenue using MS Excel. We then go on to use this moving average as a baseline to measure monthly revenue against.

You can find the data-set on Kaggle (some columns & rows were deleted in this example to simplify things): https://www.kaggle.com/podsyp/time-series-starter-dataset