Over the weekend I had a bit of time on my hand and I wanted to try out some new features in the latest Power BI release.
While looking for an interesting data set I thought about the Austrian election. As most of you know this is the country where I was born before I moved to Australia and came across an interesting web site that aggregates poll results and other election information called “Neuwal.com“. The Neuwal operators are kind enough to make their data available via json format. So, I started my shiny new latest version of the Power BI engine and connected to that data set and put an initial simple analysis together in about an hour:
I tried the fairly new ribbon chart visual to show the aggregate, average poll results, this is an interesting alternative to stacked bar charts for showing trends. The table visual on the right contains the links to the publications that have published the poll results. So, with a click on the survey time line, the relevant links are automatically filtered and the user can navigate to the specific publications. Finally I am showing at the top left a comparison of the last election to the average of all polls for a selected time frame.
To put this analysis took about an hour with a live link to the data source. So, new Neuwal results are automatically updated. If you would like to “play with the application” please use the embedded report below:
To discuss how Managility can help with your analytics projects please contact us here: Contact Managility
Managility just completed a project with a large retail company analyzing and optimizing their shipment processes based on a Power BI solution. The relevant data set included 300.000 annual shipment transactions with all relevant details like pickup/drop off location, weight, volume, provider and shipment cost as well as a pricing data from a variety of logistics companies.
The initial step involved gaining insights into the data and providing the client with a clear picture. The outcome is shown in the dashboard below (for confidentiality reasons all data is anonymised). It enables insights into developments over time, grouping data into weight and volume buckets, rankings of top sender and drop off locations as well as a variety of filter options (e.g. dangerous goods etc.). Helpful insight was gained through a new custom visual called “Flow Map” that visualizes route details through the thickness of the link between two locations.
The next step in the project was focused on realising tangible savings. Shipment rates of all relevant logistics suppliers were added to the model. This fairly complex requirement with a variety of varying rate parameters like availability in the route, differentiations by weight, time of day, urgency, volume etc. was handled effectively with Power Query. DAX calculation logic was then implemented to determine the optimal provider/ rate option to minimise cost.
The results of this process are available in an interactive dashboard that includes all providers and their shipping products. As shown in the screen shot below savings of around $1m through using an optimal mix of logistics products was identified that can then be further filtered using the criteria on the right.
The projects was initiated using Managility’ s Fast Start program that included clarification of project deliverables and priorities, the building of an initial prototype and a project plan for the entire project that in the end took only 10 days from start to finish. Using legacy BI solutions that Managility has been using beforehand, a project like this would have taken weeks.
For information on the Managility Fast Start program and product/supplier mix optimization please contact us here.
PowerApps, is a relatively new service by Microsoft that enables business users to build mobile or web browser based apps in conjunction with their corporate data in an Office like environment with no or a very low number of lines of code. At Managility we have been using Powerapps (apps) and the related Flow (workflows) services mostly to implement customised budgeting/forecasting process steps like for example budget data collection and ongoing tracking of actual purchases to assigned budgets. Recently we wanted to test how Powerapps could be utilised for our internal processes and built a practice management / time sheet system for our consultants. Within just a few days an initial version of the application was ready that runs as a mobile app or from a web browser.
Powerapps Active Projects Screen
How does it work?
Initially our consultants are automatically authenticated using their office365 subscription without a need to login separately. From that point onwards they can select the project and enter tasks and hours against it using an easy, touch enabled interface.
Users with admin rights can access summary reports directly on the app , create new projects or invoke a billing process that generates invoices to be sent via email with a summary of the billable using our xero accounting solution.
Finally, there is also the option to use the data of the app with an interactive Power BI dashboard:
Power BI Report
Contact us how Powerapps mobile applications could streamline your processes: Contact Managility
Power BI is an awesome tool but no tool will be of any use if your underlying data features the 3 “I”s:
For a simple ad-hoc analysis of one source you can get by fixing #1 in Power Query and hoping to identify the “Incorrect” in the analysis. It’s no secret that the initial benefits of many Business Intelligence projects are typically the identification of data issues that become apparent through improved insights with the use of effective data visualisation.
As soon as you want to use multiple sources #3 will become an issue, where “changes in the front end (i.e. Power Query)” will often not fix the issues anymore and you will require a broader BI repository strategy where a key part is the architecture of an integrated data storage approach for analytical purposes often referred to as a “Data Warehouse”.
For us at Managility, the reporting and front end side is typically the easiest part of project that takes 10-20% of the overall implementation time. Typically, the major portion of the rest of the time in our projects is spent on the design and implementation of the back end with loading and cleansing and ensuring consistency of data in between the different sources into a database optimised for analytics.
A process referred to as ETL (Extraction Transformation and Loading) in our tech speak. As much as software vendors will try to position their tool as “really easy” and “self-service” an inherent part of the data integration process are complex issues where data typically “doesn’t match” and mappings andrules will need to be established that no out of the box process will be able to cover co,mpletel and that typically requires specialist knowledge.
At a minimum, we recommend that as soon as you embark on more complex analytics projects with different sources, you make yourself familiar (or find someone to help you…) with multi-dimensional modelling and how to structure data into star schemas (in a nutshell the separation of facts, records of what is happening and dimensions: details and hierarchies by which you want to analyse your data). It is a more or less mandatory concept in Power BI to have data structured that way if you want to analyse across different sources. At minimum, you will require a dimension table with distinct elements by which the different sources and their “Fact Tables” can be joined. The simplest and likely most widely used example there is a common date table which we will cover in more detail in the next tips&tricks post here.
Inspired by the Delta Master BI -a tool that we have worked with for many years and that combines advanced analytics and a great UI- I was looking to realise one of its cool features in Power BI. Delta Master is a brilliant tool but unfortunately has an “enterprise price tag. As we will see we can get pretty close to using one analysis method using Power BI.
The Delta Master feature is called “Power Search” and does a ranking across all dimensions and levels in a multi-dimensional cube (e.g. Analysis Services).
The user can specify a measure e.g. “Revenue” and the method returns a ranking across all dimensions like for example shown here:
This is a great starting point to see what is “driving ” your revenue. In this case the first 2 results are trivial as we are only operating in this market and with one company but from rank 2 it gets more interesting and we see that a customer group has the biggest share of “Revenue” with 80% followed by a product group classification with 78% etc.
A ranking like this is also particularly interesting if it’s on a measure like Actual-Budget variance as it will avoid that I have to navigate through multiple dimensions to find where is the variance coming from. Using the Power Search approach I get the key factors immediately.
Taking this approach further Power Search also allows you to include combinations in the ranking when it gets even more interesting like:
To realise something similar in Power BI you just add the Table visual to your dashboard and drag the attributes that you want to use in the ranking into there:
And voila we see an overview of what is driving Revenues across all relevant attributes. To Calculate the share of the total just use this simple DAX calculation:
A requirement that is very often communicated to us in client projects these days is “Data mash up”. The ability to integrate internal and external data sources more effectively and enabling more holistic insights.
One of the key challenges in the setup of analytical application processes is the side by side use of relational databases often referred to as the “data warehouse” and multidimensional data stores.
The data warehouse architecture is typically “data driven“ or in other words it typically involves integrating existing data from different sources. This architecture is suited for flexible analysis and often requires specific technical expertise by the analyst.
The other approach is “concept driven” i.e. enabling the analyst to enter data, conduct what if analyses and to modify structures (business modelling). This mostly involves multi-dimensional (also referred to as OLAP) databases that are optimised for use by non IT specialist business users. Often an “in memory” approach is used in this architecture that enables extremely fast response times when calculating query results across 3-20 dimensions with multiple aggregation hierarchies.
None of the above concepts is “better than the other” the use case typically depends on the requirements at hand and in most cases Managility recommends a combined approach that involves both architectures.
In recent times a third aspect to consider is the integration of commercial web based data services for example provided in the Azure Data Market or by other third party providers. This enables organisations to put their data in “perspective” for example by comparing it to temperature, population or financial markets details.
Historically the “keeping in sync” of these different “data worlds” required extensive development work of ETL (Extraction, Transformation and Loading) processes.
To address the integration challenges Managility has recently developed a new approach that synchronises OLAP and relational sources: The PowerSync framework a solution that we provide to our clients free of charge. PowerSync automatically converts Jedox cubes into a relational dimensional model with fact and dimension tables.
The benefits are much improved audit options (every OLAP transaction is a relational record than can be easily traced) and a comprehensive view on the data. The combined sources can now be analysed with existing data warehouse reporting solutions or any standard BI tool that supports relational access.
In addition Managility Power Sync supports new technologies like PowerPivot, a component that enables analysis of large data volume sources in Excel and the easy integration of external sources. As per the example below the Jedox Sales demo can be easily extended with population information to enable a relative metric measuring sales per population.
At the moment we have used the approach at a few early adoption clients with very good feedback. Please don’t hesitate to contact the Managility team or me for any further questions or to setup a demo with your data.
Over the years I have lost count how many times I have heard the mantra “Excel is just a toy you can’t use for serious Business Intelligence”. Typically from “IT experts” that never had to work with a BI solution from an end user perspective.
In part this statement is true: Excel on its own is and was never designed to be a store for large data volumes required for BI purposes. An area for which it is still often misused in poorly designed spreadsheet convolutes.With the right analytical technology by its side though, this tool is still one of the most flexible BI front ends on the planet. Not to mention that there is a pretty knowledgeable base of an estimated 300m users around, that don’t have to be trained in yet another standalone BI client.
From the experience of 15 years in this industry and more than a hundred BI projects I can pretty confidently say, that for most business intelligence use cases, users will likely interact with a spreadsheet at some stage of the process. The notion that Excel has an important role to play and should be considered as part of a professional business intelligence solution is by now –despite initially different opinions- also shared by Gartner and other analysts (More here and here)
Separation of data storage and presentation
The crucial factor is to clearly define the boundaries of how Excel is used. Anyone working in this area will be familiar with the typical “Excel Hell” scenario: a convolute of unmanageable workbooks that are error prone and don’t really offer useful insight.The key to avoid these issues in a business intelligence context is in our opinion the separation of data storage and presentation layer: data should not be pasted directly from a data source (or even worse rekeyed) into a spreadsheet but made interactively accessible from consistent, “single version of the truth” data mart(s) that also include as much as possible key parts of the logic (especially calculations).
Complementary Excel Business Intelligence add-ins like Jedox, TM1 and most recently Power BI (xVelocity engine) exactly address this problem by providing central, server based data storage technologies optimised for analytic purposes and governed by professional multi user management capabilities. For example enabling that on the same report (or Excel spreadsheet) specific users can only view or change data cells (e.g. by account, department, etc.) based on their credentials (an area where Excel on its own falls crucially short). At Managility we have worked with all of these and believe they are great enablers of tremendously useful business intelligence solutions that typically cater for most analytical application requirements that involve “power users” in their familiar spreadsheet environment. Each one of these solutions have particular strengths and weaknesses and need to be evaluated based on the specific customer requirements. Of course there will always be use cases where a more controlled approach for non-Excel users is required but these days the above mentioned solutions offer extensions that enable publishing data as protected, interactive web applications or on mobile devices.
Exce(l)ptional capabilities added since 2010
The capabilities of Excel as a “serious BI frontend” have dramatically increased in recent times. Particularly since version 2010 new “game changing” elements -from a business intelligence point of view- have been added. Most important to mention here is “PowerPivot” and subsequently Power BI. A free Excel add-in -respectively with Power BI a separate desktop tool- developed by Microsoft that works in conjunction with Excel version from 2010. PowerPivot/ Power BI provide the modelling environment to generate and use data models that are deployed using the Microsoft xVelocity in-memory technology. The key benefits of the technology are its ability to process staggering amounts of data (there are many reports from people analysing 100m records on their laptops) with lighting fast response times. In addition to better processing the other key focus is on data integration. The enabler for this is Microsoft’s new BISM (Business Intelligence Semantic Model) a framework to better integrate different sources and data store technologies beyond the boundaries of internally produced data. For example by adding weather information from an external provider to analyse effects on internal sales data. With Excel version 2013 Microsoft has also added additional business intelligence features like drill down and drill across in PivotTables (a feature that also works with any complementary OLAP technology that supports ODBO) as well as amazing geographic analysis with Power Map (formerly GeoFlow) Excel Add-in. In this video we take a initial look in conjunction with randomised parking meter data from the City of Sydney. The vision, supported by great demos, is that this process is driven entirely by business end users. Here we are a bit less confident. Despite significant advances, we believe that this can work in limited areas but caution is required. Data integration particular across different sources will always be a complex and challenging subject that will likely require specific technical domain expertise.
The Best of both worlds
Microsoft’s business intelligence back end engines (like Power BI, Power Pivot and Analysis Services) are focused on read only analysis. In our client projects we have developed a variety of approaches that address this issue particularly with utilising memory based processes to handle extensive write back processes (e.g. data entry and simulation on aggregated levels e.g. all products and distribution according to typical in budgeting scenarios. Please don’t hesitate to contact us to arrange a hands on experience of the new options in one of our Fast Start Workshops or the new Advanced Business Intelligence with Excel workshop. All in all, the improvements in Excel are definitely a huge leap forward and will pose a substantial threat to currently very popular, read only analysis tools like Tableau and Qlikview whose price tag will be closely analysed by organisations who have already invested in Microsoft licenses.
In this post, I would like to present you the “Option Pricing n Greeks” in Jedox.
Well continuing on my previous post, I would like to highlight the potential of Jedox in terms of number crunching capacity, where I have designed the macro for option pricing most commonly used in any front desk team of an investment bank.
For all the background about options derivatives used in the financial industry and the measures (the Greeks) incorporated in this model references are as below:
“Screen Shot’s” associated with the windows keyboard “Print Screen” function key, have been and still being utilized heavily to report error’s occurring on the client side with the application. And clients not sending/capturing the screen state at the time of errors lead to difficulty reproducing the issue within the development arena in order to resolve the same.
Thus to simplify a similar situation, I thought it would be great if the screen shot could be captured at the time of error occurring (error handling “catch” block) and sent across to server and saved as an image without client going through the pain of capturing, saving, attaching and send via portal tickets/email.
With this interesting technique I would only like to highlight the dangers of the same, as it can be wrongly used to target the client side confidential information, so please beware !!! (Mischievous intent people shoo!!! away)
// Get the data
// Remove the headers (data:,) part.
// A real application should use them according to needs such as to check image type
$filteredData=substr($imageData, strpos($imageData, ",")+1);
// Need to decode before saving since the data we received is already base64 encoded
// Save file. This example uses a hard coded filename for testing,
// but a real application can specify filename in POST variable
$fp = fopen( 'test.png', 'wb' );
fwrite( $fp, $unencodedData);
fclose( $fp );