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.

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.      

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.

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.

Remote Support

This is a story about a potential huge security breach that affects anyone who may use a computer in their home and should be taken into consideration for yourself as well as friends and family.

A little over a year ago, I received a call from an unknown number where the caller represented themselves as Microsoft.  The caller, who spoke in a broken English dialect, indicated that there was a problem with my computer and that I should log into it right away.  Almost immediately I was skeptical of the caller’s intentions to help me as I understood that as a computer technician myself, I knew that my computer was in exceptional shape and that I would not be in any real danger.  I decided that I would play along until it got too serious as I expected the call would go in that direction.

The caller that I worked with was exceptionally clear on the steps I needed to follow and actually presented himself in a way that would make anyone believe that they were in a legitimate role and actually trying to help.  He asked me to go to my computer and power it on if I had not done so already.  As it turns out I was off from work that day and was at home with the computer already on.  The caller asked me to go to my start button, the Windows button that is in the lower left hand corner and continued to give me instructions to open a command prompt.  He asked me once the command prompt was open, to type out the following command:

netstat -n

Without going into great detail, the netstat command displays network connections for your computer.  One of the fields in the results is named “Foreign Address” which he asked if there were any numbers for me when the results came back.  Well of course it did because one is generally connected to the Internet.  He indicated that, with giving no evidence mind you, that those connections were to China and I was in the process of being hacked.  Well, Probably not!

I believe at this point anyone would feel that they were in serious trouble and would be open to any help that could be given.  Although in fact I or others were in not trouble whatsoever at this point, the trouble would begin now.  The caller indicated that they could help stop the hacking from China and that there would be a few additional steps.  The caller had me open an internet browser go to the website of one of the popular remote tools, such as TeamViewer, Join.me or GoToMeeting.  I continued to go along with this rouse and downloaded and subsequently installed the tool as requested.  At this point it was clear to me that he would gain control of my computer shortly and have the ability to do anything he wanted to by way of having free remote access to my computer.

It was at the point that the caller was about to remotely connect to my computer, that I decided it was time that this charade should be stopped.  We were at the point that in TeamViewer, I believe that the program had installed and presented a number that a remote person, using the same program could get access to your PC.  I stopped and began telling him of my IT experiences, what NetStat was doing, what he was about to do and why I had to stop him.  Before I hung up on him, I told him that he should divert his seemingly good computer and customer service skills in a positive direction, as opposed to hijacking people’s computers.
 

In the weeks that would follow, I believe I received at last one more call from this person or one of his co-conspirators, attempting to gain access to my computer.  If this was happening to me, I became concerned about others out there, who may be more naïve when it comes to computers and networking.  At a local festival, I happen to notice a detective at a display booth for our police department.  I asked him if there was anything law enforcement could do as I was not sure what kind of crime this was, but seemed like one nonetheless. Based off his response, it was kind of a dead end as it was my understanding that they were somewhat helpless in this regard.  This article is basically meant to be a warning to others as I am not sure if there is another forum to alert others of this what I call attempted computer hijacking.  

 In summary, here are the points I wish for those to remember:

• Microsoft (or Windows as they call themselves sometimes) will not call you!
• Never give remote access to your PC to anyone you do not know.
• The best prevention is to hang up on these people as soon as possible.

SSIS Consulting Services

SSIS Consulting Services from After-Hours Coders

I developed and maintained over 100 SSIS packages in SQL Server to automate manual processes and solve business issues.

I have been:

• Developing and administering packages since 2007 in SQL Server 2000 through 2012.
• Upgrading SQL 2000 DTS packages to SQL 2005 and higher.
• Used SQL, Oracle, XML, CSV, SharePoint Lists and other sources for inputs/outputs in SSIS packages. 
• I have extensive experience loading data to Oracle databases using SSIS Packages as well.

I earned a MCITP in SQL Server 2008 for Business Intelligence Developer and a MCTS for Business Intelligence development for SQL Server 2005 and 2008.

Besides training, I have gone to numerous SQL PASS (Professional Association of SQL Server) conference sessions on SSIS best practices, presented by industry leaders.

Since I work full time as a DBA/BI Administrator and would be looking to do this contract work on the side, working on this in the evening with the ability to communicate during the work day.  Would this be a possibility?

Thanks for your consideration!

http://www.afterhourscoders.com

Remote DBA Services

Hourly and Part-time SQL Server DBA Support

At After-Hours Coders, we can provide comprehensive support for your database operations and enhanced performance. Since these services may not be needed on a full time basis, you only pay for the time we spend with you, when you need it.  We can provide these services by being be on-site on a limited basis in the Milwaukee WI or even the Chicago IL area.

Featured SQL Server Remote DBA Services

In addition to the possibility of SQL Server support on site, we can offer also offers complete remote SQL Server database administration services. We are fully capable of addressing integration, maintenance, optimization, and upgrade needs. Some of the featured SQL Server DBA services include:

• Administration and Maintenance Services
• Troubleshooting, optimization and performance improvements
• Database Development
• Business Intelligence, including:
• SQL Server Reporting Services (SSRS),
• SQL Server Integration Services (SSIS)
• SQL Server Analysis Services (SSAS)
• Support Services
• High Availability Services
• Clustering
• Mirroring
• Always On
• Installation/Upgrades/Moves/Decommissioning

We have experience with SQL Server 2000, 2005, 2008, 2008R2 and 2012 on Physical and Virtual (VMware) Windows servers.

We are Microsoft Certified IT Professionals for Database Administration and Business Intelligence.

Please contact us for more information!

Oracle DBA Tracking Center

For the code or questions: Please contact us at After-Hours Coders

When having to track multiple Oracle databases spread across multiple servers and tying the database to an application, it is difficult to follow all the instances.  This tool, is a comprehensive solution to have one common location for DBAs and other personnel in an IT department to know the status of their Oracle database instances.

The tool uses a SQL Server database, the SQL Server Integration Services ETL tool and the SQL Server Reporting Services reporting tool to compile and present the data.  You will need at least SQL Server Standard Edition to run the SSIS component.  

The design of the solution is meant to answer the following questions:

·         What are my Oracle databases?

·         On what server are they running?

·         What platform is the server?

·         What Oracle version are they?

·         When did the database last startup?

Through a minimum amount of data entry, you can add data to help find out:

·         What application is tied to this database?

·         Is it Production or Development?

This ETL can be scheduled to run as often as every couple of minutes to keep information up to date. 

What do You Need?:

·         SQL Server 2005 Standard Edition or Higher.  This is built in SQL Server 2008 R2.  Install with the Database Engine, Integration Services and Reporting Services.

·         Oracle 10 Client or higher on the same server or computer you will run the ETL package.

There is a database in SQL Server to store information about the Oracle databases. The database name is “DBTRACK” on a SQL Server database instance. When the ETL Runs to completion, updates can be viewed by a running a collection of SQL Server Reporting Services Reports.

Once installed, all you have to do is enter the Oracle instance or database. This tool does the rest!

Here is the ETL in SSIS:

There are two reports available:

The first is the “Applications” report that shows all applications and the related database and type, development or production as well as server.

The second report shows the detailed database information.  The parameters asks for application and type, but defaults to all.

For the code or questions:
Please contact us at After-Hours Coders

Consulting Services

I’d like to inform people of my consulting services available.  We are available for consulting nationwide but can work onsite in limited engagements in the Milwaukee, Madison and Chicago areas. We are based in the Milwaukee area.

We can complete the bulk of your work offsite through remote connection or offline.

We specialize in:

SQL Server Databases:

• SQL Server Database administration and Data Warehouses.

 Business Intelligence:

• ·         Microsoft:

o   SQL Server Integration Services (SSIS)

o   SQL Server Reporting Services (SSRS)

• ·         SAP:

o   SAP Crystal Reports

o   SAP Business Objects Enterprise

Please see my website for more information and details:

http://www.afterhourscoders.com/services.html

Business Objects Enterprise Timeout

When running a new, very large report, I encountered this generic error in my Business Objects XI 3.1 SP2 environment using Infoview on IIS.  

“An error has occurred. Request timeout.”

Here are the steps to resolve the issue that I used on my Windows 2003 R2 Application Server.

Go into IIS (Start/Run/ inetmgr)

Right Click on the “default web site”, choose properties.

Bump up “Connection timeout” seconds to 900:

Tab over to Home Directory, configuration and options.  Update ASP Script Timeout to 900 seconds:

Finally, update the .NET settings in the Machine.Config file.  Please backup the current file before you begin!

C:\WINDOWS\Microsoft.NET\Framework\v2.0.50727\CONFIG

Add within the system.web section:

Save, exit.  Restart IIS by typing “iisreset” on the DOS command line.