Project: Standardize and Accelerate Banking Business Intelligence Platform
Business Challenge: With tens of billions in deposits, over 300 branches and one thousand five hundred BI application users, this client had built a two-terabyte MS SQL Server data repository for reports, scorecards and analytics. Created in a normalized schema with over one hundred tables, the complexity was significant and growing. Given the large volume of account-specific data, numerous aggregation tables were built in order to speed the ongoing delivery of a long list of reports, built in MS Reporting Services, SharePoint and Excel. However, a number of issues were posing increasing challenges. Ongoing data movement tasks were labor-intensive and vulnerable to errors. Critical reports were still taking too many hours to run. Reportable business metrics, which often had to be re-coded separately either in aggregation tables or within the reports themselves, were increasingly difficult to hold consistent with those previously built by other team members. In light of the client’s desire to integrate yet more data from some forty new sources, they knew that they would need to radically reduce report run times as well as standardize and centralize their business metrics. Towards those ends, they were exploring the potential benefits of introducing star schema, multi-dimensional OLAP cubes, and an enterprise-grade business intelligence dashboard.
Solution Details: Interviewed executives, analysts and engineers on current conditions and requirements. Authored and presented business intelligence enhancement roadmap document recommending strategic, technical, staffing, hardware and software platform choices for enhancement to client’s business intelligence capability, including the introduction of standardized, conforming dimensions, star schema, an OLAP platform and a short-list of leading dashboards. Authored separate document to guide implementation of business metrics in the context of a balanced scorecard, and formalized key performance indicators with standardized performance targets. Contract extended --built an Analysis Services cube aggregating fifteen million fact rows and merging/hierarchizing numerous dimensions for query performance enhancement. Wrote numerous MDX (multi-dimensional expression) calculations for performance statistics. Built performance dashboard prototype for cube, with key performance indicators, scorecards, dynamic charts and drill-down reports, using MS PerformancePoint Server. Then built Sales Performance cube and PerformancePoint dashboard prototype.
Business Outcome: Roadmap implementation has set clear expectations for overall BI requirements. Meanwhile, completed cubes and dashboard prototypes offer unprecedented query performance for multi-dimensional visualizations against a large, complex banking data set.
__________________________________________________________________________________________________ Project: Real Property Sales and Loan Multi-Dimensional Analytics Capability
Business Challenge: The clients multi-terabyte database for residential property, mortgage, title and homeowner demographics data, and their data extraction, transformation and loading (ETL) processes, robustly supported many core line-of-business applications, providing data feeds, lists and reports on properties, comparables and homeowner demographics to institutional lenders, investors, title companies and direct marketers. One of the client’s key differentiators was high data quality. However, their premier customers, the nation’s leading lenders, were only purchasing raw data feeds at bargain rates and having to build their own reporting and analytic applications themselves. The client had not yet embarked on a significant business intelligence initiative, but its premier customers were willing to pay a premium for subscription-based access to a comprehensive, web-based real property business intelligence solution hosted by this firm.
Solution Details: Wrote multi-dimensional (OLAP) sections of Business Intelligence project’s System Requirements Specification (SRS) and System Architecture Document (SAD). Designed dimensional data model and built Analysis Services OLAP database for real estate sales and loan analytics application, affording customers visual, multi-dimensional insight into trillions of dollars of real estate sale and loan transactions nationwide, according to geography, lender, title company, loan characteristics, transaction types, and with extensive built-in time-series analytic capability. Scripted multi-dimensional expressions (MDX) for Key Performance Indicators (KPIs). Both as dimensional modeler and as OLAP developer, collaborating extensively with, and guided ETL developer while mentoring senior SQL developer new to OLAP. Delivered ahead of schedule. Complex underlying data, yet fast and intuitive for users, where complex queries with on-the-fly calculations resolve a hundred forty million records across a dozen dimensions in just three seconds, affording real-time visual insight into property metrics and details – ranging from national aggregates to zip-code specific geographies -- all at the speed of thought.
Business Outcome: Organization now has their first premium property analytics solution offering for premium institutional customers and upon which to grow their new business intelligence competency. Customer response to the subscription service, even in light of recent industry contraction, has been very positive with increasing interest in application usage in support of mergers and acquisitions.
__________________________________________________________________________________________________
Project: Multi-Hospital Medicare Reimbursement Analytics and Reporting Capability
Business Challenge: The client had already built and deployed separate copies of three specialized healthcare revenue auditing applications to each of our many customers – hospitals and hospital networks – and was supporting hospital auditors in application use. The preliminary assessment service, although a necessary start to a valuable consulting relationship, was labor-intensive and unprofitable. Either the client would send their assessment auditors onsite for multiple weeks, or the hospital would copy and mail volumes of patient records to them. They sought an automated method to quickly assess a hospitals, or hospital networks, reimbursement performance, and they became aware of the HIPAA-adopted ANSI 835 ERA (electronic reimbursement advisory) communication standard, with which Medicare responded to claims from all hospitals with data describing, in impressive detail, exactly how much of each claim is reimbursed, how much is not, and with what precise justifications.
Solution Details: Evangelized, planned, wrote business specification, designed and developed a multi-hospital data warehouse / OLAP solution to analyze Medicare claim reimbursement reporting. Outsourced, to a third party claims processing clearinghouse, initial specialized data transformation stage from ANSI 835 standard EDI format into a SQL staging database. Designed a relational data warehouse to full reflect the range of inpatient and outpatient reimbursement feedback. Specified ETL requirements. Starting with beta of SQL Server 2005 Analysis Services, built a sophisticated OLAP application for a multi-dimensional cube with complex numeric facts and sixteen shared dimensions reflecting completely different inpatient and outpatient reimbursement methods. Build user interface from Excel PivotTables, PivotCharts and SQL Server Reporting Services with an array of user-selectable filters.
Business Outcome: Solution affords healthcare revenue auditors with quick monitoring and drill-down – from macro performance comparisons between facilities in a multi-hospital system – at the ten billion dollar level – down into detailed ad-hoc analyses of individual claim line-item charges, diagnoses, procedures, denials, adjustments and payment details. Now, in order to perform Medicare revenue assessments, hospitals simply sent their raw, electronic 835 files to the client. The client then processes them on this analytic platform, and the auditing professionals would then target the specific hospital procedures for which either administrative or clinical process improvements would result in the largest reimbursement dollar increases with minimum effort. Project: Rescue Two Revenue Audit Applications from Acute Quality Crises, and Establish Foundation of Internal I.T. Competencies
Business Challenge: When starting as the sole initial I.T. employee for this software-creating training organization, two of this organization’s customer-deployed claims revenue audit applications, built on MS Access by a series of vendors, suffered from basic functional stability problems, data import bugs, incorrect business rules and countless miscalculations in approximately fifteen combined reports. Both applications were already widely deployed to multiple hospitals and were relied upon by revenue auditors for their daily productivity. Hospitals were rapidly signing as new customers. Meanwhile, demands for routine application troubleshooting, as well as new functional enhancements, were high and accelerating. The applications had emerged as the single largest cause of customer dissatisfaction. Additionally, ownership and customer usage rights over the applications were not established either in the overall service contract or in the processes of customer installation or usage. Lastly, training organization’s outsourced server and PC systems support was capable, yet expensive and often busy with other customers.
Solution Details: Supported over one hundred end users, providing usage tips, routine troubleshooting, workarounds and recurring data imports while documenting bugs, authoring use cases and functional specifications, mocking-up desired user-interfaces, documenting correct business rules and defining correct report calculations. Performed all software rollouts for new customers. Phased out entrenched database application engineering vendor and negotiated contract with a more collaborative provider. Refined database schema to correctly represent authentic business rules. Implemented standardized processes for customer support response, enhancement request documentation, quality assurance and version control. Published data standards for imports, and published user guide to deployable software enhancements. For interim re-deployment, temporarily reduced available reports to a few corrected reports pending additional quality assurance, redeployed both applications to all customers and established a semi-annual schedule for upgrades to existing customers. Upon promotion to Director, hired internal database application support technician for routine support and deployments while implementing comprehensive report calculation testing regime. Hired internal systems support technician and negotiated transition of existing systems vendor to “Tier 2” only support, thereby reducing PC help-desk expense by 30% while reducing monthly expense by 30%. Launched a series of discreet work packages for coordinating outsourced development, internal quality assurance, and version control for comprehensive application enhancements to reports and new functional capabilities.
Business Outcome: Satisfied internal and external technology customers. All hospitals received no-cost upgrades to standardized versions of both applications, which now offered robust stability, many functional enhancements, reliable data imports, correct business rules, thirty accurate reports, new Pivot Tables and Pivot Charts for ad-hoc analyses and easy-to-follow documentation for user training and reference. Both the service contract and the application installation process also now specified software ownership by our firm and required agreement by customers during installation, as to the legal terms of their usage rights. Requests for routine troubleshooting and for functional enhancements were now very low. Auditor productivity and customer satisfaction were high. A number of hospitals whose revenue auditing competency had progressively matured beyond their need for continued audit training or consulting by our firm, opted to remain as customers on a newly-created, “software only” fee basis. Internal coworkers’ general satisfaction with information technology was now also high, with their own increased opportunity, as well as their customer counterparts coinciding opportunity, to maintain focus, not on computer systems and software workarounds, but instead on the accomplishment of their intended value-added tasks. __________________________________________________________________________________________________
Organization: Leading California Retail Bank
Organization: Leading National Real Estate Information Provider
Organization: Healthcare Revenue Management Training Conssultancy
__________________________________________________________________________________________________
Organization: Healthcare Revenue Management Training Consultancy
