Results 1 to 3 of 3

Thread: Mapping file - looking for opinions

  1. #1

    Thread Starter
    Smooth Moperator techgnome's Avatar
    Join Date
    May 2002
    Posts
    34,531

    Mapping file - looking for opinions

    First a bit of background...
    We use SSIS to move data from our OLAP production database to the Data Warehouse (DW) system. Out of the box, we have a number of packages that handle some of the basic tables, but not all of them, mostly the ones that have been identified by clients over the years as the ones that are important. Occasionally an "extension" (a new package) has to be written to include a custom table or a basic table that wasn't originally included. I may have the opportunity to develop a tool to make some of the mundane tasks that we do during the extension development less painful and many times faster. Part of that involves knowing when a field needs to be mapped to a related table in the DW. Like a foreign key. IF it's a foireign key in the OLAP, it's likely to be a foreign key in the DW. Where it gets complicated is that the OLAP table name isn't always the DW name, as some are DIM tables, some are FACT tables (and if you know about warehouse architecture, you know what that means, if you don't, well... suffice it to say, it means that a table could be preceeded by DIM_ or FACT_) and in other cases, some tables from OLAP are combined into a single table.

    OK, so to help with this, I plan to have a mapping file so that when I encounter table X in OLAP, I know that it relates to DIM_X in the DW and can then automatically create the appropriate field definition and fkey descriptors.... and that's what I'm looking for some opinions on...

    I plan to use an XML file to make it easy for sharing among developers... what I'm not sure I care about is the format, so I'm looking for some opinions.
    Some thoughts I've had are:
    Code:
    <Mappings>
      <Table OLAP="SomeTable1" DW="Dim_SomeTable1" Field="SomeTable1DimID" />
      <Table OLAP="SomeTable2" DW="Dim_SomeTable2" Field="SomeTable2DimID" />
      <Table OLAP="SomeTable3" DW="Dim_SomeTable3" Field="SomeTable3DimID" />
    </Mappings>
    or

    Code:
    <Mappings>
      <Mapping>
        <OLAP>SomeTable1</OLAP>
        <DataWarehouse ID="SomeTable1DMID">SomeTable1DIM</DataWarehouse>
      </Mapping>
      <Mapping>
        <OLAP>SomeTable2</OLAP>
        <DataWarehouse ID="SomeTable2DMID">SomeTable2DIM</DataWarehouse>
      </Mapping>
    </Mappings>
    Mostly I'm looking for opinions on something simple and easily maintainable and usable. At the moment I don't have an opinion either way. The only requirement is that I need it easily parsable when I read it in code, I don't want to have to go through a lot of XML gymnastics to get the listing that I need, and it needs to be easily maintainable; both from the app side, and possible by hand from time to time.

    -tg
    * I don't respond to private (PM) requests for help. It's not conducive to the general learning of others.*
    * I also don't respond to friend requests. Save a few bits and don't bother. I'll just end up rejecting anyways.*
    * How to get EFFECTIVE help: The Hitchhiker's Guide to Getting Help at VBF - Removing eels from your hovercraft *
    * How to Use Parameters * Create Disconnected ADO Recordset Clones * Set your VB6 ActiveX Compatibility * Get rid of those pesky VB Line Numbers * I swear I saved my data, where'd it run off to??? *

  2. #2
    You don't want to know.
    Join Date
    Aug 2010
    Posts
    4,578

    Re: Mapping file - looking for opinions

    If you want it to be easy to parse, design a class hierarchy with the structure you want, then populate one with some fake data and use XML serialization to save it to a file. Then use that format.

    If you want it to be REALLY easy to parse, use JSON.

    As-is, neither of your XML formats looks like the default way XML serialization would deal with XML, but you could use attributes to get the first format. The default XML serialization scheme uses elements for each class property:
    Code:
    <Mappings>
        <Mapping>
            <OLAP>SomeTable1</OLAP>
            <DataWarehouseId>Dim_SomeTable1</DataWarehouseId>
            <Field>SomeTable1DimID</Field>
        </Mapping>
        ...
    If you want attributes you have to start bending the serialization framework, and eventually you have to write a parser. Once you start writing your own parsing code, there's not really a "more complex" structure. The main thing I don't like about the .NET XML serialization is it can have a tendency to want to spit out namespaces, which are a big hassle if you ever need to parse it without .NET serialization (for example, from another language).

    The JSON for such a class structure implied by the XML above would look like:
    Code:
    {
        "Mappings" : [
            {
                "OLAP": "SomeTable1",
                "DataWarehouseId": "Dim_SomeTable1",
                "Field": "SomeTable1DimId"
            },
            {
                "OLAP": "SomeTable2",
                "DataWarehouseId": "Dim_SomeTable2",
                "Field": "SomeTable2DimId"
            }
        ]
    }
    JSON tends to have only one way to represent any given class, which makes its parsing and manipulation much easier. Most languages have really good JSON parsers. .NET's the only one with "really good" XML parsers, and it's about like saying you had "the best tetanus shot of your life".
    This answer is wrong. You should be using TableAdapter and Dictionaries instead.

  3. #3

    Thread Starter
    Smooth Moperator techgnome's Avatar
    Join Date
    May 2002
    Posts
    34,531

    Re: Mapping file - looking for opinions

    I didn't htink about JSON... that could be an option... getting attributes in .NET isn't all that hard... it just requires an <XMLAttribute> markup on the property in the class and viola! it's an attribute in the XML ... Seeing as how I will get to have virtually free-reign in how it's designed and developed, I'm thinking about using it as a chance to work on my C# skills (we're a primarily VB-centric shop) so JSON may be a fun design choice "because I can" ... would also make it easier to share it, as it could then be store on a server somewhere and served up as JSON, making it easy to get updates as necessary. Good call.

    -tg
    * I don't respond to private (PM) requests for help. It's not conducive to the general learning of others.*
    * I also don't respond to friend requests. Save a few bits and don't bother. I'll just end up rejecting anyways.*
    * How to get EFFECTIVE help: The Hitchhiker's Guide to Getting Help at VBF - Removing eels from your hovercraft *
    * How to Use Parameters * Create Disconnected ADO Recordset Clones * Set your VB6 ActiveX Compatibility * Get rid of those pesky VB Line Numbers * I swear I saved my data, where'd it run off to??? *

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  



Click Here to Expand Forum to Full Width