Skip to main content
Member
Jul 7, 2022 9:09 AM UTC
Solved

Audit Cube View column/row sharing

Hi,

Is there a way to audit (in a report...) for all cube views, which one refers to a column or row cube view template ? 

Harry_0-1657183981579.png

Harry_0-1657184486383.png

 

Thanks.

 

Regards,

Harry

Employee - Mentor
Jul 7, 2022 12:17 PM UTC

Hi Harry,

Here is a query you can throw in a Data Adapter and then into a Gird View (or some other component) on a Dashboard to give you a quick look at all the Cube Views in your app and the Column and Row Sharing assignments.

SELECT
Name
,CAST(REPLACE(CAST(CubeViewItem.XmlData AS NVARCHAR(MAX)),'utf-8','utf-16') AS XML).value('(cubeView/cubeViewNameForSharingAllRows)[1]', 'varchar(max)') AS [Cube View Name For Sharing All Rows]
,CAST(REPLACE(CAST(CubeViewItem.XmlData AS NVARCHAR(MAX)),'utf-8','utf-16') AS XML).value('(cubeView/cubeViewNameForSharingAllRows2)[1]', 'varchar(max)') AS [Cube View Name For Sharing All Rows 2]
,CAST(REPLACE(CAST(CubeViewItem.XmlData AS NVARCHAR(MAX)),'utf-8','utf-16') AS XML).value('(cubeView/cubeViewNameForSharingAllCols)[1]', 'varchar(max)') AS [Cube View Name For Sharing All Columns]
,CAST(REPLACE(CAST(CubeViewItem.XmlData AS NVARCHAR(MAX)),'utf-8','utf-16') AS XML).value('(cubeView/cubeViewNameForSharingAllCols2)[1]', 'varchar(max)') AS [Cube View Name For Sharing All Columns 2]
FROM CubeViewItem

5 replies

Veteran
Jul 7, 2022 9:49 AM UTC

Hi Harry,
The first property you are mentioning is to share columns or rows coming from other cube views. In other words, it is a kind of template to create cube views rows or columns. Then you can open the Object Lookup, and begin typing the desired Cube View Template name, select the Cube View Template, and click Copy to Clipboard. (see design guide page 749).
Hope it helps,
Nic

HarryAuthor
Member
Jul 7, 2022 1:30 PM UTC

Thanks Nicolas, we are accustomed to create row/column cube views templates to reuse them in cube views...and check where a template is shared... but I was wondering if there was a way to have a comprehensive view that would display in a report all the cube views with a shared row/column template.

Thanks,

Harry

Veteran
Jul 7, 2022 1:32 PM UTC

Thanks for the feedback Harry! 

Employee - Mentor
Jul 7, 2022 12:17 PM UTC

Hi Harry,

Here is a query you can throw in a Data Adapter and then into a Gird View (or some other component) on a Dashboard to give you a quick look at all the Cube Views in your app and the Column and Row Sharing assignments.

SELECT
Name
,CAST(REPLACE(CAST(CubeViewItem.XmlData AS NVARCHAR(MAX)),'utf-8','utf-16') AS XML).value('(cubeView/cubeViewNameForSharingAllRows)[1]', 'varchar(max)') AS [Cube View Name For Sharing All Rows]
,CAST(REPLACE(CAST(CubeViewItem.XmlData AS NVARCHAR(MAX)),'utf-8','utf-16') AS XML).value('(cubeView/cubeViewNameForSharingAllRows2)[1]', 'varchar(max)') AS [Cube View Name For Sharing All Rows 2]
,CAST(REPLACE(CAST(CubeViewItem.XmlData AS NVARCHAR(MAX)),'utf-8','utf-16') AS XML).value('(cubeView/cubeViewNameForSharingAllCols)[1]', 'varchar(max)') AS [Cube View Name For Sharing All Columns]
,CAST(REPLACE(CAST(CubeViewItem.XmlData AS NVARCHAR(MAX)),'utf-8','utf-16') AS XML).value('(cubeView/cubeViewNameForSharingAllCols2)[1]', 'varchar(max)') AS [Cube View Name For Sharing All Columns 2]
FROM CubeViewItem

HarryAuthor
Member
Jul 7, 2022 2:43 PM UTC

Thank you so much much for sharing Eric .

Regards,

Harry