Managing Data Dictionaries in DW and DSS Database Applications

  • Comments posted to this topic are about the item Managing Data Dictionaries in DW and DSS Database Applications

    Frank Banin
    BI and Advanced Analytics Professional.

  • Why would you implement a dimensional model in SQL Server database tables instead of Analysis Services?

    [font="System"]Jay Quincy Allen, Managing Partner, Relational Models LLC[/font]

  • Excellent ideas; we have on going efforts to document the DW and cubes. I'm certain I'll be looking into this more after the beginning of the year.

    Beer's Law: Absolutum obsoletum
    "if it works it's out-of-date"

  • Great ideas and tips, thanks. Has anyone found a solution for using extended properties to track ETL mappings? As in, these data elements in the source system feed into this attribute in dimension x? I'm looking for a comprehensive way to track these relationships, so that (for example) we can trace the impact of a data type change in the source system.

  • if you don't have a choice but to pull data directly from an OLTP system or a non multi dimensional data source to the cube then that's OK to model data in SSAS. The best practice is model your business dimensionally in a database before SSAS.

    Frank Banin
    BI and Advanced Analytics Professional.

  • Frank, why would that be considered best practice? A temporal relational data warehouse in database tables captures more data than a dimensional model, has a lower footprint on disk, and has better performance when searching for a small number of records within large datasets. The OLAP structure, modeled dimensionally, provides the best performance when aggregating large datasets. I don't understand why creating an intermmediate data store dimensionally in database tables would be considered best practice.

    [font="System"]Jay Quincy Allen, Managing Partner, Relational Models LLC[/font]

  • if you are trying to find out which side I am on when it comes to the DW design approach dichotomy then I would say this is not the right forum. But if you are honestly seeking my opinion then that's OK.

    You mention the fact that Data modeled dimensionally, provides the best performance when aggregating large datasets. My opinion is that whatever is feeding your SSAS cube is best modelled dimensionally.

    Frank Banin
    BI and Advanced Analytics Professional.

  • Frank Banin (12/21/2011)


    ... My opinion is that whatever is feeding your SSAS cube is best modelled dimensionally.

    I agree, especially when you have diverse data sources.

    Beer's Law: Absolutum obsoletum
    "if it works it's out-of-date"

  • You're right, I took this away from the original topic and it would be better served in another forum. I understand that many say "it's the best", not to mention Ralph Kimball himself. I'm still looking for the "why".

    [font="System"]Jay Quincy Allen, Managing Partner, Relational Models LLC[/font]

  • The answer to that questions depends on "the what". Some implementations are better when you have disparate data sources (flat files, spreadsheets, xml files, etc) as

    DEK46656 suggests. One of the benefits of using an intermediary data marts is the enforcement of data integrity which you may lose when source your SSAS cube through database views or project data source views. This is a much larger discussion than the scope of this article.

    The article itself presents the topic very well and provides scenarios in which the method can prove to be extremely useful. Kudos, Frank.

  • I've been working my way through this and am very pleased with the results so far.

    One thing people should note is that if they choose to sync Cube Dimensions using Frank's script task (commented out in the package), the AMO Find method to match an attribute in a dimension requires the AttributeID and NOT the Attribute Name.

    Annoyingly this ID is set automatically by Visual Studio. Normally it will match the attribute Name but if you have done any monkeying around, it may not always do so.

    Just something to bear in mind if you get a NullException which you are struggling to track down.

    Thanks Frank. Excellent work.

    Ash

  • JQAllen, the reason I tend to stay away from this kind of question is also the fact that It is sometimes fruitless if you have such discussions with people who haven't done BI at the enterprise level, because this topic becomes relevant and serious if you encounter some of the disparate data sources Frances mentioned. I have also seen some stuff folks do with DSVs and call Modeling.

    I am however glad you aggree that this a bigger issue and a little out of scope, even though a buning one.

    May be we should look at the evidence in a seperate discussion, I certainly will like to hear what you and other people think or have come across. it's is a big world out there with various scenarios.

    Frank Banin
    BI and Advanced Analytics Professional.

  • A further update on using AMO to add descriptions to dimension attributes.

    I was having problems with this. The code was running fine (all green in the SSIS package) but the descriptions were not showing up. I'd noticed that in Visual Studio a Description property was not available for attributes of Cube Dimensions, but was available for Database Dimensions i.e. those dimensions from which cube dimensions are created.

    Furthermore I could not find mention of a Description property for CubeAttributes on MSDN.

    So on a hunch I adapted Frank's code to update the attributes of Database Dimensions rather than Cube Dimensions, as shown below (apologies to C# enthusiasts but I code in VB):

    'Dimensional Attribute

    'Dim cd As CubeDimension = cube.Dimensions.GetByName(rdr("DimensionOrMeasureGroup").ToString())

    'Dim ca As CubeAttribute = cd.Attributes.Find(rdr("ObjectName").ToString())

    'ca.Attribute.Description = rdr("ColumnDescription").ToString()

    Dim d As Dimension = db.Dimensions.GetByName(rdr("DimensionOrMeasureGroup").ToString())

    Dim da As DimensionAttribute = d.Attributes.GetByName(rdr("ObjectName").ToString())

    da.Description = rdr("ColumnDescription").ToString()

    d.Update()

    This seems to have done the trick. Descriptions now appear for dimension attributes when browsing cubes.

    I hope this helps those people who cannot use the BIDS Helper to sync dimensions e.g. if they are based on named queries.

    Ash

Viewing 13 posts - 1 through 12 (of 12 total)

You must be logged in to reply to this topic. Login to reply