Modernising the Traditional BI Environment

Summary

The Department of Housing together with Ignia developed an award winning dash boarding solution by using the platform approach to Business Intelligence and delivering it using the same technologies and tools familiar to staff (such as SharePoint and Excel) which drove greater user adoption. The solution was delivered in just three months with a number of immediate successes. Realising the significant benefits of empowering its staff with self service capabilities, the Department embarked on a large scale enterprise business intelligence project, which was accomplished much faster than traditional approaches by using a Scalable Agile approach, where value is constantly added through quick wins and without sacrificing quality or extensibility.

The Department seeks to build better communities and enable all Western Australians to have a place to call home by providing public housing for those in need, when in need along with affordable land and housing opportunities for those on low-moderate incomes; assisting with housing finance and providing rental assistance. The Department also provides government employees in regional areas with quality homes, enabling them to deliver necessary services to these communities. With the largest geographic jurisdiction globally, the Department is also the second largest land conveyancer and property developer in Western Australia. Over the last three years the Department’s business intelligence capability requirement has grown significantly in complexity, breadth of information and exposure to the organisation. The increased requirement led to the establishment of a business intelligence portal built on the SharePoint platform. The portal was named “Know-It-All”.

The evolvement of Know-It-All

This project started as a redevelopment of the existing reporting portal, with the aim to integrate the functions of managing reports, metadata, and knowledge into the same platform as the Department’s collaboration portal. The Department, together with Ignia pioneered an innovative approach to Business Intelligence (BI), delivering results quickly and making it possible for business users to stop relying on IT and easily access and analyse relevant reports. Majid Bassiri is the Business Systems Manager at the Department of Housing and it was through his vision of empowering the Department’s staff with BI capabilities that this project began.

“Back in 2001, Know- it -All was a reporting portal without any metadata. Although we had tried to put in the functionality to solicit business knowledge in the portal, updating the metadata was somewhat cumbersome and with no data stewardship or ownership the result was much less than desirable. People would leave and no one would know where anything was…now, it has a responsive design for all business areas with easy to access knowledge management systems, analytic tools, reports, dashboards, and all things related to Business Intelligence, and the transfer of knowledge is so simple!” says Majid.

Pushing the boundaries with Agile BI

In 2013, the Department won the Leading Innovation Award at the 2013 Professional Excellence Awards conducted by The Australasian Housing Institute for the Maintenance dashboard solution, which provides trend analysis for forecasted and actual budgets of the asset maintenance function along with an analytical view of the active and overdue job orders.

This project was delivered in just three months with a number of instant successes, showcasing the power of successful BI implementations to internal stakeholders. The biggest achievement for the solution in its first year after implementation was that the maintenance budget was back in the black!

The Department quickly realised the potential of this approach to BI projects and put in place a plan for future enhancements, to develop Know-It-All into a responsive portal, providing a search friendly and intuitive navigation for the department’s BI function.

Key functionality for the system include browsable business area contextual views, data governance and master data management features and a sophisticated visual data dictionary and metadata repository. These key functions are supported and made possible through a mature enterprise data warehouse, developed and maintained to make extensive use of the SQL Server suite. The warehouse and its associated staging areas are hosted on the SQL Server database engine, with the requisite ETL processes powered by SQL Server Integration Services. A number of cubes built using SQL Server Analysis Services form the important and highly performing analytical data structures consumed by the dashboards, scorecards and reports.

Future proofing

The Department has a complex business hierarchy including over a hundred business units, sections and directorates that are aligned to four strategic pillars. This hierarchy has been known to change significantly as new government forms, key executives retire or the vision from annual strategic planning sessions is executed. Know-it-All needed to take this into account whilst still enabling logical and intuitive navigation. Workshops conducted using exercises from the User Experience discipline produced a manageable set of core business functions which were both familiar to the business and enabled alignment of business intelligence artefacts. The reports were designed and implemented using a combination of Excel Services, PowerPivot and SQL Server Reporting Services (SSRS) delivered on the SharePoint platform. The scorecards and dashboards were designed and published primarily using PerformancePoint Services. PerformancePoint Services enabled technology mash-up scenarios to rapidly produce dynamic dashboards where most of the development time was spent speaking to the business, not struggling with the technology. Ignia were able to leverage SharePoint and the associated business intelligence technology set to create a streamlined process that enabled departmental power users to develop their own dashboards and reports.

Empowering users with self-service

Ignia lead the design and implementation of a ‘visual data dictionary’; a dynamic metadata repository supported by a sophisticated and automated metadata crawler service. The repository allows searching and browsing of metadata for all the Department’s major core application data, the enterprise data warehouse and other analytical data structures including cubes and data marts. During its development, significant priority was placed on the usability and visualisation aspects.

The repository was intended for use by business intelligence developers, power users and business analysts. The challenge was to ensure the realisation of each of these discrete audiences’ goals. The visual metadata repository has proven to be a great tool for business analysts fielding cross-functional queries about subject matter that may not be instantly at hand. The repository allows rapid discovery of relevant of information that is often cumbersome and time consuming to find using the core business systems which are often difficult to navigate.

“Having the visual data dictionary means that the business users no longer need to rely on IT to get the information about the data they need the benefits that this empowering function has delivered are very impressive.” says Majid.

Managing the world’s largest geographic jurisdiction made easier with

Know-It-All

With only a few of the planned BI components implemented at the time of writing, the Department is already seeing a huge impact on the organisation, including:

$1.25 million dollar annual cost reduction

The Strategic Asset Management (SAM) system is a decision support system enabling rapid discovery and analysis of opportunities aligned to the strategic direction of the Asset Planning and Management directorate of the Department of Housing. SAM has realised a cost reduction of at least $1.25 million dollars annually to the 25,000 sites required to be assessed. Each site would have taken at least one hour to assess by trained staff. SAM requires no staff interaction to assess and rank these sites.

Predictive analytics for increased profit

Know-It-All uses big data predictive analytics to provide unprecedented decision support for a number of critical tasks and generating a profit for the department.

“Our staff can accurately assess the market and property data such as dwelling age and maintenance costs per suburb to make decisions around which property to buy or sell- this is something that previously would have taken much longer with less volume of precise data generated.” says Majid

Increase in relevant data to help increase clients’ safety

The Disruptive Behaviour dashboards provide monitoring and analysis of disruptive behaviour incidents across the state. These dashboards include contextual views to help increase the safety of tenants. The Disruptive Behaviour priority management solution utilises Big Data predictive analytics through Hadoop to prioritise work load based on the likelihood that the complaint will result in a strike under the legislation.

Business process productivity gains

Dashboards such as those provisioned for the Maintenance function, Communication and Marketing and the Disruptive Behaviour Unit have provided significant productivity gains.

According to Majid, “We have noticed significant productivity gains across the organisation; for example, Communications and Marketing can now easily identify which messages are reaching their intended audiences without expensive and time consuming surveys.”

The maintenance department can understand at a glance where there are differences between forecasted and actual budgets for execution of job orders. Likewise, the Disruptive Behaviour Unit are able to more effectively manage their workforce and asset management assignment.

Cost reduction to provision dashboards, scorecards and reports with improved accuracy

The cost to provision dashboards, scorecards and reports has been greatly reduced through the establishment of a streamlined development process that is supported by managed data sources. The process utilised standard SharePoint features included workflow components, sites, lists and custom content types. Prior to the establishment of this platform a typical dashboard may take up to three months to move from idea to production. A number of dashboards of similar complexity have been developed within two weeks under the new process.

The Data Notification Framework also allows for the next day resolution of transformation errors that previously went un-resolved until a report was identified as being inaccurate.

Future plans

An expansive data warehouse is now providing access and views of data previously unavailable to every employee. This includes access to data from legacy systems, freeing up resources and enabling data continuity across system renewals and replacements. The expansion of the data warehouse to include all the Department’s data assets in a timely and efficient manner can now continue under the business as usual mode. A new programme of work will include Data Governance, Master Data Management, and business area specific data marts.