Planning and data entry with Power BI

Planning and data entry with Power BI

This month Microsoft released an integration with PowerApps in Power BI. PowerApps is a great solution to build simple mobile or browser-based applications quickly. It also supports write back of data. We have been asked by some of our customers how this relates to what we do, so here is a quick comparison:

Our solution Agility Planning is an integrated ecosystem for enterprise grade business modelling and planning that seamlessly integrates with the market leading data discovery solution Power BI and Excel/Excel Online. The heart of our system is an OLAP engine which runs on a relational database (SQL Server on premise or on Azure) that enables in-memory processing of the relational tables. A typical standard relational, disk based approach used by other tools on the market can’t cater for processing large scale multi user write back.

The Agility Planning engine automatically generates optimal data warehouse structures either based on our connectors (cloud-based accounting systems like Xero, QuickBooks online, Saasu etc.) or completely from scratch when users build their own dimensions and cubes as required (e.g. by copy and paste from Excel) or by importing data via ETL (SQL SSIS).

PowerApps is a great solution to collect a few specific numbers/KPI’s on the base level and add as records to a database. If that’s all you require perfect. We often use it alongside Agility Planning for simple data collection, but typically the main requirements in planning and forecasting situations are normally very different:

In the planning process, users are dealing with complex data models that include among others: financials, sales, HR details , etc. as well as sophisticated calculation logic. Manipulation of these requires effective (including the support of special financial report structures) tabular entry and the option of entering data on different hierarchy levels. The users need to immediately see the effects of entries and changes in a tabular structure. All this is impossible in PowerApps.

In the following table I have put together an overview of how the different options to enter data in Power BI differ:

Feature

Agility Planning

PowerApps

Other Write Back Tools

Getting Started

Initiation

Cloud based self-service sign on/ billing Download of an App or web service Manual purchase from vendors (no self-service
checkout)

Back end

Automatically generating optimal data
warehouse structures

ü ×

×

Processing write back/model changes in
memory

ü

×

×

Connectors to cloud-based accounting
systems

ü ×

×

Audit trail that tracks all changes
(who, what, when) for exact monitoring & control as well as simple undo.

ü

×

×

AAD B2B support, single sign on

ü ü ~

Front end

Modelling

  • Web browser-based modeller.
  • No install
    required.
  • Complete flexibility with dimensions: As many dimensions and hierarchy levels as required.
  • Copy & Paste from Excel.
    (Modelling in Agility Planning)

Limited, respectively require add-in install.

Write Back

Data Entry on Pivot Tables and Power BI visual

ü ×

ü

“Splashing”/data entry on any level of the dimensional hierarchy

ü ×

ü

Copy Wizard for data manipulation

ü

×

~

Write back on Excel report formulas in addition to Pivot Table

ü ×

×

Comments

ü

×

ü

For further details on how the solutions compare and how Agility Planning can assist your business modelling and planning processes please contact us.

Effective Team Collaboration: The Key For Planning Success

Effective Team Collaboration: The Key For Planning Success

Planning in larger organisations is almost always a team effort. In this blog I am covering how new Microsoft Office standard technologies can be used by teams to work more effectively together and achieve significant process improvements.

Planning processes typically run as feedback loops that involve the following during the initial plan development stage:

Planning Phase

Subsequently in the monitoring phase the process continues in a similar fashion and includes:

Monitoring Phase

In every step of these processes, extensive team collaboration is required and typically involves:

  • Online communication and conferences
  • Management of documents
  • Task management and
  • Knowledge management.

The Shortcomings of Legacy Planning Systems

Legacy planning systems typically cover some of the core planning functionalities to aggregate information but often don’t offer much in relation to collaboration. Some solutions offer rudimentary collaboration with their own approach to commenting and file uploads, to name a few. But this is typically not as productive as it could be as they don’t integrate with the overall collaboration standards of messaging and file management systems that are utilized within the organisation.

Things get worse in the analysis phase. Here, the problem starts with the challenge that legacy planning systems lack state-of-the-art data discovery features to analyse the root cause of problems. In the best-case scenario, a separate data discovery solution needs to be implemented that links planning and actual data. In the worst-case scenario, planning data needs to be moved to another platform to make it available in the data discovery solution.

Equally problematic are lack of task management. For example, ensuring that the right actions are taken to “fix problems” uncovered in the monitoring phase. This involves assigning tasks to the right person and being able to follow up on the progress efficiently. Again, this is something that’s better part of an organisational standard than an island solution in the planning system (if it exists there at all…).

Integrating With The Standard

At Managility, our focus is on offering best of breed features to support the organization’s FP&A/Performance Management processes, while integrating seamlessly with the existing organizational standards for all the aspects mentioned. We know this not just from our experience with implementing client projects, but from our experience as a globally operating, knowledge based service provider. After trying a variety of tools we decided to implement Microsoft Teams as we found it to be the best solutions that covers all our collaboration, file sharing and knowledge management needs while still integrating -without any additional efforts- with our authentication (Azure Active Directory) system .


Microsoft Teams

Microsoft Teams is an integral part of Office365 and provides integrated team collaboration features that include: chat, video, online meetings, file sharing, professional task management plus the option to add additional Microsoft as well as other third-party applications to the team environment. One of them is Power BI. This means that the participants work in one environment where all core process requirements for the planning and ongoing monitoring process come together. With Agility Planning that includes being able to:

1. Chat, voice and video conference at a click of a button at any stage of the planning process
2. Make planning and data collection forms directly from the Teams environment
3. Create and assign tasks to respond to insights in the monitoring stage
4. Enable effective file sharing and knowledge management

To see how this works in a real-life situation have a look at this video:

If you like to find out more about Team Collaboration & Planning solutions in your organization please don’t hesitate to touch base with us.

Thank You!

Thank You!

 

On behalf of the entire Managility Team we would like to thank all our Clients and Partners for their tremendous support this year. We wish you and your families a relaxing break and a fantastic start into 2018!
Martin (CEO), Hesam (CTO) and Darren (VP US Operations)
a57689a7-dd80-4faf-8bdd-6a6ef24f8dac.jpg

Next Level Financial
Planning & Analytics

Following significant investments over the
last two years Agility Planning was launched
at the Microsoft Insights Summit in Seattle in
June 2017. A ground breaking new FP&A
offering that includes an in memory based
OLAP engine for Power BI, Excel and Excel
online

More

Effective BI is not just a matter of technology.

Usign our unique software enabled services
model, you have the option to profit form
professional advisory/outsourced CFO
services.

More

37f5f658-e65e-40e1-a978-1ac2a61c1a8c.jpg

 

Agility Planning Matrix Light Visual

Agility Planning Matrix Light Visual

The Agility Planning Matrix Light is a simplified version of the Agility Planning Pivot that includes comprehensive Agility Planning features particularly for versatile planning and budgeting. AP Matrix Light provides only some of the reporting features that are currently missing in the Power BI standard matrix like selective drill down and display of filter elements.

  

Downloads:

Please follow Agility Planning on Twitter and register here: Registration to access download links:

 

(visual and a sample report)

Please keep in mind this is an unsupported preview.This visual is provided as is and without any warranties. To submit bugs or feature requests please visit the Managility Support Site.

Watch introduction video:

Known Issues:

  1. The Agility Planning Matrix Light visual does not support cross updates to other visuals on cell click. Filtering through standard Power BI filters is supported.
  2. There are no horizontal scroll bars. To scroll left or right click on header and user arrow keys.
Excel A Business Intelligence Toy?

Excel A Business Intelligence Toy?

Over the years I have lost count how many times I have heard the mantra “Excel is just a toy you can’t use it for serious Business Intelligence”. Often from “IT experts” that never had to work with a BI solution from a business user perspective.

 

In part this statement is true:  particularly when Excel is (mis)used as data store for storing copies of similar data in 85 different versions where no one know what the origin is and if someone has changed (inadvertently…) the logic (formulas) or the actual data. We are all familiar with the resulting spreadsheet hell and related horror stories a plenty: https://www.cio.com/article/2438188/enterprise-software/eight-of-the-worst-spreadsheet-blunders.html

 

But is this really Excel’s fault? Not really… It’s just the wrong approach of storing data in the spreadsheet  (In rare cases this can make sense for example with Power Pivot as I will point out later) as opposed to using it as a (very flexible and effective!) presentation layer that can cater for most analytical application requirements that involve “power users” in their familiar spreadsheet environment.

Separation of data storage & presentation

The crucial factor is to clearly define the boundaries of how Excel is used. 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).

Exce(l)ptional New Capabilities

Excel offers great integration with a lot of data sources  out of the box. One of them -although unfortunately by now not available in all editions- is Power Pivot (Vertipaq/ xVelocity engine) that enables users to connect to a data source and load millions of records into a model linked to an Excel workbook. This allows the users to build reports as needed based on a consistent single Power Pivot data model that can be automatically updated when the source data changes.  In addition to data consistency another crucial aspect is security: as long as security rights are properly defined in the underlying source and the user has to authenticate, she will only ever see what she is allowed to see despite multiple users using the same report definition.

Some examples below (and yes this is all “just” Excel…)

 

The Next Level: A Two Way Client Server Solution

Power Pivot is a great solution but it doesn’t cater for every use case particular when there are requirements to:

  • use the data model with different clients (e.g. web applications, mobile apps etc.) and multiple users need to work on the same model for different applications.
  • write back and collect data into the central model.

In these cases a real client server based solution is needed. This can be for example Analysis Services or other complementary solutions (Managility offers two with Clear Jelly and Agility Planning that are add-on components to MS SQL Server for handling business driven business modelling/writeback) that offer a server based two way architecture for reading and writing. In conjunction with Excel Online this approach also enables rolling out professional BI applications to users that only need a web browser.

At Managility we have used this architecture approach very successfully for more than 10 years in large scale enterprise BI projects with hundreds of users and Gigabytes of data volumes. The advantages are plentiful:

  • consistency and security
  • the flexibility that Excel provides in regards to report definition
  • extensive visualisations options ans ad hoc analysis is unparalleled

and finally of course:

  • Excel knowledge is widely available.

 

Admittedly Excel is not the answer in every case (for mobile deployment  or interactive dashboards Power BI is a brilliant complementary option) but if properly used in conjunction with a true central data model (these days often cloud based) it can be a key front end option for tremendously powerful busines intelligence solutions that typically cater for most analytical application requirements that involve “power users” in their familiar spreadsheet environment.

 

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/Power BI 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.

Power BI & The Austrian Election

Power BI & The Austrian Election

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 Logistics Optimization For Power BI

Managility Logistics Optimization For Power BI

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 based Practice Management App

Powerapps based Practice Management App

What Is It?

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.

 

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:

 

Contact us how Powerapps mobile applications could streamline your processes:  Contact Managility

Power BI Top 10 Learnings Tip #1 GIGO: Garbage In Garbage Out

Power BI Top 10 Learnings Tip #1 GIGO: Garbage In Garbage Out

Power BI is an awesome tool but no tool will be of any use if your underlying data features the 3 “I”s:

1. incomplete,

2. incorrect,

3. inconsistent

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{c2d0c4a7f309d96398b72061ae9fc277f0945ca82b5bc85179baf1ca4bfd31a4} 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 simple Star Schema used for ClearJelly.net

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.

Easy Sentiment Analysis for Twitter

Easy Sentiment Analysis for Twitter

In this post we are covering how the new Microsoft Flow can be used to insert data from Twitter with sentiment analysis into a Power BI streaming dataset.

Create streaming dataset in PowerBI:

Go to PowerBI.com -> Then “Streaming dataset” –> Create streaming datas

Now we will create a dataset type of API.

Name the dataset –> Then add the following values:

Time ——DateTime.

Tweet——Text

Sentiment—–Number

Sentiment values will be numbers in 0,1 (with 0 being negative and 1 positive).

Create flow to push data from twitter to PowerBI:

Go to –>flow.microsoft.com–> My Flow–>Create from blank.

Enter your flow name and title and on the screen below Click on twitter category

Then select new step–> Add an action –>search by “Sentiment” word and select “Text Analysis – Detect Sentiment”

Example: select tweet text to be your text that will be analysed with text analytics – Detect Sentiment

In the last step we are going to push the data into a PowerBI Streaming dataset.

Click on new step –>Add an action–> select PowerBI–> Then click on PowerBI – Add rows to a dataset.

Then select your workspace, dataset, and table

Then select the following:

time: created at

Tweet: Tweet Text

Sentiment: Score

Then select create the flow.

Now the data is inserting directly to your streaming dataset.

Let’s see how to use that in a dashboard:

Go to PowerBI, dashboard then click on add tile.

Click on tile –> Then select “Custom Streaming Data” –> select your streaming dataset–>select your visual type ie: Line Chart –> Then add time as Axis, Tweet as Legend, and Sentiment as Values

Or you can use build direct report from your streaming data set.

Go to streaming data set–> click on create report.