Monthly Archives: January 2017

Duplicate objects in SSIS (ErrorCode Column)

In SSIS (multiple versions) you can get a seemingly random error when trying to create an OLEDB Source Component.

Full Error:
The package contains two objects with the duplicate name of “OLE DB Source.Outputs[OLE DB Source Error Output].Columns[ErrorCode]” and “OLE DB Source.Outputs[OLE DB Source Error Output].Columns[ErrorCode]”
The error is caused by a dumb, but in hindsight sort of obvious, problem.  The source query I was using is below.
SELECT [SurveyID]
      ,[VendorSurveyID]
      ,[SurveyStatusID]
      ,[SurveyStatusName]
    ,[ErrorCode]
     ,[ErrorDescription]
      ,[ErrorPriorityID]
      ,[ErrorPriorityDescription]
      ,[InsertDate]
  FROM [CurrentSurveyStatus] s
The problem is that my source query had a column explicitly named ErrorCode – which happens to be the name of one of the default built in columns that are piped to the component error output.  The same would be true if I had a column in my source query called “ErrorColumn”.  It’s not readily apparent that these output columns exist because they only would show up if you use the component error path, or if you look at the Advanced Editor as shown below.

SSIS won’t let you remove the default SSIS ErrorCode column from above, but it will let you rename it in the Advanced Editor.
So you have two options –
  • Rename your column
  • Rename the default SSIS column
Below I renamed the default column as you can see in the Meta Data Viewer, and the component no longer has errors.
Note: The warning is just that no rows would currently be sent to the error output in this example.

Letter to Elected Officials re: ACA Repeal and CF

*UPDATE 9/21/17: With the added traffic to this post lately from Twitter, I wanted to add:

Next month, October 14th, I am doing a Stair Climb fundraiser for the Cystic Fibrosis Foundation to raise money for those affected with this fatal genetic lung disease – like my wife Mary.

In addition to fighting for the ACA, can you help me fight for a cure for Cystic Fibrosis? Thank you! Please visit http://fightcf.cff.org/goto/ehudzikCFClimb2017

—————

Below is a copy of a letter that I am sending to various federal government officials to raise my concerns about the pending fight over changes to the Affordable Care Act (ACA) aka ObamaCare.  This isn’t political, although it references party affiliation, but it is advocacy for those with chronic illnesses and other healthcare issues.  Feel free to share.

——————————————

To whom it may concern,

My name is Erik Hudzik, and although I do not live in your district or state, as you are _____________ I am contacting you regarding a matter that is deeply important to my wife Mary and I – The Affordable Care Act.  In this letter I am going to set aside partisan bickering and try to explain to you some of the very real consequences that you and your party’s cavalier attitudes towards repealing the ACA will have on millions of Americans like us.  I’m going to do this by telling our story.

My wife Mary has Cystic Fibrosis (CF).  CF is a fatal genetic lung disease that, among other things, slowly, but surely, degrades lung function until the inevitable need for a lung transplant which brings its own complications.  She is one of 30,000 Americans with this fatal disease.  When my wife was born in 1982, her parents were told she would not make it to adulthood.  Thanks to science (a quick side note – please continue funding NIH research), the life expectancy for patients with CF now hovers around 40 years old and continues to improve.  She has spent her life chasing her life expectancy.  Despite these wonderful advances, my wife has to fight to breathe every day.  Her entire life she has had to spend hours in the morning and hours at night, every day, doing treatments to stay alive.  Every year, she is subject to several week or longer hospitalizations and several courses of home IV antibiotics.  Suffice to say that we are extremely familiar with health insurance and the costs of medical care.

Mary is the youngest of three girls and is the only sibling affected by CF.  When she was born in 1982, her parents also had to very quickly become familiar with our country’s odd health insurance system now that they had a child requiring hundreds of thousands of dollars in medical care a year.  Her father was lucky enough to get a new job with better insurance, and went on to take over the business so he was able to ensure he – and the rest of his employees – could have quality health insurance.  The ACA made it easier for a small business owner like Mary’s father to offer quality insurance.  Many Americans are not this lucky.

Fast forward to 2006 when Mary and I got married at the age of 23, and health insurance now became our responsibility.  Since this was prior to the ACA, Mary could not continue on her parent’s insurance.  So while we continued on to graduate school, I had to make sure to also find a full time job that offered very good health insurance.  Many Americans are not this lucky.

As you might have guessed, CF is a pre-existing condition.  Pre-existing since her birth.  There is no way we would have been able to get insurance on the old individual market.  After getting our graduate degrees, I was able to start my career with a good job for a mid-size company that offered good health benefits.  I stayed there for 5 years until they changed to a much lower quality health insurance plan that would not work for my wife.  I was forced to quit within 3 months, and thankfully found another job with better health benefits – again, many Americans are not this lucky.

My next job unfortunately lasted only 10 months before the employer folded back into its parent company and eliminated several jobs.  Thankfully, I was again able to quickly find another job with good health insurance that would cover my wife.  All of these job changes would have been much less stressful had we had the ACA exchanges to fall back on.

Now I find myself in a situation where I have several other promising opportunities to further my career – one of which would involve being an independent contractor for a period of time.  Now, thanks to your repeal and your “plan” to eventually do… something, I cannot take the risk of doing independent work because we cannot be sure that we will continue to have the option to purchase reasonably priced individual market insurance.  You’re ACA repeal efforts have already directly harmed the individualism and small business potential of hundreds of thousands of Americans.

Adding to all of this, because of the ACA, my father, who has been battling kidney cancer for years, was finally able to retire at age 66 as now there was an option, via the exchanges, to get insurance for my 63 year old mother, an endometrial cancer survivor.  Again, with a pre-existing condition, she would not have been able to obtain affordable, quality coverage on the old individual market.  With the pending repeal, my father may have to return to work, while still battling cancer, just to make sure my mother has insurance until she reaches 65.

We followed the health reform battle very intently.  I mean no disrespect when I say that I probably have a better understanding of health insurance and related policy and economics than many members of Congress in both parties.  It was incredibly disappointing, and frankly infuriating, that your party took up the tactic of complete and utter resistance to any efforts to work together to solve the agreed upon problems in the American Health Insurance system.  Despite starting with a market based framework which was originally developed by conservatives, and the Obama administration making every effort to include Republicans and their good faith ideas, your party refused to do anything to improve the pending law.  Instead your party uniformly and blatantly simply said “no” while trotting out lies about “death panels”.  These partisan and childish tactics should, frankly, be enough to completely disqualify you from even discussing changes 8 years later – you had your chance to shape policy, and flatly turned it down.

When the ACA was signed into law, and implemented, we were ecstatic.  We finally knew we would ALWAYS be able to purchase quality health insurance, no matter my employment situation.  It opened up countless possibilities for us and our economic future.  Despite many claims to the contrary, every year when we have looked at gold and platinum plans on the exchange in our area, the costs are extremely competitive with my employer provided insurance once the portion my employer pays is taken into account.  I can’t tell you the sense of relief the ACA gave us.

But that brings us to today.  In a matter of weeks, you have begun the process of destroying something that tens of millions of Americans have come to rely on – whether they know it or not.  You are upending people’s lives.  Every vague “policy paper”, press release, or speech, presented by Republicans has come nowhere near covering as many people with as high quality insurance.  Industry leaders from insurance companies to the AMA, and health care policy experts from across the political spectrum have warned you not proceed so quickly without having a real plan to move forward.  I fear that your party either does not fully understand what you are doing, or worse, simply do not care.  I know you have run on nothing but “Obamacare Bad!” for 8 years and you have boxed yourselves in – but please, don’t ignore the real life consequences of what you are doing.  Take what works – and is popular – within the ACA and improve upon it with reasonable good faith discussions.  Take this chance to have Republicans become part of helping to craft changes to the ACA that improve it for generations – not tear it down for short sighted political gain. Please, please don’t destroy what has brought health insurance coverage, care, and peace of mind to so many millions of Americans just for blatantly partisan political reasons.

I hope you carry the thoughts of my family, the positive impacts the ACA has had on us, and the fear we have for its pending repeal, with you for every vote that you make.  I plan to continue to update you on how your actions directly affect our family – just one of millions.

Power BI Forecasting Feature (and when your “Data is too irregular to forecast”)

In September, one of the new items Microsoft rolled out for Power BI, was a preview version of their Forecasting statistical analysis feature.  Since then they have moved the feature out of “preview” and it is readily usable on the Analytics tab on both Power BI desktop and web versions.  In this post we will look at the steps to utilize this new feature in Power BI Desktop to add some quick statistical analysis to your visualizations.

What is Power BI Forecasting?

Forecasting in Power BI gives you a quick and simple way to add a predictive forecast to a time series line chart.  Power BI Forecasting uses a method called exponential smoothing to come up with its predictions based on a time series dataset.  You cannot view or change the actual algorithm, but you can get more of an explanation of how it works on the Power BI Blog – https://powerbi.microsoft.com/en-us/blog/describing-the-forecasting-models-in-power-view/

Dataset

The data set I used for this demo is a collection of “Check-ins” from the beer rating app Untapped.  The full dataset had about 800 beer check-ins, but the full dataset is not posted here to protect the innocent.   I have a few rows of sample data below, but any time series data should do.  The “ExcelNumericDate” column is the MS Excel integer representation of a date, which I had to use as a work around for a problem I’ll explain below.  I’m sure there is a better way to do what I needed, but instead of fighting with formats, I just did it this way.

Let’s try to forecast how many beer check-ins I will have in the future based on my check-in data.

BeerName ExcelNumericDate CheckInDate
Tripel Karmeliet 42272 9/25/2015
Big Gruesome Chocolate Peanut Butter Stout 42272 9/25/2015
60 Minute IPA 42273 9/26/2015
Not Your Father’s Root Beer (5.9%) 42274 9/27/2015
Rumpkin (2015) 42275 9/28/2015
Oktoberfest 42279 10/2/2015
420 Extra Pale Ale 42284 10/7/2015
Samuel Adams OctoberFest 42287 10/10/2015
Corona Light 42287 10/10/2015
60 Minute IPA 42292 10/15/2015

 

Using Power BI Forecasting

First we need to load our data set into Power BI.  As shown below, from the Home tab in Power BI Desktop, select Get Data, and the type of format your file is in.  For this demo I used a .csv file that I had saved as a copy of an .xlsx file.  You can see I have added two columns, beer_name and CheckInDate to the canvas.

Next, in order to get discrete dates, instead of the default Power BI Date Hierarchy, we need to click on CheckInDate and change the selection from “Date Hierarchy” to the column name, “CheckInDate”.

Now change the Visualization to a Line Chart, and make sure the CheckInDate (or whatever your date field is called) is set as the Axis, and the Count of beer_name is set as the value.

(the huge spikes are tastings at Beer Fests or great brewery tours with Liberty Brew Tours… I swear)

Now we just click on the Analytics magnifying glass, and select the new Forecast option, right?

 

My Data is too Irregular??

Unfortunately, no.  When you click Add, you won’t get anything on your Line Chart except a tiny red “X” in the upper left hand corner.  Click on it to get a bit more information.

“Data is too irregular to forecast.” “Your data isn’t evenly spaced … dates or numbers in a valid timeline must have a consistent gap between consecutive points”

After more trial and error than I’d like to admit, I realized what Power BI is telling us is that it can’t create a forecast because it doesn’t have a data point for each date.  If we go back and look at the sample data I have above, you can see that on 10/2/2015 the beer Oktoberfest was checked in, and then there was not another check-in until 420 Extra Pale Ale on 10/7/2015.

Oktoberfest 42279 10/2/2015
420 Extra Pale Ale 42284 10/7/2015

 

This is where I kind of have to assume I’m missing a way to easily have Power BI just assume a 0 value for any missing dates, but I wasn’t able to find it.  This becomes clear if you look at the Line Chart above – there are no data points at 0, only at 1 or greater.  An interesting side note, if you “drill up” to the month level with this data, you CAN add a forecast because in that case each month, when all check-ins are summed by month, have values.

My work around for this seemed simple enough, merge in another data set with all possible dates for the date range I wanted for the report, then use the dates from that 2nd data set for the X-Axis so every date in the range would have a data point.  Fast forward through a wasted hour fighting with 2 .xlsx files and differing date formats (eg: January 2nd 2017 10:00 AM vs. 1/2/2017 etc…), and I just converted the datetimes in both data sets, beer check-ins and All Dates, to integers in Excel and used that for the join.  Ugly, I know.

So we import the 2nd dataset of all possible dates we want and their Excel integer equivalent as shown below.

ExcelNumericDate Date
42005 1/1/2015
42006 1/2/2015

 

Click on Relationships on the left hand side, Get Data, and select your 2nd data set formatted as above.

Once we have the 2nd data set with all possible dates, we need to merge (AKA: Join) the 2 data sets.  So click on Edit Queries on the ribbon –

On the Query Editor screen, we go to Merge Queries > Merge Queries as New –

On the Merge dialog box, shown below, we select our Join/Merge key (ExcelNumericDate), and select our “Join Kind”, eg: INNER, LEFT, RIGHT etc… Be careful here to pick the correct type of join, Right or Left, depending on which order the data sets are in.

Now we close out of the Merge dialog box, and just have a couple other small things to do.

Adding Our Forecast

First, in the Query Editor, we can rename our new Query, here I’ve called it BeerCheckins.  Then we need to go to the far right of the list of columns to find our new “column”, or rather collection of columns, we “merged” and click the double arrows shown below to expand the new columns.  We only care about the Date column now because we will use this for our X-axis.  Remember, this is now the date from the AllDates data set so there is one row for each day.

We can then click Close and Apply in the upper left to return to our data, where we are going to add one last column to simplify summing our check-ins.  Right click on our new BeerCheckins table, and select New Column.

 

For this new column, named Counter, we want to make sure that every date that does NOT have a check-in (eg: no beer), we have a data point of 0.  So we can check the beer_name column to see if there was a check-in on each date using the below DAX function:

Counter = IF(ISBLANK(BeerCheckins[beer_name]),0,1)

This new column is added to the far right, and has a value of 1 if there was a check-in on that date, or 0 if there was not (*note: though we used the [beer_name] column for the function, it is not shown)

Now we can finally go back to our Report tab, add a new Line Chart, set the NewColumn.Date as the Axis (don’t forget to again toggle off the Date Hierarchy default), and the Counter column as the Value.

Answering the Question

And this time when we go to the Analytics tab and try to add a Forecast – we can create it successfully and we find that within a 95% confidence interval I will, in fact, have 4 beer check-ins on Saturday, January 14th 2017!

Always drink responsibly.