Old ladyHow Big Is Your Package?
Size up and convert your DTS package to SSIS. If you have the biggest DTS package, win a gift card and certificate of a world record package size.  
Darren Herbold
in

Pragmatic Works

This Blog

Syndication

Tags

Darren Herbold

  • Business Intelligence and MDM (Master Data Management)

    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

    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

    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

    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

    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

    image

    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!

    image 

    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

    image

    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

    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:

    image

    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!

    image

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

    image

    image

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

    image

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

    image

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

    image

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

    image

    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.

    image

    Determine where you would like your fields to be positioned.

    image

    Choosing the table layout.

    image

    Choosing the color style of the table.

    image

    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.

    image

    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.

    image

  • SSIS variables in Data Flow Script Component in Milwaukee

    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:

    image

    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.

    PIC-0147 PIC-0150_1

    PIC-0155 PIC-0158

    My hotel in down-town Milwaukee
    PIC-0159PIC-0485

    Riverwalk Downtown is beautiful at night. During the day, not so much. 
    PIC-0487  PIC-0161

    PIC-0165 PIC-0167

  • SSIS in Chicago

    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
    PIC-0024 PIC-0026

    Union Station                                         SSIS in Chicago!
    PIC-0027 PIC-0034

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

    PIC-0049 PIC-0051

    The hustle and bustle of the city.
    I Love It!
    PIC-0053 PIC-0050

    Theater District
    PIC-0054 PIC-0055 PIC-0058

    There's that Fat Tire again!
    PIC-0059

  • Custom Reporting Services Templates

    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.

    clip_image002

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

    Download the RDL file here: CustomReportTemplate.zip

    image

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

  • Great experience in Redmond (Microsoft)

    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
    DSC01057 DSC01059

                                                                     Heading to Redmond!
    DSC01060 DSC01063

    Microsoft's building (one of many)             
    DSC01070  DSC01069

    DSC01094 DSC01097

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

    DSC01099 DSC01103

    DSC01104 DSC01109

                                                                   Awesome seafood dish at Market Street!
    DSC01120 DSC01128

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

    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!

    DSC01153 DSC01154

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

  • Redmond Bound!

    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!!

    image

  • PragmaticWorks Summit

    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.

    PIC-0905 PIC-0902

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

     

    PIC-0903 PIC-0904

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

     

    PIC-0908 PIC-0917

    Tim and Nayan at Brian's house                 Tim and Nayan

     

    PIC-0919 PIC-0920

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

More Posts Next page »
Copyright Pragmatic Works
Powered by Community Server (Non-Commercial Edition), by Telligent Systems