Well today was my first attempt outside of a classroom setting to dig my heels into SSAS DMX. I have an idea that I want to put into action (automating a process) and I believe it will benefit my organization. So fire up SSMS and connect to instance that has Analysis Services.
1) Start a New DMX Query
2) Execute Script
SELECT * FROM $system.DBSCHEMA_CATALOGS
3) Returning Specific Columns
Similar to Transact-SQL you just specify the columns you want; however, you need to wrap up the column names with brackets [column].
SELECT [CATALOG_NAME] FROM $system.DBSCHEMA_CATALOGS
Pretty simple. Now let’s try with XMLA, which is XML for Analysis Services.
Just like before connect to an instance which has Analysis Services and published cubes.
1. Start a New XMLA Query
2. Execute the Script
<Discover xmlns="urn:schemas-microsoft-com:xml-analysis"> <RequestType>DBSCHEMA_CATALOGS</RequestType> <Restrictions /> <Properties /> </Discover>
When I first encountered this I thought to myself… must be a database or service or some sort, not being entirely sure of what to expect. I searched throughout the instance starting with the database engine and moved to the SSAS instance and was unable to find anything remotely named “flight recorder”. At this point I thought this error must have risen from an external call looking for a resource that no longer exists. Little did I realize that it is actually the SSAS log. It’s also been around sin SQL Server 2005.
If you need to determine if it is enabled then this is how you go about it.
Using SSMS connect Analysis Services for that instance. Once connected right-click on the instance and select “Properties” and in the name column just about the 13th row down you will see “Log \ Flight Recorder \ Enabled”
This video steps you through the process of deploying the SQL Server Analysis Services sample database.
Music by Kevin MacLeod