By Glen R. D’Abate Acmeware, Inc.

1. Profitability By [Fill in the Blank]Using the MEDITECH DR and SQL Analysis Services to Quantify Profitability By Glen R. D’Abate Acmeware, Inc.

2. Objective To develop a SQL Query Analyzer-based On-Line Analytical Processing (OLAP) Cube that will provide information about profitability measures across a wide variety healthcare business parameters

3. Project Design Environment • MEDITECH Data Repository MAGIC 4.9 on SQL Server 2000 • MEDITECH ADM, ABS, B/AR, & CA Applications • Cost Accounting data could be provided by a third party with minor modifications • SQL Server Analysis Services Implemented on DR Server • Internet Explorer 6.0 used to browse the OLAP cube on an ASP.NET web page that resides on Internet Information Server (Web-Server) • We could also use Microsoft Excel to browse cube

4. Project Design Strategy • Identify OLAP Measures • Identify OLAP Dimensions • Build SQL Server View with Fact Table Containing Measures and Foreign Keys to Dimensions • Build SQL Server Views for Dimension Tables • Construct OLAP Cube and Process it • Deploy Cube to Web Page

5. OLAP Measures • A set of values that are based on a column in the cube's fact table and are typically numeric • In our cube: • Count of Visits • Receipt Total • Cost Total • Charge Total • Expected Reimbursement • Profitability (Calculated Total) • Expected Profitability (Calculated Total)

6. OLAP Dimensions • The ways by which would like to break out and view the measures “slice and dice” • Dimensions often correspond to MEDITECH dictionaries • Time Dimension are treated special in Analysis Services and can be defined with a hierarchy (i.e., Year / Quarter / Month / Day) • Dimensions can have a hierarchy (i.e., MDC -> DRG, ProvderGroup -> Provider, InsuranceGroup -> Insurance) • Dimension are limited to 64K entries at any hierarchical level. As a results, to use AccountNumber, we must associate it in a DischargeDate -> AccountNumber hierarchy

7. Dimensions In This Project: • Admission (Inpatients) or Service (Outpatients) Date • Transaction Procedure • Charge Category • Diagnosis • Discharge Disposition • Discharge (Inpatients) or Service (Outpatients) Date • AccountNumber • Facility • Final DRG • Financial Class • Insurance • Location • Patient Status • Attending Provider • Service There are dozens of other potential dimension fields that could have been selected for this project

8. Fact Table • The central table in the OLAP schema that contain the numerical Measures and Foreign Keys relating facts to the Dimension tables • A SQL Server View is used to bring together data from multiple tables in the DR to a single Fact table • All columns in the Fact table should have data values. NULL data must be substituted with a value (“<U>”)

9. DR Tables used in Fact Table • BarVisits (demographics) • BarVisitProvider (Attending Provider) • AbsDrgData (Final DRG) • BarChargeTransactions (Charges, Charge Category, Transaction Procedure) • BarCollectionTransactions (Receipts) • CaStandardCosts (Costs) • BarInsuranceLedger (Expected Reimbursement)

10. Fact Table View (Foreign Keys) If there is any chance that a NULL value could be present, replace it with ‘<U>’ Append leading zeros to DRGs to display in a more typical form Use only left component of the diagnosis For dates, use an integer identifier in dimension and fact tables

11. Fact Table View (Measures) Charges come from Charge Transactions UNION Receipts and Adjustments come from Collection Transactions UNION Expected Reimbursement is at the Visit level, not the Transaction Level

12. Dimension Table View Example Dimension View for the Location Dimension Use the MIS Dictionary with Locations ‘<U>’ is included via a UNION in all dimension to account for any NULL values The LocationID and Name are concatenated together when more detail is required in the cube

13. Once SQL Views are defined, SQL Analysis Manager is used to construct the Profitability cube and related components SQL Analysis Manager Data Source Links to DR ‘livedb’ Profitability Cube Defined Dimension are defined in a wizard using the Views that were set up in SQL Server

14. OLAP Cube Components • Data Source • Fact Table • Dimension Tables • Measures • Calculated Measures • Design Storage Schema

15. Tool for Building Cubes Select the DR Server Select Security Profile Select Cube Database

16. Entering a Cube using the Cube Editor Fact Table Analysis Services has useful Wizards that assist in configuring the cube Dimension Tables

17. Adding Measures to the Cube Measures are available from the Fact table (View) and are inserted into the cube definition

18. How to compute Profitability? RECEIPTS – COSTS COSTS Expected Profitability? EXPECTED REIMBURSEMENT – COSTS COSTS

19. Adding Calculated Members Formula expression for Profitability added to Cube All Cube data and many pre-defined functions are available in the Calculated Member Builder

20. Cube Design Storage Options

21. Browsing Data in the Cube Editor The Analysis Server provides a rudimentary browser to give a quick ‘reality check’ that the cube is compiling correctly

22. Deploying the Cube in Visual Studio ActiveX Control – Pivot Table 10. 0 Office Component Visual Studio .NET solution. Originally deployed as Visual InterDev 6.0 Solution It is also possible to deploy cubes using SQL Server Reporting Services, albeit a bit cumbersome

23. Web Page with Cube on Internet Information Server Visual Studio .NET project is deployed to an instance of IIS This example includes the Active Server Page (ASP) with the Profitability Cube ActiveX control within a Web Application (AcmeView) that has other OLAP Cubes AcmeView Web Application in IIS

24. Browsing the Cube using IE