OCS Infotech
Sitemap Sitemap OCS Infotech Mail Mail
Resource Centre Resource Centre
OCS Infotech Case Studies
OCS Infotech Resource Centre
Home > Resource Centre > Research Papers
Case Studies Research Papers Back
 
OCS approach for robust design of OLAP cubes
OCS has identified the need of a robust OLAP cube as a decisive factor in creating a scalable and unified view of enterprise data. In this section we have elaborated global challenges followed by solutions from OCS, which are based on multiple experiences.
Case Studies Global Challenges for OLAP cubes design
 
  • Tendency to build the World in one Cube : A common problem, that occurs when the cube developer attempts to put every possible dimension into one cube.
  • Putting unrelated data in the same Cube : Several dimensions created which are only relevant to one part of the business and not the other.
  • Disproportionate granularity : Different levels of granularity exist between the fact table and dimension table
  • Fact tables have more foreign key members than those exist in the dimension table : Rows of data in the fact table are orphaned because they cannot be joined with one or more dimensions. These can also be termed as typically data source related issues
 
Case Studies OCS overall approach for handling these challenges
 
  • A robust requirement capture design with proper quality & feedback check mechanisms.

    a) Exhaustive user group meetings with scientifically designed templates
    & Review.
    b) Business Information Process design & approval of the same.
     
    i. Requirement design & Frequency of reports - By users
    ii. Reporting Parameters and drill down depth
    iii. Report design - Bar, Pie, Chart, Scatter etc.
    Vi. Report Consumption - Desk top, Web based
    c) Identifying the data sources, underlining gaps and creating new data sources.
    d) Database sizing, tuning and studying the build up right up to table levels.
    e) Query time analysis at the database level.
    f) Streamlining the query process design
    g) Connectivity


  • A robust Analysis design
    a) Enterprise wide dimension design layout.
    b) Establishing well defined hierarchies.
    c) Capturing all the measures.
    d) Creating an unified Dimensional Model


  • Cube building Road Map
    a) Identifying the number & type of cubes required.
    b) Mapping the cubes with the type of schema that will enhance
    the performance of the cube.
    c) A well defined naming library / dictionary of all cubes
     
    i. Label
    ii. User
    iii. Schema association
    Vi. Table Association
    v. Cube association (Diagram)


  • Regularly using Performance tuning techniques to review the overall information delivery Process
    a) Query tuning
    b) Report delivery time lags
    c) Server Response times
    d) Cube performance
 
Case Studies Challenges and Opportunities while building cubes on SSAS:
 
  • Creating High performance Optimized Cubes.
  • Effective & Efficient use of MDX and its Features
  • Building Visual hierarchies
  • Aligning cubes as per report specifications.
 
Case Studies OCS Strategic considerations while building cubes on SSAS:
 
  • Business Considerations

    1] Designing cubes from the business usage viewpoint.
     
    a) Build cubes with not more than 7-10 dimensions with a
    single reporting business objective.
    b) Report delivery based cube design
    c) Create derived measures for standard calculations.
    d) Use calculated measures depending on report nuances.
    e) Define all the aggregate values to the last atomic level.
    f) Create well defined user hierarchies.
    g) Understand the query roadmap and break it down to logical
    sub-queries.
    h) Offer custom Members to typical report consumers- On demand
    & design
    i) Localize cubes and their logic depending on user requirement.
    j) Creating effective drill through which are
     
    i. Standardized for the Organization
    ii. Customized at the user level.
    2] Identifying the flavor of the cubes - Relational or Multi-dimensional analysis
    3] Report delivery format from the cube: - A standard scientific
    technique with proper visual standards is evolved for each report.
    4] Properly organizing information of Folders and Perspectives.
    5] Developing robust perspectives so that the user is not bogged
    down with the semantics of the cube in terms of the inbuilt measures and dimensions.
    6] Building multiple measure groups in the same cube in order to enable to make comparisons even when the granularity of a dimension
    in each measure group is different.
    7] Optimize aggregations based on user report consumption & Frequency.
    8] Exploit the MDX expressions to compare cube values with business rules.
    9] Adding translations carefully and wherever necessary to maintain
    a unified business view of the database.
    10] Allow users to create what-if scenarios wherever applicable with
    write back options. The typical what-if are pre-determined at
    requirement analysis stage itself.
     
    a) Forecast requiring more than one iteration.
    b) Write-enable a dimension for scenario Planning

  • Technology Considerations

    1] Recommend an unified view of the Enterprise Data Model
    2] Recommend that the foreign keys in the fact table should be derived
    from the same data source used to create the dimension table(s).
    3] Recommend a Star schema for well defined cubes with well defined
    dimensions & hierarchies.
    4] Building Visual hierarchies
    5] Build logical virtual cubes to connect between cubes. This is for typical
    information integration & reporting activity
    6] Use Virtual dimensions as often as possible
     
    a) Creating virtual dimensions from the member properties of another
    dimension as they do not create additional data cell intersections.
    For end user it seems like a regular dimension.
    b) Keep the number of possible data intersections as low as possible.
    c) Provide facilities to avoid bloating of cubes.
    7] Resolving Time Dimension
     
    a) Create First-day-of Month & Year Month level typically for effecting
    relationships
    b) Create a Common Calendar and map the Time dimensions.
    8] Creating High performance Optimized Cubes
     
    a) Partitioning a cube
     
    i. Appropriate storage mode
    ii. Aggregation design processing options and pre-calculated totals for additive measures..
    iii. SQL Server Profiler and the Performance Monitor prove useful.
    b) Use proactive caching for multi-dimensional cubes.
    c) Creating appropriate Fact less - Fact Tables to ensure smooth write
    back to cubes
    d) Write enabled dimensions for forecast type of decisions.
    e) Ensure that the mapping is never changed – Use incremental
    update of dimensions.
    f) Enable well defined drill through for reporting measures.
    g) Organize measures effectively for user consumption.
    9] Efficient & Effective use of MDX
     
    a) To effectively link collaborative reports.
    b) Develop KPI’s effectively.
    c) Create user-hierarchies linked to the Data Source Views.
    d) Exploit the well defined hierarchy definition in dimensions.
    e) Creating a well knit list of logical sub-queries to arrive at a logical
    conclusion.
    f) Create set members for the most often used measures.
    g) Calculating the cumulative values from the beginning of a particular
    time period for use & re-use.
    h) An MDX script is not only a collection of MDX expressions or
    statements that represent cube calculations.
     
    i. Re-organize the cube’s MDX script to control which calculation is
    used when there are competing calculations for the same cell.
    ii. This is to map the journey of the calculation based on a
    reporting parameter.
    iii. Script assignments enable this very effectively.
    10] Use Update Cube MDX Statement
     
    a) To allocate a given high-level input value to create the necessary lowest-level values.
    b) Never allow changes in the aggregation written back as it may upset the cube structure.
 
Back Top