SQLTech Consulting

A while back I was very disappointed to discover that SSAS does not have any easy way to go through all of your dimensions and cubes to see which users have access to which dimensions, cubes, etc.  Sure you can go one by one in order to get the information, but I was really just looking for an easy way to view the permissions based on individual object permissions, similar to the way you can in SSMS.  I called Microsoft and unbelievably they had no solution.  What a pinch to be in when you have an important audit coming and now it is going to take an absorbant amount of time to come up with the data the auditors are going to need.

Well, fortunately for me, I ran into an incredible site today.  It’s called ASSP – Analysis Services Stored Procedure Project This site is great!  It’s an open source project and it is just what the doctor ordered.  You can download the software and open up the project in Visual Studio.  From there you just build the ASSP assembly and you are all set.  This assembly has a number of various functions built into it and it returns a number of helpful datasets in the datasets you will be looking for.  They have also have additional reports and things pre-made and built right in to the project for you as well, so you can deploy those right to SSRS.  Here is an exerpt of one of the funtion calls and documentation:

DiscoverXmlMetaData(path, restrictions)
This function executes a DISCOVER_XML_METADATA command, which returns a hierarchical resultset and then “flattens” that resultset and returns it as a datatable which can be displayed by SSMS or used in a Reporting Services report.

Unfortunately standard Xpath style queries did not prove rich enough to return data in it’s full context. That is you could return a list of all the dimensions in every cube on a given server, but you could not return a column containing the cube and database to which they belonged. So a hybrid Xpath syntax was developed which allows you to specify a list of fields to return at each level.

The easiest way to explain this is with a few examples

In order to return return a list of all the dimensions in every cube in the current database you would issue the following command.

CALL ASSP.discoverXmlMetaData("\Database\Cubes\Cube\Dimensions\Dimension");

But this returns the list without any context, If you want to report the CubeName and LastProcessed date you would put a vertical bar (|) after the Cube element in the path and then put a comma separated list of fields that you want returned

CALL ASSP.discoverXmlMetaData("\Database\Cubes\Cube|Name,LastProcessed\Dimensions\Dimension");

Note: it is not entirely true that the DiscoverXmlMetadata() command returns data without context – one of the features of this function is that it automatically returns the “ID” field of any ancestor elements in the resultset.

If you want to see what sort of paths are available the easiest way is to run the following command in an XMLA query window. You basically need to skip the xsd:schema> element and look at what is returned under the <row><xars:METADATA> elements.

<Discover xmlns="urn:schemas-microsoft-com:xml-analysis">
  <RequestType>DISCOVER_XML_METADATA</RequestType>
  <Restrictions />
  <Properties />
</Discover>

The path parameter actually matches from the leaf level up, so you could do a command like

CALL ASSP.discoverXmlMetaData("Dimension");

But this would produce a bit of a mess as it would return both cube dimensions and database dimensions and other dimension references from all over the xml resultset. The function is built in such a way that it build the columns for the resultset based off the first populated node in that matches the path so you will end up with a lot of blank columns.

But what this does mean is that you can specify just the minimal path that will unquiely identify the elements you are interested in.

eg

CALL ASSP.discoverXmlMetaData("Cube\Dimensions\Dimension");

returns the same result as

CALL ASSP.discoverXmlMetaData("\Database\Cubes\Cube\Dimensions\Dimension");

It should be noted that because this is base on Xml, the path is case sensitive and “dimension” is not the same as “Dimension”.

Role information is not exposed by any of the other schema rowsets. The following commands will extract information about roles, dimension permissions, attribute permissions and cube permissions and would allow for the complete documentation of the security settings.

CALL ASSP.DiscoverXmlMetadata("\Database\Roles\Role");

CALL ASSP.DiscoverXmlMetadata("\Database\Dimensions\Dimension\DimensionPermissions\DimensionPermission");

CALL ASSP.DiscoverXmlMetadata("\Database\Dimensions\Dimension\DimensionPermissions\DimensionPermission\AttributePermissions\AttributePermission")

CALL ASSP.DiscoverXmlMetadata("\Database\Cubes\Cube\CubePermissions\CubePermission");

I am so thrilled!  This is just what I was looking for.  Check it out and see for yourselves just how great this assembly is.

1. A trace of your production server activity
2. A current backup of your production database  

I took a look at their tutorial video and it looks quite impressive.  Their documentation states that they focus on 4 areas for SQL Server Performance.  They are as follows:  

1) Smart Indexing 

Qure recommends creating, removing and modifying indexes for best overall performance.
Qure evaluates all possible indexes for a query and then uses heuristics to combine the indexes for all queries, guaranteeing that:

• Redundant indexes are eliminated
• Indexes have the highest performance impact for as many queries as possible
• The creation and dropping of indexes produces a significant, database-wide performance improvement

My Take on it: The product page states that they use a patented algorithm that suggests code modifications and places where indexes should be changed. I would like to review and compare, how much better it is to the Index Tuning Wizard.

2) Query Syntax Optimization –

Qure evaluates numerous parameters for each query and generates pinpoint recommendations for query rewrites, including correctional scripts!

Query rewrite example 

3) Schema Optimization

Qure evaluates the scehma structure and can recommendt changing column data-types, adding or removing constraints and correcting potential design anomalies.
Qure performs a comprehensive statistical analysis of the data and its usage patterns and may suggest modifying the schema to enhance performance.

My Take on it: This feature is like combining data profiling capabilities of SSIS with Database Tuning Advisor. This really makes me curious of exploring, how effective this feature is. If it works to my expectation, it can revolutionize database modeling.

 
4) Additional Optimizations

Miscellaneous performance tuning recommendations, which may have a huge impact on performance
For example:

• Coding tecnhiques and best practices violations
• Maintenance optimization
• Hint usage
• Potentially erroneous queries
• Database and server settings

My Take on it: I would like to compare performance of this feature with that of Best Practices Analyzer tool. 

Conclusion

Overall this seems to be quite a powerful tool.  The cost of this product seems a bit higher than other vendors products that I have seen in the past so I’m not sure if there is any justification. I will review in depth in the coming days and I will post a followup so I can give a more diffinitive asnwer.

I was doing some research on the web today after running into an SSIS issue I was having and I came across a product called Expressor.  It seems to be a pretty powerful integration product so I downloaded a whitepaper on it.  I have included the white paper here Expressor White Paper .  I also found a demo on YouTube as well.

Has anyone had any experience with this product before or are they a current user?  I am wondering if this is something that could fit in with one of my clients but I am not sure I know enough about them just yet to reccommend them.  Let me know your thoughts.