Thursday, December 11, 2014

Setup and Maintenance in the Epic Cogito Data Warehouse

I have had the pleasure to work with the Epic Cogito Data Warehouse application and database for the last year in a SQL DBA and Cogito Administrator role.  The Cogito Data Warehouse (CDW) is a Central Data Warehouse with a dimensional Model for Reporting that can combine Epic and external data, using ETL packages to populate from Clarity.


The first step is to secure a very large and powerful server to house the Cogito Database and Application.   It is possible to spate the application and database, but currently Epic recommends that they stay together.  So why would the server need to be so powerful?  We are targeting query workloads patterned for large sequential data sets rather than small random data transactions.


The installation of the CDW is both straightforward and well documented by Epic.  To be able to install, all you really need to have in place is:
  • The aforementioned server, with SQL Server 2012 installed. 
  • I would also suggest having ready a service account that can run the service and be highly privileged on your SQL instance
  • Once installed, all you need to do is create a blank CDW and CDW_STAGE database in your SQL Instance.      
The installation screen in version 7 is greatly improved, as it is a one-stop shop for entering all the server, database and user information.

Post Installation:

There are a couple of post installation steps to follow once you have successfully installed the Cogito Data Warehouse.  The first is to secure your CDW on the database and application side. 
  • The DBA can handle the SQL Server side of security in SQL Server Management Studio.
  • Go into the Cogito  application itself under Configuration\User Administration and assign the appropriate rights based off the need of the user. 
Remember, the rule of thumb is to ask yourself: what is the least amount of permission they need to do their job?

The second important part of post installation is to Post Installation is to enable row update tracking.  This enables ability to pull data from Clarity Tables.  To find out what tables that Cogito needs from Clarity. 
  •  Run Console Report in Configuration > Reports > Clarity Requirement.
  • Using that list, enable Row Update Tracking by updating table in Compass
  • To synchronize the updates, Run ETL job to update those tables in Clarity.

Once you have the application installed, configured and secure, it is a good time to test an execution and begin backfilling data from your Epic Clarity environment.

Before you begin backfilling, in configuration, check the maximum number of rows you are extracting.  The default setting is 10,000,000 and you may need to bump up.

Simply create a new execution, select the table(s) you wish to backfill and run the execution.  These executions may take quite a while, especially on some of the large tables.

You can check progress in Backfill Status Monitor.  Prior to V5, the only way to check on your progress was to compare counts in Clarity and CDW.  You can find the Clarity extract SQL code is in the dictionary editor and run against your CDW and Clarity environments.

There are a couple tips for succeeding in your backfill.
  • Go for the smaller tables first.  This is of course the proverbial low hanging fruit!
  •  Go after the bigger tables like BillingTransactionFact and FlowsheetValueFact last.  Break up the backfills into smaller multiple executions so the executions do not run too long.
Ongoing Maintenance

Although this is less the case with Version 7, during the early days of Cogito, we encountered many performance issues and came up with a few workarounds.  I believe that as a Cogito Administrator, your goal is to get the best runtime possible for the CDW ETL processes. 
  • Database Health – CDW:
o   Update All Statistics on tables in CDW_STAGE: dbo.*/Epic.*/config.*
o   Defrag of all indexes at 15% or more fragmented.
o   Update All Statistics on all tables each week.  Updating Statistics updates query optimization on a table and helps choose the most optimized query plan.
  • Database Health – Clarity:
o   Update All Statistics on tables:
o   Once a week, Defrag all indexes update statistics
  • On Clarity:
o   Start as early as possible!
o   Run CDW/critical tables first by marking them as high priority in the execution.
o   Load “Full-on” tables nightly as after version 5, was a requirement.

At After-Hours Coders, we offer expert Epic Consulting services for your business, based off our years of experience. Combined with our SQL Server Database Administration Services, we can successfully implement, maintain and enhance your Epic Clarity and Cogito Data Warehouse environments.

After-Hours Coders is based in Muskego, Wisconsin serving the Milwaukee, Madison and Chicago areas in person or nationwide remotely.

No comments:

Post a Comment