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

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".

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