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.
Prerequisites:
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.
Installation:
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.
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.
Backfill
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:
§
CLARITY.dbo.MEDICAL_HX
§
CLARITY.dbo.PAT_ENC
§
CLARITY.dbo.HSP_TRANSACTIONS
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