Darren Herbold

Business Intelligence and MDM (Master Data Management)

Darren Herbold — Mon, 30 Mar 2009 19:31:21 GMT

Every good data project specifically ones that involve some degree of enterprise reporting
or business intelligence propose a unique set of challenges. Most of those challenges relate
directly to the integrity of the current data architecture. Although it is next to impossible (or
very risky at a minimum) to modify a company’s existing underlining data architecture, there
are other solutions that will allow you to “start from scratch” for lack of a better phrase.

One way to breath new life into an old and outdated architecture, is to create an alternative
data schema that new applications or reporting clients can consume. Most of the time this
solution is best suited for enterprise reporting as the data will most likely not be updated in
a manner in which critical business application can rely on. That’s quite alright though, as
this technique is best utilized in a business intelligence project where the source data will
be read-only from a client perspective anyway.

So how would you facilitate a data-centric project in an environment that contains many
different data sources and subject areas that require a common data architecture? Enter
MDM: Master Data Management. MDM can be defined as a set of guidelines, procedures,
and toolsets to collect, store, clean, and maintain enterprise-wide data for the purposes
of a consistent “one version of the truth” data store that end-user will consume in some
manner.

Below are the tenants of MDM with a brief description of each. Some of these items
seem like plain common sense, but it also serves as a strict guideline to keep your
data-centric projects on track.

1. Identify sources of master data – Collect and documents all sources of data that
the organization consumes. These can be databases, excel files, flat files, etc..

2. Identify the producers and consumers of the master data – Indentify and document
the applications, vendors, and end-users involved in the production and consumption
of data within the organization.

3. Collect and analyze metadata about for your master data – Document the databases,
tables, columns, stored procedures, data types, and any other descriptive information
that is contained within your data sources.

4. Appoint data stewards – Appoint a group of individuals who know the source data at
a detailed level to help facilitate the movement of that data to the Master Data Model
while adhering to the defined business rules of the organization.

5. Implement a data-governance program and data-governance council – This group will
have the knowledge and authority to make the important decisions regarding the Master
Data including maintenance, content, and storage.

6. Develop the master-data model – This is the phase in which the actual data model will
be defined and developed. A well-seasoned data architect with a commanding knowledge
of the source data and business rules is required.

7. Choose a toolset – This should be the easy part. In my case, Microsoft SQL Server
is the natural choice. Analyze your company’s current toolset, as they may have already
spent good money on a current set of tools.

8. Design the infrastructure – Decide how this master-data model will be consumed in your
environment and design an appropriate system for the consumption of that data. This may
include Reporting Services, Sharepoint, etc..

9. Generate and test the master data – Take the time to generate real-world-ish data to test
your processes and schema. During this phase, you will ferret out any errors or inconsistencies.

10. Modify the producing and consuming systems – If you are unlucky enough to have to re-tool
     an existing application to utilize this new data source, this will be a huge undertaking. You
     will basically have to re-write your application in most scenarios. If you are using this master
     data as a reporting source, then you can start building new reports (which is why you went
     through this exercise in the first place!).

11. Implement the maintenance processes – Maintenance is never easy or fun, but if you used
     robust tools (like SQL Server Integration Services) to move the data, you are already ahead of
     the game. Just make sure the rules for updating your schema are robust and will withstand the
     test of time. A frequent audit of your processes will usually catch any anomalies.

Need BI consulting, training, or innovative BI tools?
Come see our offering: http://www.PragmaticWorks.com

Force.com IDE for SalesForce Development

Darren Herbold — Wed, 25 Mar 2009 02:08:22 GMT

Recently, I’ve been tasked with a requirement to apply field validation inside a SalesForce
form for my company Pragmatic Works. SalesForce does provide a mechanism for validating
form fields inside a form by creating what is called a “Validation Rule”. While these rules are
easy to create, their limitation is that they only validate the field that you create the rule for.
But what if you need to validate a field based on the input of another field? For instance,
if you want validate that the address fields are populated based on the selection of an activity
status, you need to move beyond the basics and apply a code-based solution.

The Solution

So how do you apply cross field validation in SalesForce? The answer is that you must
create a “Trigger”. Triggers in SalesForce act like Javascript validators and can also be
compared to Database triggers to some degree.

The Method

To write and deploy code in SalesForce, you must use their proprietary language called:
”APEX”. It’s an object-oriented-like language that has been compared to Javascript in
likeness to it’s syntax.

Force.com IDE

The IDE that you will use is Eclipse with a SaleForce twist. You will however, need to
install Eclipse. The link below will step you through all the steps necessary to install
Eclipse and connect to the Force.com API.

http://wiki.developerforce.com/index.php/Force.com_IDE_Installation_for_Eclipse_3.3.x

From there, you are on your own! Fear not though, because the IDE will also install all
the documentation and developer guides to help you transition into this new paradigm.
Very exciting indeed!

Need BI consulting, training, or innovative BI tools?
Come see our offering: http://www.PragmaticWorks.com

Loading Leads into SalesForce

Darren Herbold — Thu, 19 Mar 2009 15:38:49 GMT

As I sit here in the terminal of the airport in Atlanta, I’d like to share
a little knowledge I learned about SalesForce recently. My company
had a list of leads from a webinar series we did on Data Warehousing.
The goal was to import these leads into SalesForce so our sales guys
could start contacting those leads.

What you need first

Obviously this task involves SalesForce, so you need to have an account
with them. As with any SalesForce implementation, how you configure your
forms within the system also plays a key role. We configured a form to store
Sales Leads information in which we need to also load leads into.

SalesForce Requirements

In order to load information into SalesForce, you need to have the data in
a .csv format (comma delimited). Another requirement in SalesForce before
loading your data, is that if you are loading fields that will be mapped to a
drop-down box for example, the values must be currently defined inside
SalesForce. For example, if you plan to load Country codes in a drop-down
that was derived from a pre-existing SaelsForce Country code list, the new
country codes must exist before the mapping of lead data and SalesForce
controls can take place. I’ll expand much more on this requirement in future
blog posts.

Loading the Data

There’s a nifty tool that exists within SalesForce that allows for easy loading
of data. This tool is called “Web to Lead”. Clicking on this menu item for your
specific form will start the process. You will be prompted for the .csv file for
upload. SalesForce will then validate and profile your data to make sure derived
SalesForce controls contain the expected picklist fields for example. Then,
clicking Finish will upload the data. SalesForce will also send you a confirmation
email on the process.That’s it! Very cool indeed!!

Need BI consulting, training, or innovative BI tools?
Come see our offering: http://www.PragmaticWorks.com

New Dell XPS 1330

Darren Herbold — Sat, 14 Feb 2009 18:04:44 GMT

My wife and kids have finally (inadvertently) exposed the home computer
to all kinds of spyware and adware to the point of no return! As a result
I had to let them use my personal laptop (which is equally frightening).
I did however, set them up with a restricted account so that won’t happen
again. Still, I need my own laptop of course. After all, I’m a consultant!

Enter Dell:

The new box is a 13” Dell XPS 1330, with a 128GB Solid State Drive
and 6GB of RAM, and light as a feather. It’s perfect for jockeying in
and out of airports, hotels, and rental cars. It is soooo sweet!

Need BI consulting, training, or innovative BI tools?
Come see our offering: http://www.PragmaticWorks.com

SalesForce Resources

Darren Herbold — Fri, 13 Feb 2009 15:01:27 GMT

In continuance with my infatuation with SalesForce, I'd like to share some of my findings
as my involvement with this platform grows. While at my client, I went to speak to a developer
and noticed some interesting looking books on her shelf. They were unlike the typical ones
that someone who is familiar with the industry would expect. No. These ones were blue and
had a whole different look to them. With further inspection I found that they were SalesForce
books. Two in total, they were SalesForce development and administration books. That's when
I came to realize just how mature this SalesForce thing real is. When I picked up the books
the developer asked me if I was going to take the certification test. "Certification test?" I asked.
"Yeah, certification. They have certifications for this stuff.". My heart immediately filled with a
mixture of joy, intrigue, and excitement. How cool is that? A new toy to play with!

Anyway, in continuance with my infatuation with SalesForce, I'm posting some useful
links to some of their resources including the certification tracks.

Developer Guide
http://wiki.apexdevnet.com/index.php/Force_Platform_Developer_Guide

Workbook/tutorial
http://wiki.apexdevnet.com/index.php/Forcedotcomworkbook

Documentation/tutorials
http://wiki.apexdevnet.com/index.php/Documentation

Certification
http://www.salesforce.com/services-training/training_certification/certification/

Need BI consulting, training, or innovative BI tools?
Come see our offering: http://www.PragmaticWorks.com

SalesForce and Business Intelligence

Darren Herbold — Sat, 07 Feb 2009 19:47:40 GMT

Recently, I've had the opportunity to be involved in a SalesForce integration
at a client for which I've also been implementing an enterprise-wide MS BI solution.
The SalesForce offering is quite impressive. For example, they have an integrated
3rd party library of applications (for purchase) which extends off the SalesForce
platform that you can purchase to fit your specific business need called AppExchange.

You can find it here:
http://www.salesforce.com/appexchange

I will be writing more articles about SalesForce and Business Integration as the
project progresses. What I can say right now, is that this is a very powerful and
robust platform that I believe will change the way the software industry creates
and distributes software for some time to come.

More later...

Need BI consulting, training, or innovative BI tools?
Come see our offering: http://www.PragmaticWorks.com

Performance Point Eliminated!

Darren Herbold — Sun, 01 Feb 2009 14:05:28 GMT

Microsoft just recently announced that Performance Point is going to be scratched!
You read this correctly, it is going to be completely removed from the product list.
On a positive note, the M&A piece (Monitoring and Analytics) of the product will
be moved into the Sharepoint Server product (Enterprise edition). I'm relieved about
this announcement for a couple of reasons:

1. The M&A piece is just plain sweet. Serious dashboard eye-candy here, and very
easy to use.

2. The P&E (Planning and Estimation) piece was too difficult and cumbersome for
what it was trying to achieve.

I think this was a smart move on Microsoft's part. Not only will this eliminate an
obfuscated product from the BI offering, it will strengthen the Sharepoint product
which most companies have already (or should) in their IT arsenal. This creates
an interesting opportunity for BI developers to grow their skills in the Sharepoint
portal technologies and provide another value-add to their clients as well.

Need BI consulting, training, or innovative BI tools?
Come see our offering: http://www.PragmaticWorks.com

Microsoft BI and the Magic Quadrant

Darren Herbold — Thu, 15 Jan 2009 03:03:06 GMT

While perusing the net one afternoon, an interesting headline caught my eye. It appears that the
Microsoft's Business Intelligence offering has entered what is described as a "Magic Quadrant".
What is this Magic Quadrant?, I pondered. It sounded like something out of a Dungeons and Dragons
campaign. It turns out the "Magic Quadrant" is a performance measurement expressed as plots
on an X/Y axis developed by Gartner Research. The article (a lengthy one at that) titled:
Magic Quadrant for Data Warehouse Database Management Systems. is an exhaustive analysis of
the Data Warehouse software market measured against a stringently defined set of benchmarks.
The good news is that Microsoft's BI offering has scored in the "Leaders" section of the Magic Quadrant
and is giving Oracle, IBM, and Teradata a serious run for the money.

Check out the article here:
http://mediaproducts.gartner.com/reprints/microsoft/vol3/article7/article7.html

Need BI consulting, training, or innovative BI tools?
Come see our offering: http://www.PragmaticWorks.com

VS2008 ASP.NET Reports Web Site Project Type

Darren Herbold — Tue, 07 Oct 2008 02:13:00 GMT

Those of you who have had the opportunity to start working with Visual Studio 2008
will certainly have noticed all the new project templates now included with this new version.
If you are a web developer in particular, when choosing to create a new "Web Site"
project, you now have the option to create a new and very popular project template called
the WCF service. This option may have distracted you from another very interesting
template called the ASP.NET Reports Web Site.

What is this new project template you ask? Well, it turns out that the main purpose of
this template is to provide the developer with the means to create and expose Reporting
Services reports over the web. This template by default will supply the project with an
RLDC report (Reporting Services Report for the web) and a ReportViewer control bound
to the default aspx page.

On the start of creating the new project, the developer will be prompted with the Report
Wizard to start the design of the report you wish to expose in the web form. This article
will walk you through the steps of the wizard and explain them along the way.

Choosing the Reports Web Site project template:

This is the first page of the wizard. Of course you can choose to Cancel and go it
alone. More advanced developers will mostly likely choose to Cancel this wizard,
but it sure gets you off to a great start!

The next step is to Choose or Create a new Data Source:

The next step will ask you if you would like to store the newly created connection
to the config file.

This step will prompt for database objects to expose to your report. You can
choose from tables, views, stored procedures, and functions (UDFs).

In this example, I've chosen fields from the Product table.

This next step simply asks which data connection you wish to use to connect
to your data source.

After selecting the data connection, the next set of steps will help you define the
aesthetics of the report. In this example I've chose a Tabular layout.

Determine where you would like your fields to be positioned.

Choosing the table layout.

Choosing the color style of the table.

After completing the wizard, you will notice the following items in the
solution explorer pane. The wizard creates an object data source that
references a strongly-typed dataset. Also notice the report with an RDLC
extension. You can always design the report manually without the wizard
if you so choose.

Pressing F5 or clicking on the green play button will start the debugger
of the project and run the report in the web browser. Keep in mind that
the report is being displayed inside the Report Viewer control that is
bound to the web form.

SSIS variables in Data Flow Script Component in Milwaukee

Darren Herbold — Sat, 13 Sep 2008 12:03:04 GMT

While at a client in Milwaukee, I came upon an interesting problem on how to access a
user-defined variable in a Script Component in the Data Flow. There are actually two ways
to solve this problem. One technique is to place a Derived Column before the Script component
and create a new derived column using the variable as the expression. This will create a new
column in the data pipeline with the value of the variable as its value.

The other technique is to use the "Me" namespace in VB.NET directly in the Script Component
script code. The syntax will be as follows: Me.Variables.<variableName>. So if the variable name
was called myVar, the syntax would read: Me.Variables.myVar
One last thing you need to do is double click the Script Component to open the editor and under
the Script menu item, set the variable you wish to use in your script here:

I happened to be there in the Winter time so it was fiercely cold and I mean cold beyond
comprehension. The first day I got there it was -27. That's right, NEGATIVE 27. I knew
I was in trouble when I overheard to guys in the elevator having a conversion that went
something to the effect of:

Guy1: Wow, it's cold today.

Guy2: Yeah, but tomorrow its going to warm up and snow.

That's what he said, no kidding. Warm Up, AND SNOW!! Apparently it was TOO COLD
to snow! How crazy is that?!?!

Anyway, here are some pictures I took during my tenure there:

This is the view from the client's office. That's Lake Michigan, FROZEN.

My hotel in down-town Milwaukee

Riverwalk Downtown is beautiful at night. During the day, not so much.

SSIS in Chicago

Darren Herbold — Fri, 12 Sep 2008 23:00:55 GMT

While at a client in Chicago, I was tasked with loading data from a series
of flat files. Easy enough. The interesting requirement though, was to obtain
a hash value from the file before loading to determine if in fact the file had been
previously loaded.

The client had an executable that when run, provided a hash value as a return
value. The solution I came up with was to use a for-each file enumerator to loop
through the files, assign a variable to the full-qualified value of the file and then
pass that variable to the executable using an Execute Process Task.

When the Execute Process Task retrieved the value of the hash for that file, I
would then assign it to a variable. Earlier in the process I created a log table
that stored nothing but hash values. So all I needed to do at this point was to
check for the existence of the hash value in the log table to determine if the
file had been previously loaded. If the hash value existed in the table, then the
file has been loaded, and if not, load the file. That's it!

While we're on the topic of Chicago, I must admit that it is my favorite city to
visit. Why? Because it just plain rocks! All the great places to eat and drink,
the tall buildings with amazing architecture, and the people are the friendliest
I've encountered all of the 50 states.

Some of my favorite things to do when I'm working in Chicago is eat, drink,
and explore the city. Once I get off work, it's show-time baby!

Below are some pictures of my time there:

The Chicago River The Sears Tower

Union Station SSIS in Chicago!

Food court inside the Sears Tower
Look at all that deep-dish! Financial district

The hustle and bustle of the city.
I Love It!

Theater District

There's that Fat Tire again!

Custom Reporting Services Templates

Darren Herbold — Thu, 11 Sep 2008 22:43:00 GMT

Every client that I've been to has always requested that their reports be "consistent"
across the environment. What better way to accomplish this than to create a template
that can be used for all new report creation!

Basically a report template is nothing more than a report (RDL file) that you choose
as a starting point for building new reports from. The only catch is that you have to
place this report in a specific location on the file system so that the BIDS IDE will
see it. That location is:

C:\Program Files\Microsoft Visual Studio 8\Common7\IDE\PrivateAssemblies\ProjectItems\ReportProject

Now when you want to use this template, right click on the Reports folder in your
project and select New Item. You will then see the name of the report template
you created and added to the above directory. In the graphic below the template is
called AdventureWorksTemplate.

Below is the template I created that you can download and use and modify at your convenience.

Download the RDL file here: CustomReportTemplate.zip

If you have any questions, please feel free to reply to this blog!

Great experience in Redmond (Microsoft)

Darren Herbold — Wed, 10 Sep 2008 22:09:00 GMT

Well I just got back from Redmond where I had the opportunity to be included
in a focus group to set standards for upcoming Microsoft certification exams.
I collaborated with a group of highly-skilled individuals (most all MVPs) and had
a lot of fun during the process! Some of the folks I met were: Matthew Roche
(MVP who has been recently added to Microsoft ranks, Congrats Matthew!),
Peter (an MVP from Australia), John (MVP) and Rafael (MVP) from Mariner in
Charlotte, and Thomas from Paris, France. I don't remember some of the others
names, so if I left anyone out, I apologize!

Below are some pics I took while on the trip:

View of the city from the highway(obviously) SafeCo Field

Heading to Redmond!

Microsoft's building (one of many)

Me drinking Fat Tire at Red Robin
(Red Robin, yuck! I'm sorry, its
just plain awful) Market Street

Awesome seafood dish at Market Street!

Me and Thomas Some random I met while out drinking
(he kinda looks like Bill Gates!) (kidding, that's the wife Ashley)

Of course no West Coast trip would be complete without Fat Tire!
Skinny Dip beer is also made by New Belgium and it's awesome!
It may be time to move to Seattle!

Some weird space-looking thing Me and the random again.
(I'm in so much trouble!).

Redmond Bound!

Darren Herbold — Thu, 04 Sep 2008 10:07:35 GMT

I'll be at the Microsoft campus on Friday contributing to a focus group on BI certification
testing questions/scoring. I'm excited to contribute to this effort and mingle with some of
the Microsoft folks. I always love to travel to the west coast because it means I get to drink
my favorite beer, Fat Tire!!

PragmaticWorks Summit

Darren Herbold — Thu, 04 Sep 2008 09:56:56 GMT

Last week we kicked-off our week-long summit in Jacksonville, Florida. This is where we have
the opportunity to get together as a team and brainstorm on how we can grow the company.
This usually includes creating a roadmap for our new products, develop new sales strategies,
and conduct round-table brainstorming sessions on our offerings as a company. We accomplished
everything on our agenda and I'm very excited about the growth prospects of our company as
a whole. We are also proud to welcome our newest member to our sales staff, Robert Peters.
He's a highly qualified sales director from Quest Software, who decided to make a move to
a small and innovative company (that's us). Anyway, we are grateful to have him on board and
look forward to working with him. I'd also like to mention our newest and very talented developer
Mike, who just came on board a couple of weeks ago. Welcome Rob and Mike!
Below are some pics that I took on my BlackJack II mobile during my week down there.

These pics were taken at our office in Jacksonville.

Me and Brian above. Robert Peters, Brian, and Nayan

Brian, Tim, Nayan, and Rob Me, Tim, Nayan, and Rob

Tim and Nayan at Brian's house Tim and Nayan

Me and Rob getting a little crazy (things got a little fuzzy after that)...