Feb 08

Loading Uncertain Data

Today’s post is going to be an interesting one.  I am currently working on a requirement to prepare a value performance report, which is essentially a measure:value report.  The interesting part is that the data source is from a cloud system that does not expose data via an API, so a staff person has to download the data to multiple Excel sheets and copy them to a network share.  Let’s say hello to variability, and not the good kind either.

Read the rest of this entry »

Feb 03

PowerBI: More Questions, Less Answers

Now that the PowerBI Enterprise Gateway is generally available,  I guess its time that I start to seriously evaluate how it fits into my organization’s business intelligence infrastructure.  Currently, we use Datazen to deploy dashboards and KPIs to the organization.  With PowerBi and SSRS 2016 on the horizon, Datazen will be going away.  Read on to see some of the challenges I face with PowerBI and see if you can maybe help me out.

Read the rest of this entry »

Feb 02

#AccidentalDev

I have been interacting more and more on Twitter lately.  In a few of my tweets I have been tagging them #AccidentalDev in order to describe my “newbness” at what seems to experienced developers to be common sense.  By tagging these posts I hope to establish a baseline of my ignorance that I can then use to compare my progress as I learn more things.  Catch me on the other side of the jump to see where you stack up against my #AccidentalDev life.

Read the rest of this entry »

Feb 01

Waste Water Quality Data Mart Design

This post is the second post in the Waste Water Quality project.  The first post that describes the project can be read here.  This post will describe the design of the data mart which will aggregate the data in order to report and perform data mining on it.

Read the rest of this entry »

Jan 28

Yet Another Project

If there is one thing that IT people never have a shortage of, it’s projects.  One would think that with being the sole business intelligence practitioner in a decent sized organization I wouldn’t even be able to think about taking on a project with the scope of starting a SQL Server user group.  However, an inspiring conversation with @grrl_geek on Twitter tonight has left me bound and determined to pull it of.

So if you are a SQL Server admin or BI practitioner in the Wausau WI area and want to meet up monthly to talk shop and learn new things then drop me a comment here or hit me up on Twitter / LinkedIn.  Why should Madison and the Fox Valley have all the fun?

Jan 27

Designing an Employee Dimension From Dynamics GP

Dynamics GP is not really known for its intuitive schema and clearly named tables and columns (I’m looking at your Mr. UPR00100 table).  Trying to extract data from this mess can be a nightmare.  Read on to see how I built an Employee dimension using 100% Dynamics GP data.

Read the rest of this entry »

Jan 26

The Road to SQL Server MVP

A month or two ago I was sitting in a one on one meeting with my boss talking about professional development goals when she casually mentioned wanting me to become a SQL Server MVP.  One of my co-workers had recently earned some big hoo-rah at Splunk, and my boss felt feels that I could benefit from obtaining a similar status in the BI community (Yes, just checked and she still wants me to do it).  Her argument for it was that we use the Microsoft SQL Server stack for BI and that I certainly had the capability to achieve it.

Read the rest of this entry »

Jan 25

Waste Water Quality Project

Recently at work I received a project to create a dashboard or report that would predict when certain waste water variables would exceed the permit limits established by the EPA so that the water treatment facility could intervene before a violation actually occurred.  This post will detail the project specifications and approach, and other following posts in this series will detail the technical implementation and results.

Read the rest of this entry »

Jan 21

YYYY-MM date format with leading zero’s

So one thing that I work with a lot is formatting date strings for reports and graph labels.  A really common format is the YYYY-MM (ex. 2016-01) date format with leading zeros.  This one was always a pain in the rear for me as I would have to extract the year and month, convert them to varchar, then prepend two 0’s and take the last two characters for the month, then insert the hypen and add it all together.  Overall the code would look something like this:

CONVERT(varchar(4),DATEPART(year,GETDATE()))+'-'+RIGHT('00'+CONVERT(varchar(2),DATEPART(month,GETDATE())),2)

Given that today is 1/21/2016, the output would look like this: 2016-01.

Read the rest of this entry »