This is the 3rd and final post in this series of blog posts, showing how you can use SQL Server Reporting Services (SSRS), DMVs and spatial data to generate real time automated user guide documentation for your Analysis Services (SSAS) OLAP cube.
- Part 1 – Creating the DMV stored procs
- Part 2 – Create the SSRS reports
- Part 3 – Use spatial data and maps to create a star schema view
- Download Source Code
UPDATE: I presented a 1 hour session at SQLBits 8 covering this work, you can watch the video here.
In this post, I’m going to enhance the measure group report to include a visualisation of the star schema. To do this I’ll be enhancing one of the stored procedures to utilise SQL Server 2008’s new spatial data types combined with SSRS 2008 R2’s new map functionality.
To do this we’ll update the dbo.upCubeDocDimensionsForMeasureGroup stored proc so that it returns a SQL geometry polygon for each row, in the right place around the circumference of the star. There’s a little math in this, but nothing more than a bit of trigonometry.
First the theory. We have an arbitrary number of dimensions that we need to place in a circle around a central point (the measure group). If we have 6 dimensions, then we need to divide the whole circle (360 degrees) by 6 (=60 degrees each) to get the angle of each around the hypothetical axis.
Therefore the first dimension needs to be at 60, the second at 120, the third at 180 etc, with the 6th at 360, completing the full circle.
Obviously the angle needs to vary depending on the number of dimensions in the query, so we need to calculate it within the stored proc. To do this I’m using common table expressions (CTE) to perform further calculations on the basic query.
We wrap the original proc query into a CTE and call it BaseData. We also add an extra field called Seq, which uniquely identifies each row, we’ll use this later to enable us to rank the dimensions.
;WITH BaseData AS
(
SELECT
mgd.*
, d.[DESCRIPTION]
, REPLACE(REPLACE(CAST(mgd.[DIMENSION_UNIQUE_NAME]
AS VARCHAR(255))
,'[',''),']','') AS DimensionCaption
, REPLACE(REPLACE(CAST(mgd.[MEASUREGROUP_NAME]
AS VARCHAR(255))
,'[',''),']','') AS MeasureGroupCaption
FROM OPENQUERY(CubeLinkedServer, 'SELECT
[CATALOG_NAME]
+[CUBE_NAME]
+[MEASUREGROUP_NAME]
+[DIMENSION_UNIQUE_NAME] AS Seq
, [CATALOG_NAME]
, [CUBE_NAME]
, [MEASUREGROUP_NAME]
, [MEASUREGROUP_CARDINALITY]
, [DIMENSION_UNIQUE_NAME]
, [DIMENSION_CARDINALITY]
, [DIMENSION_IS_VISIBLE]
, [DIMENSION_IS_FACT_DIMENSION]
, [DIMENSION_GRANULARITY]
FROM $SYSTEM.MDSCHEMA_MEASUREGROUP_DIMENSIONS
WHERE [DIMENSION_IS_VISIBLE]') mgd
INNER JOIN OPENQUERY(CubeLinkedServer, 'SELECT
[CATALOG_NAME]
,[CUBE_NAME]
,[DIMENSION_UNIQUE_NAME]
,[DESCRIPTION]
FROM $SYSTEM.MDSCHEMA_DIMENSIONS
WHERE [DIMENSION_IS_VISIBLE]') d
ON CAST(mgd.[CATALOG_NAME] AS VARCHAR(255))
= CAST(d.[CATALOG_NAME] AS VARCHAR(255))
AND CAST(mgd.[CUBE_NAME] AS VARCHAR(255))
= CAST(d.[CUBE_NAME] AS VARCHAR(255))
AND CAST(mgd.[DIMENSION_UNIQUE_NAME] AS VARCHAR(255))
= CAST(d.[DIMENSION_UNIQUE_NAME] AS VARCHAR(255))
WHERE CAST(mgd.[CATALOG_NAME] AS VARCHAR(255)) = @Catalog
AND CAST(mgd.[CUBE_NAME] AS VARCHAR(255)) = @Cube
AND CAST(mgd.[MEASUREGROUP_NAME] AS VARCHAR(255)) = @MeasureGroup
)
We’ll then add a new CTE which calculated the number of records returned by the previous query.
,TotCount AS
(
SELECT COUNT(*) AS RecCount FROM BaseData
)
Next we cross join TotCount with the base data, so that every row has the extra RecCount field. We then rank each record, providing each with a unique number from 1 to n.
, RecCount AS
(
SELECT RANK() OVER (ORDER BY CAST(Seq AS VARCHAR(255))) AS RecID
, RecCount
, BaseData.*
FROM
BaseData CROSS JOIN TotCount
)
Each record now contains its row number, as well as the total number of rows, so it’s easy to calculate its position around the circle (rank/n * 360). Now we have that, calculating the x and y coordinates of each dimension is simply a case of applying Sine and Cosine. Note that the SQL SIN and COS functions expect angles to be provided in radians not degrees, so we have to use the RADIANS function to convert it for us. I’m also multiplying the result by 1000 to scale the numbers up from -1 to +1 to -1000 to +1000, which makes our life easier later on.
, Angles AS
(
SELECT
*
, SIN(RADIANS((CAST(RecID AS FLOAT)
/CAST(RecCount AS FLOAT))
* 360)) * 1000 AS x
, COS(RADIANS((CAST(RecID AS FLOAT)
/CAST(RecCount AS FLOAT))
* 360)) * 1000 AS y
FROM RecCount
)
We can now use the x and y coordinates to create a point indicating the position of each dimension, using the code below.
geometry::STGeomFromText('POINT('
+ CAST(y AS VARCHAR(20))
+ ' '
+ CAST(x AS VARCHAR(20))
+ ')',4326) AS Posn
This is a good start, but we want a polygon box, not a single point. We can use a similar geometry function to create a polygon around our point.
geometry::STPolyFromText('POLYGON ((' +
CAST((y*@Stretch)+@BoxSize AS VARCHAR(20)) + ' '
+ CAST(x+(@BoxSize/2) AS VARCHAR(20)) + ', ' +
CAST((y*@Stretch)-@BoxSize AS VARCHAR(20)) + ' '
+ CAST(x+(@BoxSize/2) AS VARCHAR(20)) + ', ' +
CAST((y*@Stretch)-@BoxSize AS VARCHAR(20)) + ' '
+ CAST(x-(@BoxSize/2) AS VARCHAR(20)) + ', ' +
CAST((y*@Stretch)+@BoxSize AS VARCHAR(20)) + ' '
+ CAST(x-(@BoxSize/2) AS VARCHAR(20)) + ', ' +
CAST((y*@Stretch)+@BoxSize AS VARCHAR(20)) + ' '
+ CAST(x+(@BoxSize/2) AS VARCHAR(20)) + '
))',0) AS Box
You’ll notice that I’m multiplying the y axis by a @Stretch variable. This is to allow us to squash or squeeze the resulting star to make it look better in the report. I’m also using a @BoxSize variable which we can use to change the relative size of the boxes. It’s for this reason why I like to work on a -1000 to +1000 scale, it means we can have an integer box size of say 250 instead of a fraction such as 0.25, I just think it’s easier to read.
So you’ll now have a stored proc similar to this.
CREATE PROCEDURE [dbo].[upCubeDocDimensionsForMeasureGroup]
(@Catalog VARCHAR(255)
,@Cube VARCHAR(255)
,@MeasureGroup VARCHAR(255)
)
AS
DECLARE @BoxSize INT
DECLARE @Stretch FLOAT
SET @BoxSize = 250
SET @Stretch = 1.4
;WITH BaseData AS
(
SELECT
mgd.*
, d.[DESCRIPTION]
, REPLACE(REPLACE(CAST(mgd.[DIMENSION_UNIQUE_NAME]
AS VARCHAR(255))
,'[',''),']','') AS DimensionCaption
, REPLACE(REPLACE(CAST(mgd.[MEASUREGROUP_NAME]
AS VARCHAR(255))
,'[',''),']','') AS MeasureGroupCaption
FROM OPENQUERY(CubeLinkedServer, 'SELECT
[CATALOG_NAME]
+[CUBE_NAME]
+[MEASUREGROUP_NAME]
+[DIMENSION_UNIQUE_NAME] AS Seq
, [CATALOG_NAME]
, [CUBE_NAME]
, [MEASUREGROUP_NAME]
, [MEASUREGROUP_CARDINALITY]
, [DIMENSION_UNIQUE_NAME]
, [DIMENSION_CARDINALITY]
, [DIMENSION_IS_VISIBLE]
, [DIMENSION_IS_FACT_DIMENSION]
, [DIMENSION_GRANULARITY]
FROM $SYSTEM.MDSCHEMA_MEASUREGROUP_DIMENSIONS
WHERE [DIMENSION_IS_VISIBLE]') mgd
INNER JOIN OPENQUERY(CubeLinkedServer, 'SELECT
[CATALOG_NAME]
,[CUBE_NAME]
,[DIMENSION_UNIQUE_NAME]
,[DESCRIPTION]
FROM $SYSTEM.MDSCHEMA_DIMENSIONS
WHERE [DIMENSION_IS_VISIBLE]') d
ON CAST(mgd.[CATALOG_NAME] AS VARCHAR(255))
= CAST(d.[CATALOG_NAME] AS VARCHAR(255))
AND CAST(mgd.[CUBE_NAME] AS VARCHAR(255))
= CAST(d.[CUBE_NAME] AS VARCHAR(255))
AND CAST(mgd.[DIMENSION_UNIQUE_NAME] AS VARCHAR(255))
= CAST(d.[DIMENSION_UNIQUE_NAME] AS VARCHAR(255))
WHERE CAST(mgd.[CATALOG_NAME] AS VARCHAR(255)) = @Catalog
AND CAST(mgd.[CUBE_NAME] AS VARCHAR(255)) = @Cube
AND CAST(mgd.[MEASUREGROUP_NAME] AS VARCHAR(255)) = @MeasureGroup
)
,TotCount AS
(
SELECT COUNT(*) AS RecCount FROM BaseData
)
, RecCount AS
(
SELECT RANK() OVER (ORDER BY CAST(Seq AS VARCHAR(255))) AS RecID
, RecCount
, BaseData.*
FROM
BaseData CROSS JOIN TotCount
)
, Angles AS
(
SELECT
*
, SIN(RADIANS((CAST(RecID AS FLOAT)
/CAST(RecCount AS FLOAT))
* 360)) * 1000 AS x
, COS(RADIANS((CAST(RecID AS FLOAT)
/CAST(RecCount AS FLOAT))
* 360)) * 1000 AS y
FROM RecCount
)
,Results AS
(
SELECT
*
, geometry::STGeomFromText('POINT('
+ CAST(y AS VARCHAR(20))
+ ' '
+ CAST(x AS VARCHAR(20))
+ ')',4326) AS Posn
, geometry::STPolyFromText('POLYGON ((' +
CAST((y*@Stretch)+@BoxSize AS VARCHAR(20)) + ' '
+ CAST(x+(@BoxSize/2) AS VARCHAR(20)) + ', ' +
CAST((y*@Stretch)-@BoxSize AS VARCHAR(20)) + ' '
+ CAST(x+(@BoxSize/2) AS VARCHAR(20)) + ', ' +
CAST((y*@Stretch)-@BoxSize AS VARCHAR(20)) + ' '
+ CAST(x-(@BoxSize/2) AS VARCHAR(20)) + ', ' +
CAST((y*@Stretch)+@BoxSize AS VARCHAR(20)) + ' '
+ CAST(x-(@BoxSize/2) AS VARCHAR(20)) + ', ' +
CAST((y*@Stretch)+@BoxSize AS VARCHAR(20)) + ' '
+ CAST(x+(@BoxSize/2) AS VARCHAR(20)) + '
))',0) AS Box
FROM Angles
)
SELECT * FROM Results
GO
If you then execute this in Management Studio, you’ll notice an extra tab in the result window called Spatial Results.
EXEC [dbo].[upCubeDocDimensionsForMeasureGroup]
@Catalog = 'Adventure Works DW 2008R2',
@Cube = 'Adventure Works',
@MeasureGroup = 'Financial Reporting'
Click on the Spatial Results tab, then select Box as the spatial column, and you’ll see the boxes that we’ve created in a preview window.
This is now getting somewhere close. But as well as the dimensions, we also want to show the measure group in the middle, as well as lines linking them together to actuallly create our star. We can do this by adding a couple more geometry functions to our query. We end up with the end of our proc looking like this.
,Results AS
(
SELECT
*
, geometry::STGeomFromText('POINT('
+ CAST(y AS VARCHAR(20))
+ ' '
+ CAST(x AS VARCHAR(20))
+ ')',4326) AS Posn
, geometry::STPolyFromText('POLYGON ((' +
CAST((y*@Stretch)+@BoxSize AS VARCHAR(20)) + ' '
+ CAST(x+(@BoxSize/2) AS VARCHAR(20)) + ', ' +
CAST((y*@Stretch)-@BoxSize AS VARCHAR(20)) + ' '
+ CAST(x+(@BoxSize/2) AS VARCHAR(20)) + ', ' +
CAST((y*@Stretch)-@BoxSize AS VARCHAR(20)) + ' '
+ CAST(x-(@BoxSize/2) AS VARCHAR(20)) + ', ' +
CAST((y*@Stretch)+@BoxSize AS VARCHAR(20)) + ' '
+ CAST(x-(@BoxSize/2) AS VARCHAR(20)) + ', ' +
CAST((y*@Stretch)+@BoxSize AS VARCHAR(20)) + ' '
+ CAST(x+(@BoxSize/2) AS VARCHAR(20)) + '
))',0) AS Box
, geometry::STLineFromText('LINESTRING (0 0, '
+ CAST((y*@Stretch) AS VARCHAR(20))
+ ' ' + CAST(x AS VARCHAR(20))
+ ')', 0) AS Line
, geometry::STPolyFromText('POLYGON ((' +
CAST(0+@BoxSize AS VARCHAR(20)) + ' '
+ CAST(0+(@BoxSize/2) AS VARCHAR(20)) + ', ' +
CAST(0-@BoxSize AS VARCHAR(20)) + ' '
+ CAST(0+(@BoxSize/2) AS VARCHAR(20)) + ', ' +
CAST(0-@BoxSize AS VARCHAR(20)) + ' '
+ CAST(0-(@BoxSize/2) AS VARCHAR(20)) + ', ' +
CAST(0+@BoxSize AS VARCHAR(20)) + ' '
+ CAST(0-(@BoxSize/2) AS VARCHAR(20)) + ', ' +
CAST(0+@BoxSize AS VARCHAR(20)) + ' '
+ CAST(0+(@BoxSize/2) AS VARCHAR(20)) + '
))',0) AS CenterBox
FROM Angles
)
SELECT * FROM Results
GO
So, we’ve now got the polygons and lines being generated by the proc, it’s now time to add them into the report and display them to our users.
Firstly open up the CubeDoc_MeasureGroup.rdl report, and go to the properties of the dsDimensions dataset. Click Refresh Fields and the new x, y, Posn, Box, Line and CenterBox fields should now be available.
Then drag a Map from the toolbox onto the report. This will start the map wizard.
Select SQL Server Spatial Query as the source for the data and click Next.
Choose dsDimensions as the dataset to use for the data and click Next.
Choose Box as the spatial field, and Polygon as the layer type. It may well give you an error, just ignore it.
Don’t select the embed map or Bing maps layer.
Click Next, then select a basic map, select the default options for the remaining wizard stages and you’ll end up with a map in your report.
If you preview the report at this stage you won’t see the polygons. This is because the map still thinks it’s a geographical map, and it is trying to draw our boxes as latitude and longitudes. We don’t want this, but want it to show them on our own scale. To fix this, just change the CoordinateSystem property of the map from Geographic to Planar.
You can then preview the report, which should show you something like this
It still doesn’t look like a star but we’ve still got a few more changes to make. We need to add a couple more layers to the map, the center box for the measure group and then the lines to link them all together.
Add a new polygon layer to the map and then set the layer data to use the CenterBox field from the dsDimensions dataset.
Repeat the above, but with a new line layer instead of a polygon layer. Set the layer data to use the Line field of dsDimensions.
To move the lines behind the boxes, just rearrange the order of the layers by using the blue arrows in the map layers window. We want the Lines layer to be at the bottom.
Set the PolygonTemplate.BackgroundColour property of the CenterBox layer to Khaki, and the set the same property of the dimension box layer to LightBlue.
Then set the PolygonTemplate.Label property of the CenterBox layer to the MeasureGroupCaption field, and set the ShowLabel property to True. If you don’t then SSRS will decide whether or not to show the label, we want it to always show.
Set the PolygonTemplate.Label property of the Dimension layer to the DimensionCaption field, and set the ShowLabel property to True.
You can then play around with font sizes, zoom, background colours, line widths etc. to get the effects that you want, but you’ll end up with a star schema visualisation similar to this.
You can also configure actions for each layer. Using this you can hyperlink each dimension box to show the CubeDoc_Dimension report for the selected dimension etc, making the star schema interactive.
This has been quite a fun blog post to investigate, I hope you can take something useful from it and have as much fun with it as I’ve had with it. Every demo that I’ve seen using spatial data has been using maps, hopefully this shows an alternative use beyond geographical mapping.
- Part 1 – Creating the DMV stored procs
- Part 2 – Create the SSRS reports
- Part 3 – Use spatial data and maps to create a star schema view
- Download Source Code
In my system, I have multiple Catalogs with multiple cubes and with the code provided, I wasn’t able to pick between catalogs.
I ended up converting all the stored procs to leverage dynamic sql that allowed me to choose a catalog I want to view the cubes in.
DECLARE @sql as varchar(max)
SET @sql = ‘
SELECT *
FROM OPENROWSET(”MSOLAP”,”DATASOURCE=Server; Initial Catalog=’+@Catalog+’;”,
”SELECT *
FROM $SYSTEM.MDSCHEMA_CUBES
WHERE CUBE_SOURCE = 1”)
WHERE CAST([CATALOG_NAME] AS VARCHAR(255)) = ”’+@Catalog+”’
OR ”’+@Catalog+”’ IS NULL
‘
EXEC (@sql).
Also ran this to give me a list of Catalogs in a particular server.
DECLARE @sql as varchar(max)
SET @sql = ‘
SELECT CATALOG_NAME
FROM OpenRowset(”MSOLAP”,”DATASOURCE=Server;”,
”SELECT *
FROM $SYSTEM.DBSCHEMA_CATALOGS
”)
‘
EXEC (@sql)
That’s a great update, thanks for taking the time to share it.
Alex
Here are this and some other articles on SSAS Dynamic Management Views: http://ssas-wiki.com/w/Articles#DMV_.28Dynamic_Management_View.29
This is the cooles documentation for a cube I’ve ever seen! Neat, elegant and reusable. Thank you very, very much 🙂
Awesome post. All three articles are clear and useful. Thanks for making the effort available.
Hello Alex:
This was a tremendous post. Thank you so much for posting it. I too am fond of BIDocumenter, but was never happy with the way they document SSAS (too technical). You put it all together. You should seriously talk to PragmaticWorks and buy your work here so they can incorporate it into BIDocumenter.
Thanks again,
Roland