On 10th June I presented a 10 minute nugget about SSAS DMV at the SQL Server User Group event at Microsoft TVP. Andrew Fryer presented 2008 R2 and Gemini and Ashwani Roy presented SSAS and MDX Studio. Sybase presented Information Architecture. Details of the event below.
This is the DMV scripts I used that evening:
select * from $system.dbschema_tables –where table_catalog = ‘AW’
select * from $system.dbschema_columns –where table_catalog = ‘AW’
select * from $system.dbschema_catalogs
select * from $system.mdschema_cubes
select * from $system.mdschema_hierarchies
select * from AW.[$Dim Product].[$Dim Product] –database dimension
select * from AW.[Sales].[$Dim Product] –cube dimension
select * from AW.[Sales].[Fact Sales] –cube
select * from AW.[$Dim Product].[Dim ProductMG] –dimension cube (1 dim no measure)
select * from $system.mdschema_members where cube_name = ‘$Dim Product’ and [dimension_unique_name] = ‘[Dim Product]’ and [hierarchy_unique_name] = ‘[Dim Product].[Color]’
select * from AW.[$Dim Product].[$Dim Product] where Color = ‘Black’ or Color = ‘Silver’ –no LIKE,<>,in,JOIN
select distinct Color from AW.[Sales].[$Dim Product]
select * from $system.discover_commands
select * from $system.discover_connections
select * from $system.discover_memoryusage
select * from $system.discover_object_memory_usage
select * from $system.discover_object_activity where object_reads > 0
select * from $system.discover_partition_stat
select * from systemrestrictschema($system.discover_partition_stat, CUBE_NAME = ‘Sales’, DATABASE_NAME = ‘AW’, MEASURE_GROUP_NAME = ‘Fact Sales’, PARTITION_NAME = ‘Fact Sales’)
select * from $system.discover_partition_stat
select * from systemrestrictschema($system.discover_dimension_stat,DIMENSION_NAME = ‘Dim Product’, DATABASE_NAME = ‘AW’)
select * from systemrestrictschema($system.discover_performance_counters, PERF_COUNTER_NAME = ‘\MSAS 2008:Connection\Current connections’,PERF_COUNTER_NAME = ‘\MSAS 2008:MDX\Total Cells Calculated’)
An example of discover schema rowset XMLA for comparison with DMV (DMV is so much easier isn’t?)
<Body>
<Discover xmlns =”urn:schemas-microsoft-com:xml-analysis”>
<RequestType>MDSCHEMA_CUBES</RequestType>
<Restrictions>
<RestrictionList>
<CATALOG_NAME>AW</CATALOG_NAME>
<CUBE_NAME>Sales</CUBE_NAME>
</RestrictionList>
</Restrictions>
<Properties>
<PropertyList>
<Catalog>AW</Catalog>
</PropertyList>
</Properties>
</Discover>
</Body>
</Envelope>
<!–Discover
( in RequestType as EnumString,
in Restrictions as Array,
in Properties as Array,
out Resultset as Rowset
)–>
And this is the script to create the linked server, and putting the DMV output into a table using select into to enable other T-SQL predicates such as ‘like’.
The idea with putting the DMV output into a table is so that we cancombine SSAS DMV with SSAS OlapQueryLog. We can then create a data warehouse (fact and dimension tables) from these tables, then we can build a cube on top of that data warehouse.
use SSASQueryLog
select * from QueryLog
exec master.dbo.sp_addlinkedserver @server=’SSAS’, @srvproduct=N”,
@provider=’MSOLAP’, @datasrc=’VIVA2′, @catalog=’AW’
select * from OpenQuery(SSAS, ‘select [Measures].[Order Quantity] on columns from [Sales]’)
select * from OpenQuery(SSAS, ‘select * from $system.discover_connections’)
select * from OpenQuery(SSAS,’select * from systemrestrictschema($system.discover_performance_counters, PERF_COUNTER_NAME = ”\MSAS 2008:Connection\Current connections”,
PERF_COUNTER_NAME = ”\MSAS 2008:MDX\Total Cells Calculated”)’)
–create/insert into table:
select * into Object_Activity from OpenQuery
(SSAS,’select * from $system.discover_object_activity’)
select * from Object_Activity where Object_Parent_Path like ‘%.AW.%’ and Object_Reads > 0 –like
select * into Object_Memory_Usage from OpenQuery
(SSAS,’select * from $system.discover_object_memory_usage’)
select * from Object_Memory_Usage –where Object_Parent_Path like ‘%.AW.%’
select * into Memory_Usage from OpenQuery
(SSAS,’select * from $system.discover_memoryusage’)
select * from Memory_Usage –where ObjectParentPath like ‘%.AW.%’
Details of the event
Evening Meeting on Wed Jun 10, 2009 in READING
SQL 2008 R2 and Gemini; From Data Modelling to Information Architecture and Attribute Relationships, Aggregations and using MDX Studio to its best
Organiser: UK SQL Server User Group
Address: Building 3, Microsoft Campus, Thames Valley Park, Reading, RG6 1WG
Agenda:
5.30pm – 6:00 Registration and networking + Tea/Coffee with biscuits.
Meet and greet.
6:00pm – 6:15pm Round Table discussion and Nuggets – ALL
Take stock and get the latest news in the SQL Server field. This is also a great opportunity to ask any burning questions you have, may be a problem at work.
It’s also a great opportunity to share your knowledge and gain exposure in the industry by giving a short 1 – 6 minute “nugget” demo/tip.
6.15pm – 7:00pm Andrew Fryer, Microsoft
SQL Server 2008 R2 and Gemini
SQL Server 2008 R2 will be the release with Gemini in it. Gemini is about trying to marry self service BI for end users with proper control of that data for the IT Professional. The beta program has only been recently announced and so it’s still early days but if you want to know what to expect then this is your first real opportunity in the UK. So join Andrew Fryer for an hour of peering into what 2010 BI looks like
7:00pm – 7:45pm Ruairi Prendiville, Sybase
From Data Modeling to Information Architecture
It used to be about doing the thing right: a tactical approach for data modelling was necessary, because all the data was contained within the same domain. Now it’s about doing the right thing. Management demands information that is strategic and which helps them make business decisions. This information is more complex, housed in multiple domains, created in different languages, and used by different areas of the organisation for opposing purposes. Adding to the complexity is the critical need for it to be timely, accurate and comprehensive.
In this 45-minute session, Andrew de Rozairo and Alex Pratt of Sybase will impart the secrets of successfully building a foundation for a comprehensive and successful information architecture. You’ll learn how to take a new approach to modelling: combining the business view, information view and the technology view to achieve a strategic vision.
7:45pm – 8pm BREAK: Light refreshments
More time to network and ask questions…
8pm – 8:15pm Vincent Rainardi
Nugget (demo) on SSAS
8:15pm – 9.00pm Ashwani Roy
Attribute Relationships, Aggregations and using MDX Studio to its best
Setting proper relationships for the attributes of a dimension is essential from a query performance point of view. It is one of the most important things while dimensional modelling.
Aggregations are pre calculated summaries of cube data that help enable Microsoft SQL Server 2005 Analysis Services (SSAS) to provide rapid query responses. Aggregations are like fact table data grouped and aggregated to a higher level in the Hierarchy. Building the correct aggregations can greatly enhance query performance.
MDX Studio can be used to analyze complex MDX expressions, monitor performance characteristics of MDX queries and get insight into how MDX interacts with other UDM features such as attribute relationships. MDX Studio provides unique visualization representation of MDX expression and allows the user to interact with different stages of MDX execution. It is built by Mosha Pasumansky, who is inventor of MDX and one of the Architects of Microsoft Analysis Services.
Data Warehousing and Data Science 