Have a Backup and Cross Train

As programmers we like to think we are indispensable.

Except from what I've learned everyone is replaceable.

And I've seen people who take on work that no one else knows the slightest thing about.

And the programmer can't take vacation because nobody know's their job.

This is good and bad.  Good job security I suppose.  Makes the person look like a work-a-holic.

But that person has actually taken hostage his own job.

And it's in the company's best interest to cross train everybody.

Especially the silo-programmers, who keep everything hush hush.

I've seen programmers leave for different positions and a few weeks or months later, some process doesn't work and nobody has the slightest clue where or how to begin fixing it.

No documentation.

I know I worked for a company for almost 4 years and could not take vacation.

Because I ran the month end, every month, with no backup.

So when I managed to schedule a few days off to travel to Long Island, NY for a tennis tournament, I actually had to run the month end from the hotel lobby computer at night after my tennis matches.

So I'm here to tell you now, cross train every position with a backup!

You won't be sorry!


Create Value

The thing that you worked on today is great.

You put effort in, you did your best, and that is that.

Five years from now, will you remember today?

Will you remember that line of code you put in production?

Will you remember the meetings you attended?

What things of significance will you remember five years from now?

I doubt the code you wrote today will still be production in the same format.

Most likely some other person will have re-written it in new technology with new business rules.

Think back five years ago from today?

What were you working on?

Did you create something of value?

Or did you cash your check for that week and toss the memory into the wind?

Never to be remembered or learned from.

So when you go to work tomorrow, try to create something of value.

Something that will last well into the future.

You won't regret it!


Think Tank

Think Tank.  That has always been a goal of mine.

To work in a place with like minded people who explore ideas and look for solutions.

I suppose that's what attracts me to Business Intelligence.

It's a growing field, un-chartered, ever changing.

And the people in the industry are extremely bright.

And have decades of experience.

So what right do I have to participate and contribute.

Who knows.

Except each Friday at 1pm on Twitter we meet up for #BIWisdom.

And we exchange ideas in 140 characters or less.

About the latest trends and topics surrounding BI, Big Data, Sentiment Analysis, Mobile, Open Source.

I like to watch the feed and occasionally contribute.

Sometimes my ideas are a bit different from the mainstream.

Perhaps because I'm embedded in the trenches on a daily basis.

Fulfilling data requests, talking with co-workers, watching managers search for accurate data.

I attended the Big Data conference in Orlando a few months ago and contributed to the Twitter stream #SQLSymposium and the facilitator of the event thanked me for my effort.  And I was given a Samsung tablet as well.

I really like the banter and exchange of ideas.

And it's the closest thing I've found to being part of a Think Tank.

And I'm grateful for the opportunity to partake and contribute.

Thank you!

Life of Data Flow into Info

Here's a diagram depicting the Life of Data Flow into Information...

Business Intelligence Hidden Purpose

Reporting and analysis is good for empowering the user to base decisions and actions to better the organization.

Sure, that's true.

Except I work with data day in and day out.

And you know the funny thing.

Reports help to expose problems with processes.

Because I'll get a call or email from a user, 'Why isn't this data showing up on the report?'.

After some analysis of the code, the business logic in the report / SQL reveals something that surprises the end user.

And then they have to decide how to handle the anomoly.

Change the front end business process and / or fix the bug in the front end software.

I can't tell you how often the reports expose issues upstream.

That's a primary function of BI that a lot of people don't talk about.

But should.

BI should tell a story.

And the stories I hear over and over are issues with processes or front end apps.


Quick, Fix This

So today I spent debugging a report.

What was the issue, I didn't know.

Just knew it was wrong and I was assigned to fix it.

So I found the report in Team Foundation Server, added it to my MS Project.

Looked at the SQL.

Then looked at the Spreadsheet.

Hmmm.  Still don't know.

So I sent an email to the user.

She called shortly and we discussed the issue.

I brought in another guy on the speaker phone.

So I knew what the problem was.

Next I cleaned up the SQL put everything in Table Variables.

And formatted the code so it was legible.

Then ran each piece of code separately.

There it was, one of the records was duplicated.

But why?

More digging.

Spent a few hours trudging through the logic.

5pm.  Holy cow the day flew by.

On the way out I discussed my findings.

Sure enough, when I explained what I found, I found the solution without knowing it.

Some data was not entered in one of the tables which was mandatory for the correct results.

So tomorrow I should have loaded data.

And then I can view the changes.

I think the other issue with the double entries has to do with a Left Outer Join which should be an INNER JOIN.

Will research tomorrow.

Moral of the story.

It IS possible to troubleshoot a report, without knowing the database structure, the business logic or anything about the report.

With a phone call and some analysis of the SQL code, you can fix bugs and be proficient at troubleshooting.

However, it's still essential to learn the business rules so you can troubleshoot quicker.

And then document the process' and really speed things up.

And there you have it!


Clarify Your Data

Describe what chicken tastes like?

Well, it tastes like, well, it tastes like chicken.

Huh?  Can you define it in more detail?

Well, it's a meat, which can be cooked in a variety of ways, best when tender, has a slight aftertastes and is best eaten warm.  It's similar to steak but less like fish.

Oh, okay.  So in describing 'chicken' you must reference other measures in context to describe what it actually is.  You can indicate it's similarities and / or differences to other known items.

Now can you tell me what the sales for last quarter look like?


Good compared to what?

Well, compare to 2 years ago, it's higher in volume and revenue. Same with last year.  Except the 1st and 3rd quarter were down compared to 2nd and 4th.  The NorthEast region looked strong in the 2nd half of the year.  It looks like SoutWest did not make its numbers.  However, we met our key performance indicator in 8 out of 10 areas.  We need to focus on consistent sales for every quarter paying particular attenion to the SW region and 1st and 3rd quarters.  And do more research to find out why this is the case.

Ah, so you can describe your data by comparing time intervals, KPI's and locations.

Precicely the goal of Business Intelligence.  Analize the data, by comparing and contrasting figures across different dimensions to produce a logicial provable conclusion in order to base actionable decisions on the future.

Thank you.

Business Intelligence Triange

Here's a diagram depicting Business Intelligence Triange between the DATA, the Business Units and IT.

The 3 can work in tandem as a cohesive unit to get the most bang for your buck.


Documenting Helps to Learn

I don't know about you but documenting things helps me to learn them.

Because in order to document, you have to trace each step of the process, verify what's happening where & when, take screen shots and then deliver the final result to somebody or someplace.

I enjoy it to be honest.

And as we know, documentation is only good for the time it was created as things will change and everyone forgets to update the doc.

I plan to put the doc on our SharePoint site and that will be that.

In the meantime, it allows me to ask questions from the developer for details on where everything is and how it works, etc.

I wouldn't want to make a living of it, but it does help in the learning process.

And when the dev goes on vacation in 2 weeks, and I have to change permissions to the SharePoint or Power Pivot site, hopefully I'll know where to begin.

Fun stuff!

SSRS Search for Keyword

I may have posted this before.

Except I use this query several times per week.

So I'm re-posting it for you and for me:

Simply copy & paste into SQL-Server SSMS T-SQL, change the Put_Keyword_here to the value you would like to search for and RUN...

Since SSRS stores the entire report in XML in the database ReportServer, the query basically scans every report and returns the matches - quite nice!!!!!

use [ReportServer]
      , CONVERT(varbinary(MAX), c.[content]))
      , c.[Path]
      , c.[Name]
FROM [ReportServer].[dbo].[catalog] c
where path not like '/Data Sources/%'
and c.[content] IS NOT NULL
AND CONVERT(varchar(MAX), CONVERT(varbinary(MAX), c.[content]))
LIKE '%Put_Keyword_here%'


Exceptional Service

We've been on a health food kick lately.

So we buy our fruits & vegetables from the market instead of the grocery store.

And we have been searching for a good one.

Most have a variety assortment to choose from.

And mediocre prices and customer services.

So today I said we are going to find a market with great quality, prices and customer services.

My wife replied "it doesn't exist".

As soon as she said that, I stopped at a new market.

Clean, no bugs, good prices, good quality, friendly service.

The lady answered our questions on how to pick ripe fruit, which ones were Delicious offered recommendations.

Just when we thought we were doomed to the rigorous lack of customer services experience with high prices and low quality food, our expectations were exceeded.

Not sure what country the couple / owners were from, the guy looked to be Russian but they had people of Mexican descent as customers and the place is in a Spanish section of town, so who knows.

However, we found a place with exceptional customer services.

And we will be re-visiting them weekly to purchase our stash of health food.

We also have a meat market we attend with good prices, quality food, wrapped to order with coupons on Groupon.

And then we buy in bulk at Costco, so we actually go to the real grocery store just as a ritual to pick up milk, bread and staple items.

So to summarize, the world of customer services has not completely gone to hell in a hand basket.

And there you have it.

Business vs.IT

The users want their data.  They want it now.

They will perform their own analytics and reporting.

So what is the new role of IT?

Who knows?

I believe it will change over time.

IT is no longer the gate keeper of the castle, refusing to let down the gate for users to enter.

The users have stormed the castle and kicked out the IT folks.

What will they decide to do?

Will they become the pigs in the book Animal Farm, that begin to walk on their hind legs such as the humans did?

Will they become the authoritarian business rule / data keepers from the rest of the org?

Will they become too lazy or not completely understand what they are doing and decide to outsource it?

Sure IT was bloated, burocratic, slow, too many hurdles to jump through.

Will the business users do a better job of managing their data.

The business should be careful for what they ask for.  You want complete control of your data.  You better be prepared for all that entails.

Obviously they're going to have to build some type of structure to backup, archive, store the data.

And tune it for fast queries.

And document it.

And mash it with other data sets.

And cleanse it.

Hmm.  That seems like a lot of work.

Perhaps there was a team they could hire to do all this, hmm, maybe the IT department could help.

And so the struggle to integrate the business units and IT will continue to battle for control.

Like I've said in prior posts, there needs to be a new department, not owned by IT nor the Business, which orchestrates the entire data infrastructure of the organization.

That will, once and for all, unite the units, oversee everything from centralized point and be governed by whatever rules need to be in place: HIPPA, Sox, Data Governance, Change Management, etc.

A new business unit that handles nothing but data.

That's the future!

Hiring Process is Outdated

Changing jobs is definitely a life changer.

Luckily I found a job I like.

Challenging, nice people, good technology, good pay.

I'm not sure how other people change jobs.

Do they methodically plan out their approach?

Decided to change jobs.

Research what company's exist in the desired market.

Send  resumes, get interviews, receive a bunch of offers.

Then negotiate a signing bonus, extended vacation, company car, etc.

Maybe it was like that 30 years ago.

From my job changing history, I blast my resume to any company that has a position for which I'm qualified.

I get a few interviews and accept one without much information.

Then I get there, and shortly begin to dislike it, and wonder why I accepted such a position.

It seems to me the hiring company's don't reveal enough information during the process.

To me, locally anyway, it's as if the interview process is one way, they are sizing up the applicant and if and only if they decide you are the right candidate, you are given an offer which has almost no wiggle room for modification and then you sign a non compete waiver and go in blindly.

It seems to me that the interviewee should be evaluating the hiring company as much as they are evaluating the applicant.

Let's be honest, if the hiring people don't disclose enough info about the position, culture, etc. the applicant isn't going to stick around and you both just wasted time and effort.

It seems to me the entire hiring process needs to be re-evaluated and modified so it's a win-win for both parties.

The entire model seems outdated and needs an overhaul.

1. Where do you want to be in 5 years?  Hopefully not living on the streets because your company went bankrupt or shipped my job overseas.

2. What's your greatest strength?  I seem to know what questions will be asked on the interview and have the ability to answer them in a sincere believable fashion to get you to like me.

3. Tell me an instance where you didn't get along with someone and how did you handle it?  My previous co-worker would take credit for my work over and over so I would put bugs in his code when he went on vacation so he'd have to answer his cell phone and mess up his vacation.

I mean seriously, most questions are standard and some people can fool anybody and obviously you are not going to respond like mentioned above.

It's just an act on both sides where everyone says the politically correct answer, never revealing any truth.

I would say the biggest factor in our society for job changing is insurance.  Because everyone needs insurance.  And when you switch jobs, you may have a gap in time where you are not insured.  Unless you are a millionaire and can afford the most ridiculousness priced 'Cobra" insurance.

So it seems to me if there was an insurance that you could somehow take with you between jobs, it would sure make job changing easier.

That would be one change anyway.

The other thing would be for the applicant to do research on the perspective employer with LinkedIn and sights like Glassdoor.  If the hiring company is going to do background checks on you, you should also be able to background check them.

Because job changing is a life changer.  And you don't want to jump jobs to get to a new place and hate it. Looks bad on the resume and it's not easy to go to your next interview and explain the short stint.

Have you ever gone to an interview and told them why you left your last job: "I fired my last employer as they did not respond to my recommendations, failed to meet my expectations repeatedly over time and I was forced to let them go, unfortunately."

That will get you a first class ticket out the door on your tuckus.

Why can't we fire our employer?  Because the cards are stacked in the Corporation's favor.  You have the privileged, if you are deemed lucky enough, to have the benefit to serve your time for such gracious host, until they seem fit to get rid of you, but not the other way around.

I'm just throwing ideas out there.  I'm not unhappy at my job.  Just saying there's room for improvement on the entire hiring process as the steps we do today are the same they did 50 years ago more or less.

Time for a change!


Table Variables

I recently discovered a report that ran for over 7 hours on a regular basis.

So I opened up the code.

Turns out the database is read only, replicated, no permissions to add indexes.

So I reviewed the Stored Procedure.

First I consolidated the code as 1 Sproc called another.

Then formatted the code so it was legible.

Then my co-worker and I reviewed the query for table scans.

Sure enough, he suggested to use Tables Variables instead of direct calls to the database.

So I did that, in seven places, and add Clustered indexes.

The thing about it these reside in memory as opposed to temp tables which get written to disk.

I also added a few indexes to the existing #Temp tables, as well as cleaned up the code.

When the new code ran, I was expecting it to run for a while.

When results returned in a few minutes I was real surprised.

So next step will be to validate the data and push new code to production.

I believe the code is getting cached to the SSRS server, however, not for long.

Soon the user will be able to query the replicated data within minutes instead of waiting for the report to refresh over night.

Another way speed up the query is to use Derived Tables, which embeds the temp table in actual code within the  SQL statement, I've seen good results with this before.

And there you have it.


BI User Group Guest Speaker - me!

I was all excited to go tonight to the Tampa Bay Business Intelligence Users Group meeting.

Around 1:30pm the phone rings, Jose Chinchilla, president of the group.

Asks what I'm going t speak about tonight.


The presenter canceled at the last minute.

I agreed to speak and would find something to talk about.

I had a 2-3pm meeting so I didn't have much time to prepare.

I found a few good blog posts from my site and starting to create a Power Point demo.

Then found a site to upload to:  http://www.slideshare.net/jbloombi/tampa-microsoft-bi-user-group-july-9-2012

I read through it a few times to get out the spelling errors.

And that was that.

Arrived at the meeting before 6pm.

I thought I'd be presenting for about a half hour, then found out I'd have the entire meeting time.

Luckily I brought my new Samsung device which connected directly to the projector, and I brought my MiFi  device for Internet connection anywhere, as there was no internet connection in the room.

So Jose introduced me, I gave my standard Anthropology degree should be unemployed joke, and then spoke for over an hour.

I went through each of the slides, just to get a feel for what the title was about, and ad-libbed most everything.

I stopped and asked for questions along the way wasn't sure if I was rushing through it.

A lot of good discussion overall and feedback & questions.

I then thanked Jose and said he'd be raffling off a car tonight so get your raffle tickets in early, a BMW I think.

Overall, this was my first professional speaking engagement, didn't have time to get nervous.

Some good feedback overall.

I'd be willing to speak again...


Self Service Train

From my experience report viewers no longer want to view pre-canned reports, except maybe for state reporting and such.

They want to interact with the data.

They want to pick and choose dimensions and measures.

What if I slice by this field?

And what if I add this date range measure?

And how come this number doesn't look right?

Can you add these new fields to the data set? 

And that's where IT comes in.

Set up the infrastructure, understand the data to create Models, maintain user permissions and such.

IT still has a role in the data, except the users want self service to take data on the go, mobile, etc.

Data is evolving, traditional reports have a value, but the self service train is taking off.

Get on board or get left behind.


Data Whisperer

A Data Whisperer can handle any customer with their data needs.

No request too difficult.

Mashing data from distinct sources.

Giving the ability to pivot on a dime.

And push the info to a mobile device.

A data whisperer can answer your questions so you can provide insight.

And allow you to manage your business.

A Data Whisperer may be a rogue developer embedded within a business unit.

It may be a data scientist or a report writer.

Or it may be your data architect.

Who answers the questions that only the wind knows.

Every department in every organization could use their own private data whisperer.

Don't you think?


Evolution of Reporting

Reports are nothing new.

They've been around forever and a day.

Back on the Mainframe / AS400, people were writing batch reports for over 30 years, some are still doing it.

Then along came the Relational Database with the definition being:  "is a collection of data items organized as a set of formally described tables from which data can be accessed easily"

So then there were 4th generational languages which allow the developer to easily connect to a database, apply the necessary joins, without having know much about SQL.

Then came the Data Warehouse, which stores data for reporting and analysis.  Data was denormalized for quick access with slicing and dicing the dimensions and measures for multi level dissection and drill down using a language called MDX, which is a query language for OLAP databases.

Because of time delays and complexity involved, users began to demand faster access to their data, delivered in a variety of formats, for consumption in near real time.

Which gave rise to a new breed of reporting such as Tableau, QlikView and Microsoft version Power Pivot.  These new tools allow a business user, not necessarily IT people, to rapidly connect to a data source, pull data in, join the data, see their data, then deploy to other users, Power Point or web interfaces with permissions almost instantly.

Users can create stunning visualizations including Dashboards which drill down, drill through, color graphics, dynamically adjust based on Slicers and Pivots.  With other tools such as Performance Point which usually require a developer, Balanced Scorecards are now common place with Key Performance Indicators KPI to quickly draw attention visually to points of interest on the Scorecard.

Users don't always stay put.  And by that they sometimes are on the golf course, at the air port, at home, sometimes connected to the VPN other times not.  Reports still need to handle this feature.  A lot of Reporting solutions offer some type of Mobile access, through portals such as Microsoft SharePoint.

Next step in the evolution of Reporting is a sub-set of Big Data, called Hadoop.  This type of system uses thousands of low end servers to host enormous amounts of data, mostly un-structured, as in non-relational.  Basically a central controller replicates 3 copies of every item to a variety of servers, which self regenerate if corrupt, and the data gets widdled down to key, element pairs, which can then be queries on in batch queries.  The concept is quite powerful and has been around for some time, although because of the reduction in costs of hardware and software and developer time, it has entered the mainstream.

Another intersection of Reporting and Data and Big Data is the advent of Artificial Intelligence.  The goal of AI is to simulate a human cognitive brain, that understands vast amounts of information, can query in real time, can understand human speech and thought patterns, as well as fragmented speech that is regional or specific to circumstances, etc.  If you've seen Space Odyssey series, you may be familiar with the computer character HAL, move each digit 1 letter (IBM), although the real version IBMWatson seems to be a lot friendlier and can win on Jeopardy.

So as you can see, Reporting has been here for a while, has changed somewhat over time, and will continue to grow and expand in the future.

BI Lifecycle

In BI there are a few particular check points to focus on.

Data Sources - where does the data originate?: servers; databases; flat files; what frequency is the data collected? in real time?, who owns each data source? what credentials needed to connect with, etc.

ETL - what business rules are applied to the data? what are the keys to join on? etc.

Data Model - how is the data put together in a structured fashion to allow easy consumption by multiple tools? etc.

Distribution - how will users consume the data? what application types? what security needed? what data can user see? mobile / portable / disconnected data? self service or canned reports?  Dashboards / Scorecards / KPI's needed? etc.

This should get you thinking of the full life cycle for a BI project.

The world has changed.  No longer IT sits in the castle, lets the gate down for the User to enter, submit request and come back in a month for a product that may or may not be accurate, limited flexibility and might not match the other reports in the organization.

Users want data, they want it now, it better be correct and you better be able to provide it in half a dozen formats / sources / self service options.

That's if you want to stay competitive.


Document Processes

I've been working on some new stuff lately.

I was tasked with comparing data between Excel and PDF looking for discrepancies.

Afterword, I was to import the worksheets into a SQL database, then pivot the data.

It was a good first project, got my feet wet with learning some of the underlying business rules that are the foundation of a software company.

From there, I was tasked with learning the Cube process, all the disparate data sources, where that data originates, how it gets consolidated in the SQL Agent jobs, SSIS packages, table structures, ETL, Stored Procedures, Views, etc.

So I began to document the processes, one of my favorite things to do.  This allows me to see the opportunities for improvement.  For example, tracking down the source code and consolidating the projects and moving into Version Control took up a few hours of my time today.

It's funny, when I was a child, my father would take me to the junk yard.  We'd walk around looking for cool stuff, parts for a washing machine, we even assembled my bike from old parts we found.  Even back then, I would try to straighten up the junk yard, move things around in a more orderly fashion.  And one day I got spoken to by the guard to stop trying to clean up the junk yard.  My father thought that was hilarious.

Except I've spent my life cleaning up other people's code, very similar.  Trying to create order out of chaos, my specialty.

I really like the atmosphere where I'm at, a lot of smart people, willing to help, work hard but have fun environment, jeans & t-shirts, free coffee, group lunch on Thursdays, and my boss is really cool.  And so is the technology, business model, location, parking, I really hit the jackpot on this one.

So today I was told to learn the BI ecosystem from data gathering to user consumption and all in between.  Because soon they will expose me to the customer base and I will be the point person for all data requests for the BI team, while the architects continue to build new data models for consumption / support.

So I need to get up to speed pronto.  What a great challenge!

New Technology

So yesterday we bought new I Phones.  Rid of the BlackBerry's.

They get you on the two year contracts.

So I also bought a hot spot which works anywhere, so I can connect vial the laptop.

As well as the new Samsung tablet device.  Because Verizon doesn't support this device so they said.

And I bought a new BlueTooth keyboard / mouse because the keypad is not sufficient for extended use.

So all in all, it was an expensive day at the phone store.

I now have a PC, laptop, IPhone and Samsung tablet.  And a Dell Printer.

I also bought a monitor extender a few months ago.  And picked up a Dell keyboard for the laptop as well.

I buy in spurts, long periods of drought, then wham, I buy everything in the store.

This new technology should keep me busy for a while.


Data is the Blood of an Org

Organizations store data from a variety of sources.
Real time data, legacy data, mashed data, Data Warehouse data, External Data, Customer Data, Vendor Data, etc.
And for a long time, that data has stayed within the confines of IT.
It has remained 'Potential Energy'.
What really needs to happen is to circulate all that data throughout the organization.
Like getting the blood flow to all parts of the body.
So it can function properly.
And get the BI people to assess the patient / org.
So the question is, does your org have any blood clots? 
Any places the data does not flow?
That would be the first place to investigate for better processes.
A healthy org needs fresh data..

Like the body needs fresh oxygen pumped through the veins & arteries.