6/28/2013

New Reports on Sales Data and Commissions

I spent the past few days working with the sales data.

There are two feeder systems, one for sales people, the other for an online cart.

There's a website which grabs the data from these two locations to display on the web app.

Starting Monday, the data will go directly into Great Plains instead of a manual process.

So now the data will be available much quicker.

So I have a query which pulls from Great Plains, which I pull hourly, from an SSIS package.

Then I have another job which pulls info from SalesForce.com database to get the Territory info.

Then I mash those up, and write to another table, which will keep record of all the Sales with the Territory and Rep which runs hourly.

The data will be static so we can report over time both Sales Volume and Commissions.

Not only can we report on New Business and Renewal Business, we can start processing Commissions on Channel Reps as well.

And I believe the website may break on Monday so I prepared by giving the Users an SSRS report with parameters like @StartDate @EndDate @SalesType and @SummaryDetail.

I also plan to email the reports every hour, once we get the SharePoint SSRS to send Emails.

It was a very busy week, for the entire department, as we are also migrating our instance of SalesForce.

Pressures on next week.  Can't wait!

6/27/2013

Swimming in the Raw Data

Reconciliation.

Comparing numbers.

Raw details.

Line by line.

That is one of my strong points.

I did this for prior job, for about 4 years.

Working for an insurance servicing company.

I had to tie the numbers from Month to Date, to Year to Date, to Inception to Date, which matched the Commissions statements, which matched the money received from the bank.

Not every programmer has an appetite for this level of minutia.

However, I seem to enjoy it.

And this week I've gotten a chance to reconcile the two systems, line by line, look for variations between feeder systems and the Great Plains.

The only tools I use basically are T-SQL and Excel.

A bunch of Temp tables and lots of queries to slice the data in multiple ways, look for patterns and trends and anomalies, which I send to the users for clarification on business rules and what have you.

I enjoy swimming in the raw data, not everybody does.

And so it goes!

6/25/2013

What Title Should A Data Person Use

So how does someone in the 'data' space market themselves.

Report Writer.  Data Specialist.  Data Architect.  ETL.  Database Developer.  Business Intelligence Developer.  Data Scientist.  Data Warehouse.  Big Data.

Perhaps Full Life Cycle Business Intelligence Developer.

Truth of the matter is all apply.

Data is not a one package fits all job.

We touch on many facets.

Not to mention process analyst, project manager, business analyst, quality assurance, data governance, data cleansing.

Data covers a wide spectrum.

And if you code as well, like I do, you get recruiter calls from many avenues.

I like working with data.  And the customers.

Sure beats digging ditches.  Which is my actual degree (archaeology).

Oliver Twist as a Programmer

Um, I'd like the tools I need to do my job please, Sir.


The master was a fat, healthy man; but he turned very pale. He gazed in stupified astonishment on the small rebel for some seconds, and then clung for support to the copper. The assistants were paralysed with wonder; the boys with fear.
'What!' said the master at length, in a faint voice.


Please Sir, I want some more.

The master aimed a blow at Oliver's head with the ladle; pinioned him in his arm; and shrieked aloud for the beadle.
The board were sitting in solemn conclave, when Mr. Bumble rushed into the room in great excitement, and addressing the gentleman in the high chair, said,
'Mr. Limbkins, I beg your pardon, sir! Oliver Twist has asked for more!'

Wouldn't it be nice if we all had the tools required to do our jobs?

6/24/2013

Pseudo Code Lives Forever

We've all been there before.

We write some quick code to solve a quick problem.

Next thing you know it, your quick code is now production code.

To be maintained and supported from here until forever.

If you had known the code had this importance, you would have done a better job on it.

This happens a lot in the world of programming.

Pseudo code meant for one time fix, become part of the infrastructure.

Funny but true!

6/18/2013

#BusinessIntelligence Skill Set

It occurred to me recently, while talking with a co-worker.

I now have full life cycle development skills.

From spec gathering, to understanding the database, mashing data with other data, to data modeling, table creation, ETL to staging with SSIS, applying business rules, moving data to Fact / Dimension tables, to porting data to SSAS (Tabular Model), refreshing the data, reporting via PowerView, PowerPivot, PerformancePoint, SSRS, plus Crystal Reports and Actuate and Java.

As well as programming in Microsoft Visual Studio c# / Visual Basic doing ASPX or Windows Service or Console apps.

To scheduling SQL Agent jobs to run throughout the day.

And some experience / training on Big Data - Map/Reduce - Hive - Impala - Sqoop - Pentaho Kettle.

Skill set is finally starting to take shape.

Taken a while, better late than never.

Better Late than Never

Microsoft BI Stack

Missing Link - SharePoint

SQL Denali

6/12/2013

#PerformancePoint Dashboard Almost Complete

Not much news on the home front.

Spent the day yesterday creating a PerformancePoint dashboard.

Luckily they hired a new Great Plains Dynamics admin and he's got some SharePoint 2013 experience.

So we sat together today and he showed me how to format the data, how to add Filters and how to create year over year comparisons with variance using KPIs.

So on my first Dashboard, I've got 4 KPI's, 2 by month, 2 by quarter, 3 reports in grid view and one filter/parameter by month connected to another report.

Just need to create the Yr over Yr KPI variance and I'll be ready to show it to the users.

All in all, I've got 5 Tabular Model SSAS data sources for this Dashboard.  Which means I've got 5 SSIS package / Stored Procedures to pull the data locally, 5 Visual Studio Tabular Model projects to create the Cubes, 5 SSIS packages to refresh the Cubes, and a whole bunch of SQL Agent jobs running through out the day.  All stored in Team Foundation Server.

It will look really cool when its done.

6/07/2013

#HADOOP Data Stored in Several Locations

So let's think about this for a minute.

You have data in your Relational Database, you have web logs and perhaps sensor data.

So the data exists in one place.

You push all this data to HADOOP , which gets stored in the HADOOP File System (actually in 3 separate places).

So the data exists in two places.

Then you push some of that data to a NoSQL Database, perhaps HBase, which makes another copy and stores it on top of the HDFS system.

So the data exists in three places.

Then you create some HIVE tables, which stores another copy in the HDFS system.

So the data exists in four places.

Then you export that data to perhaps Excel or back to another Relational database for Users.

So the data exists in five places.

HADOOP is known for storing huge data on commodity hardware to save costs.

However, as we just stepped through, it could end up costing you way more because the data is replicated in several different places.

Just something to consider when determining total cost of data ownership.

Programmers Who Were Once In Management

I used to be a computer programmer.

And then became a Supervisor.

And now I'm back working as a Programmer.

I think it was a good experience.

To see how the other half works.

When I was a Team Lead, I needed the work to get done.

Each person on the team had a certain level of skill.

And a certain pace in which they worked.

And a quality in which the work was done.

And I didn't see it change much over time.

Obviously you want someone who does great work, and lots of it.

One who takes the project by the horns and runs with it.

You get to meet with customers and gather specs, hold meetings, and do performance appraisals.

So now that I'm back working a regular programmer, you have empathy for those in charge.

And the funny thing is, I see lots of programmers that were once Managers or even Chief Information Officers.

I guess many people jump back and forth between being technical and managerial.

I always figured that once you make the leap into a management role, you lost your skill set.

This is probably true for the most part, but not entirely.

I suppose working with the code is simpler than working with humans.

That's my story and I'm sticking to it.

Want It Done Right, Do It Yourself

If you want something done right, you better do it yourself.

First off, just because you're paying someone, doesn't mean you are getting better quality.

Some people just go through the motions.

Or take short cuts.

Or the easy way out.

When you do something, you put your effort into it, I would hope.

Because you have a vested interest in the outcome.

You are not performing a service for monetary gain, you are doing something for a particular reason.

Whatever that may be.

And if you have to, pay the money to learn to do it yourself.

Sometimes the word professional means "exchanging service for money", however there's no guarentee of quality.

Or perhaps they fix one problem and cause three others.

So if you want it done right, do it yourself.

Because you have to live with the consequences, not them.

6/06/2013

The Big Bang of Technology

It seems DATA has fragmented into thousands of tiny pieces.

From artificial intelligence to predictive analysis to sentiments analysis to big data ecosystem to data warehousing to data modeling to nosql databases to relational databases to reporting to business intelligence to in-memory to cloud to embedded to web to client server to visualization to traditional reporting to self service to data scientist to analytics to streaming to real time data to long data to graph data to parallel warehouses to data silos to data cleansing to data governance to ETL to ETL to business rules to gathering specifications to business analysts to quality assurance to standard deviations to small data to slicing to data sets to data pools to complex data to correlations pattern recognition to store all data forever to unstructured data to semi structured data to structured data to chief data officers to queries to joins to tables to I think I need some aspirin as I'm getting a headache.

With all these topics, how can anyone possibly keep up with all the different products out there, vendors, versions, let alone become experts in every possible facet.

Did I mention these are DATA topics, we haven't mentioned security, hardware, servers, programming, mobile devices, operating systems, CRM, financial packages, Salesforce, the list goes on and on.

We have witnessed the 'Big Bang of Technology'.

Everything is moving away from the center in different directions at fast speeds, as does the Universe.

We can never put back into Pandora's box.

This technology is growing faster than anyone could have imagined.

In 10 years we'll look back at the infancy of the Big Bang and reminisce on the Golden Years.

For now we can run as fast as possible and hope to get a glimpse of the evolving wonder that which we base our lives.

And so it goes!

Get Sh#t Done!