Predictions for Big Data and Cloud Computing in 2014

Now it is the time to review the past year and make resolutions for the coming year. Although prediction is very difficult – especially of the future, you can find lots of predictions for 2014. Here are predictions for big data and cloud computing – two of the 2013 big buzzwords:

• 12 Big Data Predictions for 2014
• Cloud 2014: Top 10 Trends to Watch in The Year Ahead

In these articles, we are looking forward to seeing that Hadoop will adapt to the volume, velocity and variety of data in 2014. That is, “Hadoop will power real-time tailored applications”, as well as techniques for “deeper security, deeper operational management, deeper resource management and multi-site replication.” Businesses are going to “leveraging the digital breadcrumb trail customers leave as they interact with products and services online”. Imagine how these data about customers can be magically turned into value:  analysis of customers behavior to provide more personalized services, fraud detection, anomaly detection, and trend analysis…

On the other hand, Cloud computing will become even more fundamental to business strategy. Clouds will be more specialized in terms of needs or verticals in order to differentiate from each other.

Open Source, such as R, may move from an alternative role to a leading role. As for this, it seems to make sense that when none of mainstream can provide a unified framework to solve problems, who will be willing to spend money on expensive licenses? So open source deserves as a playground for implement new ideas.

It was also predicted the move from the Internet of Things to the Web of Things.

So what is your plan to prepare yourself well for the coming year?

How to: Run SSIS in 32 bit Mode

SSIS is an excellent tools for extract, transform, and loading data. Nowadays, more and more machines that SQL Server runs on are 64 bit. However, there are some conditions that require SSIS to be run in 32 bit mode, such as

1. when SSIS tries to interact with Microsoft Excel or Access files that only support 32 bit.

2. ActiveX script task or Execute DTS 2000 package task.

3. Debug script tasks.

Sometimes if it is not set in a right runtime mode, you will get error messages like:

Error: 0xC00F9304 at Package, Connection manager "Excel Connection Manager": SSIS Error Code DTS_E_OLEDB_EXCEL_NOT_SUPPORTED: The Excel Connection Manager is not supported in the 64-bit version of SSIS, as no OLE DB provider is available.

Or even without error messages when you try to get your script task pause at a breakpoint inside the script. It seems never hit the breakpoint and does not pause as expected.

How to set 32bit runtime mode at PROJECT Level?

After the project is opened in BIDS, right-click the project on the Solution Explorer panel and then click Properties.

Then on the Property Pages, first select Debugging under “Configuration Properties” on the left. Then change the property for Run64BitRuntime to be FALSE. (The default value for Run64BitRuntime is True.)

 

Now every package under this Demo Debug Project is set to run in 32 bit.

How to set 32bit runtime mode at PACKAGE Level?

To set 32bit runtime mode at PACKAGE Level means that you can run just one package in 32 bit. This can be accomplished through SQL Agent job settings.

It is required to have X86 DTExec.exe installed in order to have SQL Agent job to execute in 32 bit mode. 32 bit version of DTExec.exe is often installed at

C:\Program Files (x86)\Microsoft SQL Server\100\DTS\Binn\DTExec.exe (for SQL Server 2008)

C:\Program Files (x86)\Microsoft SQL Server\100\DTS\Binn\DTExec.exe (for SQL Server 2012)

As shown below, when you schedule jobs, you can set the Execution Options in job steps to “Use 32 bit runtime”.

If you execute the package using DTEXEC via command-line scheduling process, you need to specify the 32-bit version of DTEXEC by explicitly running the DTEXEC.EXE from the right folder as shown:

"C:\Program Files (x86)\Microsoft SQL Server\100\DTS\Binn\DTExec.exe" /f C:\PackageFolder\Package.dtsx /Conf C:\ConfigFile.dtsConfig

Quality Measures in Healthcare

What is high quality health care? The answer can be “doing the right thing, at the right time, in the right way – and having the best possible results”. It is often measured in terms of cost, quality, and access as the so-called Iron Triangle of Health Care. Many healthcare programs have been set up to reward the quality of healthcare services, and to help consumers and payers make decision for the best use of their healthcare dollars. Today I would like to give a brief introduction about two common quality measures in healthcare that I learned through my work. One is HEDIS (Healthcare Effectiveness Data and Information Set) measures and the other is Physician Quality Reporting System (PQRS) . These are two main measures used for quality reporting.

HEDIS measures

HEDIS measures are developed, managed, and updated annually by the National Committee for Quality Assurance (NCQA). (NCQA is often referred as the “gold standard” in health plan accreditations. NCQA accredited health plans covers about 70.5% of all Americans enrolled in private health insurance according to NCQA’s own report.)

HEDIS measures address two types of care: preventive health care (children and adolescents, women and adolescent girls, adults, and seniors) and condition-specific care. Condition-specific care can be both chronic conditions and acute conditions. Sometimes HEDIS measures are categorized as chart review measures and claim-derived measures.

Patient-Centered Medical Home (PCMH)

NCQA provides PCMH program with the mission of improving the quality of healthcare. It includes clinical programs such as:

– Diabetes Recognition Program (DRP)

– Heart/Stroke Recognition Program (HSRP)

– Back Pain Recognition Program (BPRP)

Therefore, PCMH quality measures utilize the HEDIS measure set.

Examples of HEDIS measures

Johns Hopkins Medicine’s HEDIS tip sheet of September 2013 demonstrates how HEDIS measures are used by providers. You can see that CPT, ICD-9 codes, and UB Revenue Codes are used for billing purposes.

PQRS measures

On the other hand, Medicare has developed PQRS for its specially covered populations: primarily elder people over the age of 65 or younger with chronic disabilities. You can learn more detailed about PQRS from CMS here.

How to: Query BIT Data Type in SQL Server and SSIS

If you have ever met problems when trying to export BIT data type columns to files or use them in SSIS, here are the tips for you.

The bit data type is an integer data type that can take a value of 1, 0, or NULL. Thus it is often used to represent Boolean type values such as Yes/No, True/False, and On/Off. However, BIT columns might be interpreted differently by different providers. When you query BIT columns in SSMS, it displays as 1 and 0 as its value. However, when you either query BIT columns in SSIS or export them to text files, it won’t be 1 or 0 any more. Below will show you some tricks to handle BIT columns.

How SSIS handle BIT columns?

In SSIS, BIT columns are interpreted as Boolean [DT_BOOL]. How to tell this?

You need right click the OLE DB Source component and choose “Show Advanced Editor”. Then click the “Input and Output Properties” Tab. Expand “Output Columns” under OLE DB Source Output. You will notice that the BIT column is Boolean type as shown below. Here column IsSHP is the BIT data type column.

So when you need to query BIT column, you need to directly use TRUE or FALSE. Below shows that how you can query BIT columns in the conditional split task.

Note that there is no quote around true in the expression.

How to export BIT columns to files as integers in SSIS?

Solution 1: Using Derived Columns

Or if you want to store it with less storage, you can use DT_UI1 data type.

IsSHP_bit = [IsSHP] ? (DT_UI1)1 : (DT_UI1)0

Solution 2: Tweak OLE DB Source

First of all, you need to convert BIT columns in OLE DB connection manager from OLE DB Source Editor,

select convert(int, IsSHP) as IsSHP

Then change the DataType in the OLE DB Source to integer type DT_I4 as shown below.

Now you will get 1 or 0 as output for BIT columns.

That’s it and hope it will help!

Earn Certificates to Differentiate Yourself?

Becoming a certified DBA/developer won’t make you an exceptional DBA/developer ; being an exceptional DBA/developer doesn’t require any certification. But certifications can definitely add a plus if you have another competitor with the same other qualifications but without certifications. Below is the info about Microsoft SQL Server certifications for recent versions of SQL Server.

SQL Server 2012 related certifications

Microsoft Certified Solutions Associate (MCSA)

MCSA: SQL Server 2012 certification. It requires to pass three exams:

1. Exam 70-461: Querying Microsoft SQL Server 2012
2. Exam 70-462: Administering Microsoft SQL Server 2012 Databases
3. Exam 70-463: Implementing a Data Warehouse with Microsoft SQL Server 2012

Microsoft Certified Solutions Expert (MCSE)

There are two paths for expert level certificates. The prerequisite for taking expert level exams is to earn the above MCSA.

MCSE: Data Platform

1. Exam 70-464: Developing Microsoft SQL Server 2012 Databases
2. Exam 70-465: Designing Database Solutions for SQL Server 2012

MCSE: Business Intelligence

1. Exam 70-466: Implementing Data Models and Reports with Microsoft SQL Server 2012
2. Exam 70-467: Designing Business Intelligence Solutions with Microsoft SQL Server 2012

What will be the impact of SQL Server 2014?

From the article: How will the updates for SQL Server 2014 impact official Microsoft training and certifications? It seems that SQL Server 2014 won’t affect MCSA for now but it will definitely impact MCSE exams taken after 2014 March. So if you want to complete your expert level exam for SQL Server 2012, please be sure to finish it by March.

Reference: Microsoft SQL Server certifications

SSIS Validation Status “VS_NEEDSNEWMETADATA”

Problem                                                

Here is the error message:                                                   

Error on Component:[DFT_xxx]: "component "OLE DB Destination" (2327)" failed validation and returned validation status "VS_NEEDSNEWMETADATA".

It looks like some data mapping for columns in the SSIS package were lost after being deployed to the production. Both dev and production have the same number of columns. Also the name looks the same!

Solution

Check the columns to see whether they have the same case in the production as in the development. SSIS is CASE sensitive and it cannot automatically recognize those columns if the only difference is the case.

How to update column name?

You need to use sp_RENAME to update column name as below.

sp_RENAME '[dbo].[tableName].columnname', 'ColumnName' , 'COLUMN'
GO

Hope it will help you debug faster!