Project Description
Many companies deny the use of linked servers due to security concerns.  Many blogged solutions to provide SSAS documentation require the implementation of a linked server.  This solution provides documentation on SSAS Solutions without a linked server by extracting all SSAS Metadata and loading it to a SQL Server database for reporting.

Users can elect to save historical data in data vault methodology which could prove useful in identifying what changed quickly in the actual code and SQL.

And more importantly, not everyone's has a budget for paid-for documentation tools.  

Features
SQL Server 2012 database with:

  1. Staging Tables
  2. Data Vault Tables
  3. PROCs for Loading Data Vault Tables
  4. PROCs for Reporting

SSIS Package:

  1. Ability to extract for one SSAS database or all databases on a server
  2. Parameter driven Connections
  3. Ability to choose to use Data Vault
  4. Load table tracks table updates
  5. Metadata extracted includes:
  • Cubes
  • Databases
  • Data Sources
    • Includes Connection Strings
  • Dependencies
    • Relationships
      • Active
      • In-Active
    • Calculations
      • MDX
      • DAX
    • Measures
    • Hierarchies
  • Dimensions
    • Dimension Levels
    • Permissions
    • Source SQL for Table
  • Hierarchies
  • KPIs
  • Measure Group Dimensions
  • Measure Groups
  • Measures
  • Memory Usage
  • Data Mining
    • Mining Columns
    • Mining Models
    • Mining Structure
    • Mining Structures
  • Roles
    • Members
    • Row Filter Restrictions
  • Storage Table Columns
  • Storage Tables
  • Provider Types

SSRS Solution:

  1. Two solutions
    • Business Users
    • Development Team
  2. Report parameter driven (includes parameters for server and database)
  3. Shared Data Sets
  4. Detailed reports for all metadata extracted (see SSIS) in a relational format
  5. Bus Matrix
  6. Schema Diagrams
  7. Clean crisp look and feel

Requirements

  • SQL Server 2012 Database Instance
  • SQL Server Analysis Services Instance(s)
  • Reporting Services Server or Reporting Services Integrated in SharePoint
  • SSDT

Testing for other versions  underway.

Upcoming Enhancements

  • Extracts from SSIS Package Solutions
  • Extracts from SSRS Report Solutions

Last edited Mar 22, 2016 at 12:23 AM by fujimoh, version 12