Project Management 3 Adjustable Pillars

You have:

  1. Time
  2. Resources ($)
  3. Functionality / Quality
Add Time - extend the project deadline, subtract and you must adjust items 2 or 3 or both.
Resources - add money, people, software to speed up a project, however at some point there's diminishing returns.
Functionality - remove pieces to finish on time, adding functionality will extend the project.

Add time or resources.  Or subtract functionality.

There's really no other way around it.


Resurrected #DataScience Project

The past few days I've been resurrecting the project I worked on 4th quarter 2012.

The project was to determine ROI.

However, in order to do so, we must have an audit trail from initial click on Google ad-word to money deposited in the bank.

And due to the current process, it's possible for the chain to get broken along the way.

As we pass off leads to resellers and distributors.

So my code looks for matches that otherwise would be unmatched.

And last year I figured we could match only 32%.

And the project fizzled out.

However, this time around, I'm looking at a specific subset of products.

And the number shot up to 47%.

However, breaking it out by year the number climbed to 63%.

So I had a meeting with out business analyst to have her verify my data.

I output all the records about 70k of them,  placed in Excel spreadsheet and sent it over.

She will log onto SalesForce.com and validate the data with Accounts, Opportunities and Leads.

And if that's good, then we'll have a master data set in which to build on.

That will be awesome.  The logic to gather this data resides in about 25 t-sql files.

I'd like to port the process over to SSIS to automate at some point.

This was my first Data Scientist project.

And so it goes!


#TampaBay Next Hotbed for IT

When I moved to Tampa Florida in 1981 or so, first thing I said getting off the plane is, "where's the mountains?  Why's it so flat?".

Next was the summer heat, holy Toledo talk about humidity.

Next was the suburban sprawl.

After school and college I relocated back here from Atlanta.

I discovered the lack of quality high paying jobs.

And over the years other people have noticed the same thing.

What's up with that?

Sure we have at least two pawn stores on every corner, as well as check cashing stores.

And we are up there in number of strip clubs per square mile.

We've got plenty of landscaping jobs, hotel jobs, fast food jobs.

The economy lit up like a fire during the housing boom.

Money was rolling in hand over fist.

Anything related to housing was guaranteed income.

And since the bust, we are the foreclosure capital of the nation, or pretty close.

And since the dust has cleared, we still have pawn stores, check cashing and strip clubs.

And a lack of high paying quality jobs.

We were labeled the next up and coming city in the 1980's.

Back when the Bucs couldn't give a way free tickets.

Well, they've won a super bowl and the Rays are contenders.

So where are the high paying job?

Well, we've still got some good ole boy company's that pass the biz down from generation to generation, where they work you hard and pay you less.

And we've got your typical banks, insurance, sprinkled in with some quality IT software company's.

But most company's sell something other than IT.

And I think we can change that.  We can integrate the culture with smart people who want to settle down here, start family's and create a culture of intellect.

Combine with Orlando and Gainesville to expand the existing High Tech Corridor.

We are ripe for growth and by growth I'm talking about high paying quality jobs that will attract top talent.

We can make it happen.

Just need to shed the legacy of tourist, retirement homes and manual labor jobs.

There are no limitations.

And so it goes!

ITIL Process Drove Wedge Between IT and Biz

Back in the old days, the IT department serviced the customer.

Business customer Bob called up their buddy Joe in the IT department.

Hey Joe, it would be great if the XYZ app did this this and this.

Joe the Programmer said, sure Bob, anything for you.

Should have it in a jiffy.

Happy customer and happy programmer.

Then along came the rules people who said, IT is not supporting the customer in the correct way.

We need to build barriers between IT and the Biz.

They should never have direct contact.

Everything must go in a ticket.

Thus, systems like the ITIL process were born.

Now, Bob the customer is not allowed much contact with the programmer.

He must decide what changes he needs, submit them into the ticket abyss and sit and wait.

The ticket goes into the queue, gets assigned, prioritized, spec gathering, design, build, test, deploy.

Customer gets to sit and wait while the IT department works their production line in queue format.

And while the programmer works the tickets, other production issues bump in priority.

So the customer gets to sit some more.

And let me tell you about paperwork.

It takes more time and effort to get the correct paperwork, authorization, signatures, bow to the mighty change management people, kiss their ring appropriately to show you are not worthy, and pray to God they approve your ticket.

Then release, and the code is nothing what the customer wanted.

However, we now have a documented audit trail to show just how ineffective the process is.

Cover the butts of the higher ups.

Sure we are now slower, inefficient and produce lesser quality code, but at least we now have auditable processes.

And people wonder what caused the rift between IT and the business.

Now you know.


#SAP #CrystalReports Slow SubReports

The part time project I'm working on has some Crystal Reports 2011 pointing to a Business Warehouse.

The cube has hundreds of measures going across.

So returning the entire set in one query causes an error.

So the full time developer broke out the measures into chunks.

Basically a single report with 10 SubReports.

And it runs slow, too slow for comfort.

So that's why they brought me in.

To streamline the reports so the customer will approve before going live with the big project.

So I went through my checklist of things to look for:


So I verified the report and sub-reports and it was still running slow.

I then applied the following techniques:

  • Right Align Subreports so they fit on the main report page
  • In each of the Subreports as well as the main, I condenses white space wherever possible.  
  • Inserted New Section for each row.
  • Then Fit Sections for every section on the report.
  • Removed every single "Keep Together" checkbox in report and all subreports.
  • Added PageNofM in order to render through all the subreports upfront.
  • Changed the report from Portrait to Landscape 11x8.5.
  • Deleted all Unused Sections of the report.
  • Unchecked "Suppress Blank Sections" for every section.
  • Unchecked Select Distinct.
  • Unchecked Verify on 1st Refresh.
  • Unchecked Verify Stored Procedure on 1st Refresh.
  • Unchecked Verify Database Driver upon change.
  • Checked the Perform Grouping on Server checkbox.
  • Checked the Select Distinct Data for Browsing.
  • Checked the Perform QueryAsynchronously checkbox.
  • Checked the Use Indexes on Server for Speed.
  • Removed Unused Formula fields from sub-reports.

So far report run time has dropped from 8-10 minutes down to 4:27 minutes. Shaved off 53% off the baseline runtime.

Not too shabby, however, there's a ways to go!


4 Cool Features #SSRS #MSBI #SAP #CrystalReports

This morning I posted a blog about 2 features I liked about SAP Crystal Reports:


Now I'd like to post a blog on four features I like about SSRS.

1. Dynamic Parameters - when running a report, user can select a drop down parameter and based on his or her selection, you can dynamically populate the list of other parameters.  This is way cool.  With that said, it would be nice if Microsoft can fix the issue when allowing multiple parameter value selection from a drop down without having to add extra code and then having to use a Split function in the SQL query / Stored Procedure.  Still, dynamic parameters rock!

2. Ability to have Multiple Data sets from multiple data sources within a single report without the need for Sub-Reports.  When I first saw this I thought to myself, that's one feature I've missed using Crystal Reports for the past 15 years.  For this reason alone, I think SSRS is a better product.

3. Ability to create SSRS reports within the BIDS environment (Solution/Project) makes coding straight forward for Microsoft developers who don't need to learn an entirely new IDE just for reporting.

4. Ability to run Data Driven Subscriptions is another award winning feature over Crystal Reports.  To schedule a report to run on specific frequency and have the report driven by a query that returns rows and have the server generate one report per row is just fantastic.

That's my top 4 list off the top of my head.

However, if you're going to be a Business Intelligence developer, you can't really limit your toolset to just one vendor so learn as much as you can.  Each product has strengths and weakness'.

And you can't pick which technology your client will be using.

And so it goes!

2 Cool Features #SAP #CrystalReports

Working with Crystal Reports since 1996 version 5, I've had lots of exposure to it's functionality.

I've also worked with Microsoft SQL Server Reports Services (SSRS) for some time now.

I wanted to point out two advantages that I've seen with Crystal Reports.

1. Report Bursting Indexes - is a feature that can speed up your report by identifying key fields, typically found in the Select Expert.

You can find this feature under the Report menu item --> Report Bursting Indexes.

Here you can select specific field(s) by selecting the field on the left and pressing the > button to move specific field(s).

Another way is to click the "Auto" button which will identify the Field(s) in the Select Expert and add them automatically.

You must run / re-run the report.

2. Performance Information - can be found under the Report --> Performance Information menu item.  After you run a report, select this feature to find enormous amounts of detailed information about your report:

You can also select the "Save..." button to create a text file:


Report Definition

 Number of Database Fields:   11  
 UFLs in use:   None  
 Number of Subreport Objects:   1  
 Page N of M Used:   No  
 File Format Schema:   14.0.0  

Saved Data

 Number of Data Sources:   1  
 Unicode Saved Data:   Yes  
 Total Number of Records:   6002  
 Selected Number of Records:   6002  
 Recurring Database Record Length:   1 KB  
 Recurring Formula Record Length:   0 bytes  
 Constant Formula Record Length:   0 bytes  
 Size of Saved Records:   9 MB  
 Size of Saved Memo Fields:   0 bytes  


 Grouping Done on Database Server:   No  
 Sorting Done on Database Server:   Yes  
 Record Selection Done on Database Server:   Yes  
 Require Two Passes:   No  
 Require the Total Page Count:   No  
 Number of Nodes in Group Tree:   1  
 Number of Summary Values:   0  
 Size of Saved Group Tree:   98 bytes  
 Built the Group Tree in Progress:   No  

Latest Report Changes

 Recurring Database Fields:   Changed  
 Record Selection Formula:   Changed  
 Print Time Formulas:   Changed  
 Database Table Linking:   Changed  

Performance Timing

 Open Document:   0 ms  
 Run the Database Query:   1853 ms  
 Read Database Records:   35 ms  
 Number of pages formatted:   1  
 Average time to format a page:   385 ms  
 Number of page starts generated:   97  
 Average time to generate a page start:   59 ms  


Report Definition

 Number of Database Fields:   7  
 UFLs in use:   None  
 Page N of M Used:   No  
 File Format Schema:   14.0.0  

Saved Data

 Number of Data Sources:   1  
 Unicode Saved Data:   Yes  
 Total Number of Records:   0  
 Selected Number of Records:   0  
 Recurring Database Record Length:   1 KB  
 Recurring Formula Record Length:   0 bytes  
 Constant Formula Record Length:   0 bytes  
 Size of Saved Records:   0 bytes  
 Size of Saved Memo Fields:   0 bytes  


 Grouping Done on Database Server:   No  
 Sorting Done on Database Server:   Yes  
 Record Selection Done on Database Server:   Yes  
 Require Two Passes:   No  
 Require the Total Page Count:   No  
 Number of Nodes in Group Tree:   0    For all instances
 Number of Summary Values:   0    For all instances
 Size of Saved Group Tree:   82 bytes  
 Built the Group Tree in Progress:   No  

Latest Report Changes

 Recurring Database Fields:   Changed  
 Print Time Formulas:   Changed  
 Database Table Linking:   Changed  

Performance Timing

 Run the Database Query:   367 ms   For all instances
 Read Database Records:   0 ms   For all instances
 Format First Page:   2 ms   For all instances
 Number of pages formatted:   1    For all instances
 Average time to format a page:   2 ms   For all instances
 Number of subreport instances formatted:   1  
 Time to format all subreport instances:   381 ms  

These two cool features are great for developing reports and can only be found in Crystal Reports from my recollection.

And so it goes!

Project Update #SAP #CrystalReports

Last night I was at my part time contract 4:30pm.

Was able to log onto the pc with credentials and run the Crystal Report.

I took a snapshot of the metrics using the cool feature Report | Performance Information feature:

This gives you the run times for main and all sub-reports as well as other vital statistics.

I saved the file off as a "Baseline" so when I figure out what's causing the slowness I can compare before and after to present to the Client.

So looking at the report, it appears to have about 28 parameters or variables from the Business Warehouse query.

So every time I run the report, I have to click "Null" value for 25 of the 28 params.  Seems like there could be an easier way to handle this.

So after the params are entered, the report returns data within a minute on page one.

Then by clicking to the end of report, it must be calling the other 9 sub-reports because it doesn't return records for about 8-10 minutes.

So I investigated the other 9 sub-reports.  It appears that although they have the exact same params and they are linking the subreports, they are not linking the fields from main to sub-reports.

That's as far as I got as I left at 6pm and headed home to try the remote access.

Unlike my other contracts, this one requires stricter security.

First log in, then enter credentials including the ID from an electronic device they provided which changes numbers every few seconds.  I managed to get passed this and clicked on Remote Desktop Connection, where I was foiled by lack of Terminal Server access.

So I contacted my boss and she will get me access today.

Tonight I'd like to remove 8 of the 9 sub-reports, set a baseline, then experiment with the Linking options between report and sub-report see if I can speed it up.  If successful will apply methodology to remaining sub-reports.

Also would like to see the drivers used in the report I researched OLAP is not the preferred and should use SAP BW drivers.

What the report does is they have about 100 metrics in a cube and the report barfs trying to return all 100.

So they split the queries up into chunks, about 10 fields per sub-reports consisting of 10 sub-reports.

Crystal Reports is calling the database 10 times for 10 different queries although it's only returning 1 row split into 10 chunks.

I will investigate further.

And so it goes!


New #CrystalReports P/T Contract #SAP

I started my new contract tonight.

Arrived 5pm, got badge from security guard, met my new boss.

She and one of the developers and I met in a conference room.

Discussed the scope of the project.

They have some reports that are running slow.

And the Crystal Report has 10 sub-reports.

I will be able to work remote or at the office.

So we went to my cubicle, I logged in, except my password didn't take.

Call to the help desk got me working in a few minutes.

We logged on to Crystal Reports via the shared drive.

I reset my password in BOBJ.

And the report did not render, something to do with my roles probably.

So the full timer logged in, ran the report, ran in under a minute.

Then paged to the end the report just sat there, thinking for 10 minutes.

Thus the problem.

In addition, they logged into Excel and ran the query and it returned instantly.

So the problem is the Crystal Report.

My first task tomorrow will be to investigate.

Look at the 10 sub-reports, how they're linked, the type of sub-report (embedded or on-demand) and then check the drivers.

After that I'll start supressing the sub-reports, all of them and then add each one back one at a time.

See the response time.

And document each step to present back to the client.

They said if there's time, they have another project I may work on.

The environment is nice, cubicle, with mixture of high and low walls, so you can see the person next to you.

And the center was lined with candy and snacks, very impressive.

So after an hour tonight just to get acclimated, I'll work again tomorrow 5pm.

Get some troubleshooting in, look for the glitch(es).

And so it goes!


Active Directory Employee Manager Relationship

Yesterday I was able to extract the data from Active Directory into a SQL-Server database / table.

Today I modified the SSIS job to include the employees Manager.

This seems to have stuck a nerve.

I created an SSRS report to expose the data.

And it was apparent that the data needs to be updated.

And that led to who's going to do what.

Which spawned email threads, conversations, project discussions.

Which led to AD integration from the HR system API discussion.

And a co-worker is already using the employee-manager feature - and I have a report that will use it as well.

Whoa Nellie!

Pandora's box has been opened.

IT Is All About Creativity

Where do we want to go today?

That's the question that got me into IT in the first place.

There are so many mindless jobs out there where the repetitive nature requires little thinking.

Little room for creativity.

Monotonous routine.

The kiss of death.

I'd prefer to journey forth into the unknown.

Without a map.

Something different every day.


Free form.

IT gives us the opportunity for all that and more.

To play within the boundaries and of structure with occasional visits outside the box.

For me, it's the only way to fly!


Age of Enlightenment - Data Revolution

We are in the midst of a Data Revolution.

A modern day Age of Enlightenment.

People are getting deep into the data.

Trying to find the magical insight that will promote their business to become huge success.

Data has been around for a long time.

However, only recently has data exploded and cost reduced.

Everyone's on board to ride this wave of analysis.

And for me, being there in the mid 1990's writing SQL and providing reports, it seemed very few others were doing reports at that time.

The number of Business Intelligence developers have sky rocketed.

There's more competition, however, there's more opportunity.

And data analysis has grown as well as splintered.

So many facets of data and ways to slice, dice, visualize, ETL, Dashboards, Scorecards, quite mind boggling.

Now add in Big Data, Unstructured Data, Data stored in Memory.

There's no way to keep up.

I say we are going through a Data Renaissance never seen before.

So sit down, hold on and get ready for the ride of your life.

Active Directory Data Stored in Database

Today I was asked to get some reports to work based on Active Directory.

Apparently they already tried to get this to work years ago.

And they provided some source code in c# web app.

So I created a c# project and copied the code to a new class.

It was taking too long.

So I went to the archive of my blog and located my old posting:


Changed a few things and sure enough, got data in the database, pulled from Active Directory using SSIS.

Sent out the email to the group and it was well received.

Bosses chimed in and everything.

So ended the day on a good note.

It's good that this blog helped solve a real IT problem.

And so it goes!


Interviews Are Sales Pitches

Are you a good sales person?

If so, you've got a better chance of getting a job.

Because in order to get a job you must attend a series of Interviews.

And in the Interview, you are selling a product.

And that product is you.

Sure this baby will hum at 120 miles per hour.

Take sharp turns while downshifting into 3rd.

Sure it get's good gas mileage.

And low maintenance costs.

Now, did I get the job?

So you must learn the product.

And by that you must know your strengths and weaknesses.

And you must polish, tune and present the merchandise in the best possible light.

And know your customer on what he / she is looking to purchase.

So an Interview is really a Sales Pitch.

Get them to buy your product / service.

And so it goes!

Opposite of Agile Development

Have you ever been in an environment where it's utter chaos.

People are working as fast as they can to meet the customer's demands.

However, the customer doesn't provide design specs up front.

They give them to you as you go along.

So you are constantly changing things.

Which gets you behind in your development.

Thus, people are working as fast as they can to meet the customer's demands.

And guess what.  This is the breeding ground for mistakes and bugs in the code.

So what does that mean?  Work even faster.

Now, take it from the top.

Throw the project over the fence, don't give meaningful business logic, throw in tight deadlines, watch  developer jump through hoops, creating a bug filled application that will need to be re-written.




Dancing with Data

Have you ever seen the Kevin Costner movie, "Dancing with Wolves".

The story takes place in the 1800's where a war hero is sent to the wild west on a mission.

Apparently he's forgotten about and left to survive amongst the unknown.

He befriends a Native American tribe, who he thought were the enemy.

He integrates into their culture and leaves his past behind.

Have you ever worked on a project that seemed like the best thing ever.

You're sent out into the wilderness alone to solve the biggest problem.

Then somewhere along the way, you realize you've been forgotten about.

Your own version of "Dancing with Data".

Working in isolation, nobody knows what you're working on.

It's just you and the data.

You get lost in the rows and columns, swimming amongst the data.

Eventually, the home office realizes your mission and circles back to find a status update.

Except they don't quite understand it and although they say it's important, you sense the lack of.

And people are lined up to take your insights and brand them as their own.

To take credit for your hard work.

You have no choice but to hand over the jewels.

Was it all for nothing?

Learn to Prioritize Emergency's vs Everyday Work

Not all problems are created equally.

Sometimes I've found myself jumping from one emergency to the next to the next.

With no time to come up for air.

So over time, I've realized this is not the best way to solve problems.

Working for the government, you realize the pace is not always rush rush.

In fact, it can be slow slow.

And problems get solved, eventually.

And trying to fix problems as fast as you can is actually frowned upon.

Sure if the boat's sinking, it's probably in the best interest to patch the leak.

However, I've seen situations where people let problems exists, for different reasons.

Sometimes it's a political strategy, let someone look bad even though it's a simple fix that could have been done sooner than later.

Sometimes the structure of priorities forces you to work on certain issues first.

For example, a state mandate or a request from the CEO or perhaps an auditor would trump a middle manager issue with a report.

So perhaps it's better to work at a good pace, prioritize work and be flexible to change on a dime if the need arises.

Because as the childhood story goes, the tortoise wins the race.

12 Reasons Your Report Is Incorrect

Top 12 Reasons your reports are wrong!

  1. Bad Data
  2. Out of Date Data
  3. Multiple Reports don't Match
  4. Underlying Logic Flaw in SQL
  5. ETL has Logic Flaw
  6. Business Rules are Incorrect
  7. Mashing Data Causes Errors
  8. Duplicate or Missing Data
  9. Summary does not Match Details
  10. Dependent Data is Incorrect or Missing
  11. Report not Reflecting Upstream Business Rules Change
  12. Data Refresh Jobs not Refreshing


#SAP #CrystalReports Performance Tips

Per my previous posting, http://www.bloomconsultingbi.com/2013/02/crystal-reports-possibility.html I've been getting re-familiar with Crystal Reports, Business Objects Enterprise, XCelcius.

And in order to do that I posted a few blogs to install Crystal Reports 2011, http://www.bloomconsultingbi.com/2013/02/installing-sap-crystalreports-bi.html as well as installing Crystal Enterprise 2011, http://www.bloomconsultingbi.com/2013/02/installing-sap-crystal-server.html .

Much of Crystal Reports hasn't changed much over the years, however, much has.

And the changes appear to be good ones.

And the project I'm interviewing for has to do with increasing performance on Sub-Reports pointing to SAP Business Warehouse Accelerator, which basically takes cubes in a Universe and compresses them for optimal speed.

However, I've summarized a few tips from researching the internet and they are as follows:
  1. Driver - verify the correct one is used
  2. Creating the report - makes a difference if you File->New vs. Toolbar->New
  3. Saving the report - makes a difference if you File->New vs. Toolbar->New
  4. Configuring the Toolbar - set default to not use Query Driver
  5. Use BW Publishing to deploy reports - affects Params static vs. dynamic and show Hierarchy's
  6. Params - better to create variables in BW
  7. Verify DB, if underlying cube has changed
  8. Avoid PageNofM and Page#
  9. Avoid multiple data sources
  10. Schedule batch reports
  11. Sub-Reports - *** On-demand increases performance
  12. Sub-Reports - Linked vs. Unlinked - linked adds logic to Record Selection
  13. Sub-Reports - placement in main reports matters - avoid placing in details
  14. Performance Information - new feature displays stats on Main & Sub Reports
  15. Perform Grouping on Server
And some links: 

This list is basically a 'cheat sheet' for if I do get the project I'm interviewing for.

Although it's part time for 1-2 weeks, it would be a great challenge to identify the slowness of the sub-reports.

We'll see what happens...

and here's a posting on how to install Crystal Server...


Installing #SAP Crystal Server 2011

Today we are going to install Crystal Server for 2011 from SAP.


Click Free Trials upper right corner...


Plenty to Choose from here...

SAP Crystal Server 2011 will direct you here:


Now that we've installed Crystal Server 2011, time to take a spin around the block...

Login  to Central Management Console using "Administrator" and your password created during installation...

Version 2011 seems very familiar as I was the BOBJ Admin working for Pinellas County using 2008.

Installing #SAP #CrystalReports #BI

Today we are going to install Crystal Reports Developer Edition for 2011 from SAP.


Click Free Trials upper right corner...


Plenty to Choose from here...

SAP Crystal Reports 2011 will direct you here:


Download a 30 Day Trial

You will complete a form with your info - next check your email inbox for a trial key... unzip the download and begin the installation!

Check for updates and ready to go!

Goto your start menu, click on Crystal Reports folder..Crystal Reports Icon...

Now you can begin your journey with SAP Crystal Reports Developer by connecting to your Data Source of choice.

Hope this picture-blog helped!

My first install for Crystal Reports was Version 5 in 1996 - they actually had a side product called Crystal Info back then, which I worked with.  I heard pieces of that code still exists in the newer versions of BOBJ Enterprise.


and here's a link on how to install Crystal Server:


Have fun!


Crystal Reports Possibility?

I've been getting lots of recruiter activity lately, even though I'm not looking to change at this time.

The jobs haven't been that spectacular to make the move.

Except one of my old recruiters who placed me a few years ago contacted me.

She had a part time contract with a reputable local company.

To  troubleshoot some Crystal Reports which are about to be deployed.

Except they are running slow for some reason.

And they are looking for a gunslinger to assist.

And I used to be known for Crystal Reports specifically.

Until I made the leap to SSRS a few years ago.

So I was glad to get the phone call.

We've been in contact for over 10 years and one of the few recruiters that knows me well.

I haven't got the contract yet as I need to speak with the hiring manager.

It's only for a few weeks as they have a full timer on staff.

And if the person they contract with can identify the problem, they'll have the full timer take over.

Either way, these are the type of challenges I like.

It's remote contract after hours.

Either way, this team of recruiters are top notch!

And are a pleasure to work with.

The hourly rate is good too.

Maybe I'll get the opportunity.

And so it goes!


My First Data Scientist Project

I was tasked with an assignment to map records from one system to another to another.

To do so, I'd had to use fuzzy logic.

And the project was successful to some degree as I mapped 41% of the customers.

And after completed the project in 4 or 5 weeks, it's sat idle, for about 4 months now.

The topic appeared once or twice, which I gave an overview of the project findings.

Except interest went away as fast as it appeared.

So today, I have a meeting to discuss the project.

To explain how the data was mashed together and how the data flows through the system.

And it's my expectation that the info will be useful.

To know when a sales lead came in, from what system, how it flowed into SalesForce, which a Salesperson, reseller or distributor sold a product, to appearing in the Great Plains financial package.

In essence, closing the loop from initial click to money in the bank and all points in between.

I put a lot of time and effort into understanding the project from the business rules side, the technology side, and the analytic side.

And it's my understanding that's basically what a Data Scientist does.

I learned a lot and that's what counts.


Harvesting Data

Money doesn't grow on trees.

If it did, we'd all be Money Farmers.

Data doesn't grow on trees either, except you can harvest it.

Like a Data Farmer.

If you stop and think about it, some company's do nothing but harvest data.

Take Facebook as the prime example.

They are in the data gathering business.

Silly you, you thought it was a community interactive communicative sharing site.

The truth is you are doing their data entry at no cost.

Not only that, you are giving them your entire life story, pictures and all, at no charge.

You are giving away your most private thoughts, connections, locations to a data gathering company in exchange for nothing.

Once they have your data, they can map out your profile similar to a credit report.

They can determine your threat potential, your risk potential, if you drink beer, wine or milk, who you associate with, when and where.

You are unknowingly providing the juiciest of details to a company who may or my not have your best interest at heart.

The thing is, they already know so much about your patterns you'd be amazed.

Based off your electronic residual trail on the internet, your shopping patterns, just about everything is being tracked.

So in a sense, data does grow on trees.  And they are amassing mountains of your data.

You've been warned!  They are harvesting your data at this very moment.

Mine too!

Understanding Biz Infrastructure

Programming is the easy part.

Understanding the business infrastructure could take a lifetime.

You have to know the legacy of apps.

"Oh, Bob worked on that 5 years ago.  He didn't know what he was doing.  We should have re-written that app."

"How does that database get populated?  Oh, that app, it's replicated on a server in another country, nobody has rights to that server."

"Why does that job run at 11:34pm EST?  Because we have 3 other jobs that must run prior.  If any fail, there's no data for the Sales team in Europe."

Oh, I see, and where is all this info documented?  Echo.  Silence.  More echo.

Hence, the problem.

Undocumented processes, dependency's that nobody knows about, history of apps as to who and why things were done in certain ways.

And these are the days of our lives.

Reports MUST ALWAYS Match

The first rule of report writing is you don't talk about report writing.

The second rule of report writing is all your report totals must match other reports.

As in, reflect the same totals, sums, aggregates, etc.

Doesn't matter as much if the data is wrong, but they should at least match between reports.

Because managers love to point out the fact, in a big meeting, "I wanted to bring you to the attention that exhibit A report does not, in fact, match exhibit B report."

It's much harder for that same person to identify the incorrect piece of data when all the reports reflect the same results.

I'm not preaching to send out incorrect reports, I'm just saying you don't want to give low hanging fruit to people that can make you look bad.

Always ensure your reports are accurate before you send them out, but more importantly, make sure the totals match all the other reports!

Word to the wise!

First Step In Troubleshooting

A lot of times I get tickets.

Some report is not correct.

Fix it.

I always start by duplicating the problem.

If the problem is not repeatable, it sure makes it tough to troubleshoot.

Sometimes in trying to duplicate the error, the problem fixes itself.

Like today.

So I'll just close the ticket and see if the error reoccurs.

This could turn into a boom a rang ticket, where the user just sends it back, same issue.

If so, I'll troubleshoot again.

In the meantime, it's similar to a visit to the doctor - "hey doc, it hurts when I do this.  Well, don't do that."

And so it goes!


Uses For All That Data

To refine existing processes
To create new processes

Validate the Numbers
Used for Quality Assurance purposes
Identify Mistakes

Report the Numbers
MTD/YTD/ITD (inception)
Sales Figures

Legal Documents
Send reports to State and Fed
High School Transcripts

Variety of Outputs
Pivot Tables
Power Pivot

Data Discovery
Power Pivot
Power View

Dash Boards
Visual Representation of Data
Scorecards / Key Performance Indicators
Interactive Components / What if Scenarios (XCelcius)

Data Mining
Predictive Analysis
Complex Models
Statistical Trending

Big Data
Hadoop - Map Reduce
Columnar Tables
Desperate Data Sources
Hugemongous Databases

Converted c# AJAX Logic to SQL

So I've been working on a c# app, nothing big, to add quotas to a table in SQL-Server.

A team mate wrote the initial piece which has about 5 or 6 classes and connects to AD.

It basically reads in the Territory's / Regions from the SalesForce backup database.

User can assign dollar amount and currency for people's sales quotas.

I added a new c# web page with AJAX calls.

However, after working on it a bit, today I decided it would be easier to do it all in SQL.

So instead of having an entire web page which user must click one by one, I added a single button on the main default.aspx page.

It go out and grabs all the new entries in SF not in Quota table.  Then it loops twice, one time for New Business and one time for Renewals, and then inner loops 12 times, once per month, for a total of 24 rows inserted into the DB for each new Territory/Region.

I really tried to write some .net c# AJAX code, but it was just easier to use SQL.

And I removed the c# web page from the project, tested and pushed the single page Default.aspx c# app to production.

Then saved to Team Foundation Server and called it a day.

Fun stuff!