Reading XML Configuration Files using TSQL

Sometimes the result of a query depends on a parameter in a configuration file. Today, for example, I needed to write an SSRS report with a URL to another IIS. The dynamically generated  URL should look like this http://devserver/folder1?CampainID=123 or http://prodserver/folder2?CampaignID=123  .  The destination server name (DevServer or ProdServer) saved in an XML configuration file.

The XML file:

<?xml version="1.0" ?>

 <Servers>

  <Name>devserver</Name>

</Servers>

Obviously I could write .NET code in the RDL to retrieve data from the XML file. However, I used a simpler option using TSQL:

SELECT XMLSource.value('(Servers/Name)[1]', 'VARCHAR(50)') as servername

FROM (

    SELECT bulkColumn,

    cast(bulkcolumn as XML)  as XMLSource

    FROM OPENROWSET(BULK 'D:\ServerConfig2.xml',SINGLE_BLOB)

    AS servers

    ) as T1

SSAS DMV - Getting the MDX of Calculated Members

While Analyzing cube (performances or data accuracy) I run many MDX queries. It is important to know whether the Measures in the query are calculated and if so, what is the MDX expression of the calculated measure.

Option one - open Management Studio (import the cube schema, if you can't find the source code) and look at the Calculation Tab.

A simpler option: run the following query

SELECT Measure_name, expression, Measuregroup_name, *
FROM $system.mdschema_measures
--where Measure_name =   'Growth in Customer Base'
ORDER BY Measure_name

The SQL 2012 BOL now contains valuable information about DMV. Finally.  

http://msdn.microsoft.com/en-us/library/hh230820(v=sql.110).aspx

MDX - Handling Non Unique Member Names

The Problem: I needed to write a query using non unique member name. For ex. there are few cities called "Berlin". While using the name only one record retured.