• yasserbatsh (10/10/2013)


    Thank you Eric for your reply.

    i will indeed need to leverage on sql server for my solution.

    What do u think is a better solution to tackle such requirement other than xml data types or sparse columns?

    Thanks.

    I have a data aggregator application which should be able to import data with

    different structures (mostly CRM data) such as Customer,invoice, campaigns, etc..

    we can't know the fields or structures in advance.

    When you say "data aggregator", I'm thinking something like a data mart, a database that aggregates data from multiple sources and possibly different structures. To leverage the strengths of a relational database like SQL Server, I would reccomend creating a fixed number of conformed and normalized tables. For exmple, there should be one (and only one) Customer table, Invoice table, Campaign table, etc.

    Your ETL process in the SQL Server environment would most likely be SSIS. It should have one field mapping (DataFlow task or package) for each data source, and the destination would be the appropriate conformed table. So, you may have multiple data sources, each with a different Customer structure and attributes, but you decide upfront which attributes (columns) you want to import into your tables.

    For example, when you query the Invoice table, the only thing differentiating records originating from 'Customer A' versus 'Customer B' would be something like a Client_ID column. The other columns like Invoice_ID, Product_ID, Quantity, etc. should be the same, even if the originating data from each client is structured very differently.

    If each customer has their own product coding scheme, but any code can map to an industry standard SKU, then you would also ideally perform mapping in the ETL process to conform customer specific product codes into SKU codes.

    A document centric database like MongoDB would allow you to persist and index the source data as is. However, to performing operations like reporting across multiple customers, running ad-hoc queries, or integrating with other databases would be much more limited than what you could do with a SQL Server database where data for all customers is conformed and normalized into relational tables.

    It really depends on the pourpose of this database and the case usage requirements.

    "Do not seek to follow in the footsteps of the wise. Instead, seek what they sought." - Matsuo Basho