Skip to main content

Avoid Data Duplication for Shared Members

One of the weak points of SQL Server Analysis Services (Multidimensional) is the absence of built-in logic for Shared Members: the multiple occurrence of members within one dimension. As a result, shared members must be turned into unique members and the data-values linked to these members must be duplicated (or triplicated, etc.) in the Cube.

This data-duplication doesn’t need to be problematic for sparse members - lilke rare products in a Product dimension. It is, however, especially cumbersome when the Default member of a dimension - the member that is chosen in case the dimension is irrelevant - is being duplicated. Such Default members tend to be dense (occur frequently). For example, in an HR dimension the functional members are only chosen for typical HR Accounts, like ‘Temp staff count'. For the majority of the Accounts the functional members are irrelevant and the data-values are booked on ‘Default’ / 'None’. Duplication of such members have a large impact on the data-density of the Cube.

CXO imported this feature into the HFM adapter, Longview adapter, OneStream adapter, and Oracle EPM Cloud adapter. You can make sure your cube isn't exploding because of shared members by designating certain members as default members.

This document describes how data-duplication can be limited for high-frequent dimension members.

Note: Unless mentioned otherwise, tables and procedures have to be present / be executed in the cxo_fact_… database.

How Do I Know if I Need This Feature?

Shared members are commonplace in EPM applications. Data for members, that occur multiple times, in a hierarchy are stored multiple times. This grows the cube, slows down cube processing and can affect query response times

Most shared members, especially when they do not have a lot of data, are usually not an issue.

Data dense members however in dimensions, like “none” or “unspecified”, that occur multiple times can become a problem.

Our adapters, using a stored procedure we have prepared, can target this issue and streamline your cube substantially.

To determine whether you need the shared member functionality, ask the following two questions to yourself:

  • Is processing your cube taking a lot of time (> 30 minutes)

  • Are many reports running (too) slow (> 10 seconds)?

Reach out to your Customer Success Manager or Account Manager to discuss your issues or get a copy of any code introduced in this article.

We can help with diagnosing if shared members are an issue and if this feature is indeed necessary or helpful.

Table defaultMembers

CXO provides a standard table defaultMembers in the CXO fact database so you don't have to create one by yourself. Reach out to your account manager or customer success manager for help if needed.

Here is an example of the code for the defaultMembers table:

CREATE TABLE [dbo].[defaultMembers](
				[analDim] [int] NOT NULL,
				[defaultMember] varchar NOT NULL,
				CONSTRAINT [PK_defaultMembers] PRIMARY KEY CLUSTERED
				(
				[analDim] ASC,
				[defaultMember] ASC
				)
			)

Typical contents of this table is:

The high-frequent members in Analytical Dimension 1 (dimenion A01, stored in dimension table cxo_dim_a01) are "Lan_Cards" and "Home_Network_Boxes". The members are in this table because:

  • They represent the dimension members that have a high frequency in the facts.
  • They occur multiple times in the dimension.

We found this by executing the script in Annex II, which resulted in:

This result shows that duplicate members cause the cube to be almost twice the size (Growth factor = 1.96) compared to a dimension with only unique members.

It further shows that the members ‘Lan_Cards’ and ‘Home_Network_Boxes' are most responsible for data-duplication, because they are 3 times in the dimension and occur at a high frequency in the facts.

Upgrading the dimension-tables

The original dimension-table (cxo_dim_a01) is as follows:

When processing the cube, the ID column is used to join with the fact-table. and you can see that for the Default Members this results in 3 copies.

By executing the Stored Procedure given in Annex I we do 2 things (i.e., running the SQL command 

exec [dbo].[sp_ignore_shared_members] @custom = 1
):
  1. We make sure that the link to the facts is only made by 1 of the duplicate members.
  2. We replace the other copies by an MDX script which refers them back to the original.

The resulting cxo_dim_a01 is:

Note that the fomulas replacing a hard-copy of the duplicate members must be propagated to the ancestry of these members.

Cube Calculation to Execute the CustomRollup Field

The last step to make the shared members visible in reports or controls involves the creation of a cube-calculation within one of the Analytical Dimensions that are not actively used by the Source System. Usually, we use the A10 dimension for this.

The content of the cxo_dim_a10 dimension remains unchanged:

For our example where we want to avoid hard copies of the shared members in the A01 dimension, this cube-calculation is:

create member currentcube.[A10].[A10].[Default]
as
case when [A01].[A01].CurrentMember.Properties("Has Custom Rollup")
THEN
				[A10].[A10].[[None]]] +
				SUM
				(
				StrToSet([A01].[A01].CurrentMember.Properties("Custom Rollup")),
				[A10].[A10].[[None]]]
				)
else
				[A10].[A10].[[None]]]
end
			, VISIBLE = 0 , ASSOCIATED_MEASURE_GROUP = 'Cxo Fact' ;

Of course, in any report, for A10 we should now use member Default instead [None].

Avoiding Duplicates in Two Dimensions

It is possible to follow this procedure for 2 dimensions at the same time. The DefaultMember table could then look like:

In this case, repeat all actions as for A01.

To accomodate Custom Rollup in 2 dimensions, the Cube-calculation ([A10].[A10].[Default]) gets more complicated:

create member currentcube.[A10].[A10].[Default]
as
case when [A01].[A01].CurrentMember.Properties("Has Custom Rollup")
THEN
				case when [A07].[A07].CurrentMember.Properties("Has Custom Rollup")
				then
				[A10].[A10].[[None]]] +
				SUM
				(
				StrToSet([A01].[A01].CurrentMember.Properties("Custom Rollup")) *
				StrToSet([A07].[A07].CurrentMember.Properties("Custom Rollup")) *
				{[A10].[A10].[[None]]]}
				)
				else
				[A10].[A10].[[None]]] +
				SUM
				(
				StrToSet([A01].[A01].CurrentMember.Properties("Custom Rollup")),
				[A10].[A10].[[None]]]
				)
				end
else
				case when [A07].[A07].CurrentMember.Properties("Has Custom Rollup")
				then
				[A10].[A10].[[None]]] +
				SUM
				(
				StrToSet([A07].[A07].CurrentMember.Properties("Custom Rollup")),
				[A10].[A10].[[None]]]
				)
				else
				[A10].[A10].[[None]]]
				end
end
			, VISIBLE = 0 , ASSOCIATED_MEASURE_GROUP = 'Cxo Fact' ;

Please check the report-performance when involving 2 dimensions. Theoretically, more than 2 dimensions is also possible. You simply follow above logic and add more If-Then-Else blocks.

HFM - Automatic Dimension Update

To ensure that the execution of the Stored Procedure sp_ignore_shared_members for 1 or 2 analytical dimension is done automatically after each dimension refresh (e.g., after a full extraction), you can include it in Stored Procedure sp_custom_after_creating_dimension_tables in the cxo_hfmexpressstaging_… database:

ALTER PROCEDURE [dbo].[sp_custom_after_creating_dimension_tables]
				@repository VARCHAR(50),
				@fact_db VARCHAR(50),
				@sourceID INT
				AS

				BEGIN

				declare @sql varchar(max)

				declare @message varchar(1000)
				select @message = 'Running procedure for avoiding multiple storage of Default memnbers (a01 and a07)'
				exec sp_log_message	@loadset_id = 1, @message = @message, @update = 0

				set @sql = '
				exec ' + @fact_db + '.dbo.sp_ignore_shared_members @custom = 1
				exec ' + @fact_db + '.dbo.sp_ignore_shared_members @custom = 7
				'
				exec (@sql)

			END

Annex I - Stored Procedure for Avoiding Data Duplication for Default Members in Analytical Dimensions

create procedure [dbo].[sp_ignore_shared_members]
				 @custom int
as
begin

declare @custom_string varchar(10) = cast (@custom as varchar(10))
declare @custom_short varchar(100) =
				 case when @custom < 10 then
				 concat('a0',@custom) else
	 			concat('a',@custom) end
declare @custom_long varchar(100) =
				case when @custom < 10 then
				concat('cxo_dim_a0',@custom) else
				 concat('cxo_dim_a',@custom) end
declare	@na varchar(50) = 'non_aggregating'

declare @sql varchar(max) =
'
				-- reset the dimension-table
				update		a
				set			a.ID = b.ID
				from		' + @custom_long + ' a
				inner join	' + @custom_long + ' b
				on			(a.[Name] = b.[Name])
				where		b.[ID] = b.[UID]
				update		' + @custom_long + ' set CustomRollup = NULL

if exists (select * from defaultMembers where analDim = ' + @custom_string + ')
				begin
				declare @defaultMember varchar(60)
				declare _defaultMembers cursor for
				 select 	defaultMember
				 from 	defaultMembers
				 where 	analDim = ' + @custom_string + '
				open _defaultMembers
				fetch next from _defaultMembers into @defaultMember
				while (@@FETCH_STATUS = 0)
				 begin
				 if exists (
				 select * from ' +
				 @custom_long +
				 ' where [Name] = @defaultMember
				 and ID <> [UID]
				 )
				begin
				declare @copy int
				declare @original int =
				 (
				 select	[UID]
				 from	' + @custom_long + '
				 where	[UID] = ID
				 and		[Name] = @defaultMember
				 )
				declare @child varchar(300)
				declare _copies cursor for
				 select	[UID], Child
				 from	' + @custom_long + '
				 where	[UID] <> ID
	 			and		[Name] = @defaultMember
				open _copies
				fetch next from _copies into @copy, @child
				while (@@FETCH_STATUS = 0)
				begin
				update	' + @custom_long + '
				set		CustomRollup = ''[' + @custom_short + '].[' +
				@custom_short + '].&['' +
				cast(@original as varchar(10)) + '']''
				where	[UID] = @copy
				while exists
				(
				select	*
				from	' + @custom_long + '
				where	child =
				(
				select	parent
				from	' + @custom_long + '
				where	[UID] = @copy
				)
				and		[Name] <> ''' + @na  + '''
				)
				begin
				set		@copy =
				(
				select	top 1 [UID]
				from	' + @custom_long + '
				where	child =
				(
				select	parent
				from	' + @custom_long + '
				where	[UID] = @copy
				)
				)
				update	' + @custom_long + '
				set		CustomRollup =
				case when
				CustomRollup is null
				then
				''''
				else
				CustomRollup + '',''
				end  +
				''[' + @custom_short + '].[' +
				@custom_short + '].&['' +
				cast(@original as varchar(10)) + '']''
				where	[UID] = @copy
				end
				fetch next from _copies into @copy, @child
				end
				close _copies
				deallocate _copies
				end
				fetch next from _defaultMembers into @defaultMember
				end
				close _defaultMembers
				deallocate _defaultMembers
				update	' + @custom_long + '
				set		CustomRollup = case when CustomRollup is not null then ''{'' +
				CustomRollup + ''}'' else NULL end

				update	' + @custom_long + '
				set		ID = [UID]
				where	[Name]
				in		(
				select	defaultMember
				from	defaultMembers
				where	analDim = ' + @custom_string + '
				)
				end
				'
				print (@sql)
				exec (@sql)
				end
			go

Annex II- Script for Showing Expected Data Duplication in the Cube

Below script can be executed in the cxo_fact_… database to find out which dimension-members are primary responsible for data-duplication inside the CXO cube:


declare		@base_count float
set			@base_count = (select count(*) from cxo_fact)
if exists (select * from sys.tables where [Name] = 'all_dimensions')
				drop table all_dimensions
create table all_dimensions ([Growth factor] float, dim varchar(10), [name] varchar(100), occurrences int, total_records int, overall_count float, [Root Member] varchar(100) null, [Root Member - 1] varchar(100) null,
[Parent] varchar(100),
				[Parent + 1] varchar(100),
				[Parent + 2] varchar(100),
				[Parent + 3] varchar(100),
				[Parent + 4] varchar(100), [Parent + 5] varchar(100), [Parent + 6] varchar(100), [Parent + 7] varchar(100), [Parent + 8] varchar(100), [Parent + 9] varchar(100), [Parent + 10] varchar(100))

				declare		@dim_growth float

				if exists (select * from sys.tables where [Name] = 'dim_count')
				drop table dim_count
				create table dim_count (id int, recordcount int, CONSTRAINT [PK_dim_count] PRIMARY KEY CLUSTERED ([id] ASC))
				if exists (select * from sys.tables where [Name] = 'dim_overall_count')
				drop table dim_overall_count
				create table dim_overall_count (dim varchar(10), [name] varchar(100) collate SQL_Latin1_General_CP1_CI_AS , occurrences int, total_records int, CONSTRAINT [PK_dim_overall_count] PRIMARY KEY CLUSTERED ([dim] ASC, [name] ASC))

truncate table dim_count
				truncate table dim_overall_count
				insert into	dim_count select ent, count(*) from cxo_fact group by ent
				insert into	dim_overall_count select 'ent' dimension, b.name, count(*) occurrences, sum(a.recordcount) total_records  from dim_count a inner join cxo_dim_ent b on (a.id = b.id) group by	b.name
				select		@dim_growth = cast((sum(total_records)*100.0 / @base_count) as int) / 100.0 from dim_overall_count
				insert into all_dimensions (overall_count, [Growth factor], [dim], [name], [occurrences], [total_records], [Parent], [Parent + 1], [Parent + 2], [Parent + 3], [Parent + 4], [Parent + 5], [Parent + 6], [Parent + 7], [Parent + 8], [Parent + 9], [Parent + 10])
				select		@base_count, @dim_growth [Growth factor], a.*, c.Name as Parent, d.Name as [Parent + 1], e.Name as [Parent + 2], f.Name as [Parent + 3], g.Name as [Parent + 4], h.Name as [Parent + 5], i.Name as [Parent + 6], j.Name as [Parent + 7], k.Name as [Parent + 8], l.Name as [Parent + 9], m.Name as [Parent + 10]
				from		dim_overall_count a inner join	cxo_dim_ent b on (a.name = b.Name)
				left join cxo_dim_ent c on (b.Parent = c.Child)
				left join cxo_dim_ent d on (c.Parent = d.Child)
				left join cxo_dim_ent e on (d.Parent = e.Child)
				left join cxo_dim_ent f on (e.Parent = f.Child)
				left join cxo_dim_ent g on (f.Parent = g.Child)
				left join cxo_dim_ent h on (g.Parent = h.Child)
				left join cxo_dim_ent i on (h.Parent = i.Child)
				left join cxo_dim_ent j on (i.Parent = j.Child)
				left join cxo_dim_ent k on (j.Parent = k.Child)
				left join cxo_dim_ent l on (k.Parent = l.Child)
				left join cxo_dim_ent m on (l.Parent = m.Child)

				truncate table dim_count
				truncate table dim_overall_count
				insert into	dim_count select acc, count(*) from cxo_fact group by acc
				insert into	dim_overall_count select 'acc' dimension, b.name, count(*) occurrences, sum(a.recordcount) total_records  from dim_count a inner join cxo_dim_acc b on (a.id = b.id) group by	b.name
				select		@dim_growth = cast((sum(total_records)*100.0 / @base_count) as int) / 100.0 from dim_overall_count
				insert into all_dimensions (overall_count, [Growth factor], [dim], [name], [occurrences], [total_records], [Parent], [Parent + 1], [Parent + 2], [Parent + 3], [Parent + 4], [Parent + 5], [Parent + 6], [Parent + 7], [Parent + 8], [Parent + 9], [Parent + 10])
				select  @base_count, @dim_growth [Growth factor], a.*, c.Name as Parent, d.Name as [Parent + 1], e.Name as [Parent + 2], f.Name as [Parent + 3], g.Name as [Parent + 4], h.Name as [Parent + 5], i.Name as [Parent + 6], j.Name as [Parent + 7], k.Name as [Parent + 8], l.Name as [Parent + 9], m.Name as [Parent + 10]
				from		dim_overall_count a inner join	cxo_dim_acc b on (a.name = b.Name)
				left join cxo_dim_acc c on (b.Parent = c.Child)
				left join cxo_dim_acc d on (c.Parent = d.Child)
				left join cxo_dim_acc e on (d.Parent = e.Child)
				left join cxo_dim_acc f on (e.Parent = f.Child)
				left join cxo_dim_acc g on (f.Parent = g.Child)
				left join cxo_dim_acc h on (g.Parent = h.Child)
				left join cxo_dim_acc i on (h.Parent = i.Child)
				left join cxo_dim_acc j on (i.Parent = j.Child)
				left join cxo_dim_acc k on (j.Parent = k.Child)
				left join cxo_dim_acc l on (k.Parent = l.Child)
				left join cxo_dim_acc m on (l.Parent = m.Child)

				truncate table dim_count
				truncate table dim_overall_count
				insert into	dim_count select a01, count(*) from cxo_fact group by a01
				insert into	dim_overall_count select 'a01' dimension, b.name, count(*) occurrences, sum(a.recordcount) total_records  from dim_count a inner join cxo_dim_a01 b on (a.id = b.id) group by	b.name
				select		@dim_growth = cast((sum(total_records)*100.0 / @base_count) as int) / 100.0 from dim_overall_count
				insert into all_dimensions (overall_count, [Growth factor], [dim], [name], [occurrences], [total_records], [Parent], [Parent + 1], [Parent + 2], [Parent + 3], [Parent + 4], [Parent + 5], [Parent + 6], [Parent + 7], [Parent + 8], [Parent + 9], [Parent + 10])
				select  @base_count, @dim_growth [Growth factor], a.*, c.Name as Parent, d.Name as [Parent + 1], e.Name as [Parent + 2], f.Name as [Parent + 3], g.Name as [Parent + 4], h.Name as [Parent + 5], i.Name as [Parent + 6], j.Name as [Parent + 7], k.Name as [Parent + 8], l.Name as [Parent + 9], m.Name as [Parent + 10]
				from		dim_overall_count a inner join	cxo_dim_a01 b on (a.name = b.Name)
				left join cxo_dim_a01 c on (b.Parent = c.Child)
				left join cxo_dim_a01 d on (c.Parent = d.Child)
				left join cxo_dim_a01 e on (d.Parent = e.Child)
				left join cxo_dim_a01 f on (e.Parent = f.Child)
				left join cxo_dim_a01 g on (f.Parent = g.Child)
				left join cxo_dim_a01 h on (g.Parent = h.Child)
				left join cxo_dim_a01 i on (h.Parent = i.Child)
				left join cxo_dim_a01 j on (i.Parent = j.Child)
				left join cxo_dim_a01 k on (j.Parent = k.Child)
				left join cxo_dim_a01 l on (k.Parent = l.Child)
				left join cxo_dim_a01 m on (l.Parent = m.Child)

				truncate table dim_count
				truncate table dim_overall_count
				insert into	dim_count select a02, count(*) from cxo_fact group by a02
				insert into	dim_overall_count select 'a02' dimension, b.name, count(*) occurrences, sum(a.recordcount) total_records  from dim_count a inner join cxo_dim_a02 b on (a.id = b.id) group by	b.name
				select		@dim_growth = cast((sum(total_records)*100.0 / @base_count) as int) / 100.0 from dim_overall_count
				insert into all_dimensions (overall_count, [Growth factor], [dim], [name], [occurrences], [total_records], [Parent], [Parent + 1], [Parent + 2], [Parent + 3], [Parent + 4], [Parent + 5], [Parent + 6], [Parent + 7], [Parent + 8], [Parent + 9], [Parent + 10])
				select  @base_count, @dim_growth [Growth factor], a.*, c.Name as Parent, d.Name as [Parent + 1], e.Name as [Parent + 2], f.Name as [Parent + 3], g.Name as [Parent + 4], h.Name as [Parent + 5], i.Name as [Parent + 6], j.Name as [Parent + 7], k.Name as [Parent + 8], l.Name as [Parent + 9], m.Name as [Parent + 10]
				from		dim_overall_count a inner join	cxo_dim_a02 b on (a.name = b.Name)
				left join cxo_dim_a02 c on (b.Parent = c.Child)
				left join cxo_dim_a02 d on (c.Parent = d.Child)
				left join cxo_dim_a02 e on (d.Parent = e.Child)
				left join cxo_dim_a02 f on (e.Parent = f.Child)
				left join cxo_dim_a02 g on (f.Parent = g.Child)
				left join cxo_dim_a02 h on (g.Parent = h.Child)
				left join cxo_dim_a02 i on (h.Parent = i.Child)
				left join cxo_dim_a02 j on (i.Parent = j.Child)
				left join cxo_dim_a02 k on (j.Parent = k.Child)
				left join cxo_dim_a02 l on (k.Parent = l.Child)
				left join cxo_dim_a02 m on (l.Parent = m.Child)

				truncate table dim_count
				truncate table dim_overall_count
				insert into	dim_count select a03, count(*) from cxo_fact group by a03
				insert into	dim_overall_count select 'a03' dimension, b.name, count(*) occurrences, sum(a.recordcount) total_records  from dim_count a inner join cxo_dim_a03 b on (a.id = b.id) group by	b.name
				select		@dim_growth = cast((sum(total_records)*100.0 / @base_count) as int) / 100.0 from dim_overall_count
				insert into all_dimensions (overall_count, [Growth factor], [dim], [name], [occurrences], [total_records], [Parent], [Parent + 1], [Parent + 2], [Parent + 3], [Parent + 4], [Parent + 5], [Parent + 6], [Parent + 7], [Parent + 8], [Parent + 9], [Parent + 10])
				select  @base_count, @dim_growth [Growth factor], a.*, c.Name as Parent, d.Name as [Parent + 1], e.Name as [Parent + 2], f.Name as [Parent + 3], g.Name as [Parent + 4], h.Name as [Parent + 5], i.Name as [Parent + 6], j.Name as [Parent + 7], k.Name as [Parent + 8], l.Name as [Parent + 9], m.Name as [Parent + 10]
				from		dim_overall_count a inner join	cxo_dim_a03 b on (a.name = b.Name)
				left join cxo_dim_a03 c on (b.Parent = c.Child)
				left join cxo_dim_a03 d on (c.Parent = d.Child)
				left join cxo_dim_a03 e on (d.Parent = e.Child)
				left join cxo_dim_a03 f on (e.Parent = f.Child)
				left join cxo_dim_a03 g on (f.Parent = g.Child)
				left join cxo_dim_a03 h on (g.Parent = h.Child)
				left join cxo_dim_a03 i on (h.Parent = i.Child)
				left join cxo_dim_a03 j on (i.Parent = j.Child)
				left join cxo_dim_a03 k on (j.Parent = k.Child)
				left join cxo_dim_a03 l on (k.Parent = l.Child)
				left join cxo_dim_a03 m on (l.Parent = m.Child)

				truncate table dim_count
				truncate table dim_overall_count
				insert into	dim_count select a04, count(*) from cxo_fact group by a04
				insert into	dim_overall_count select 'a04' dimension, b.name, count(*) occurrences, sum(a.recordcount) total_records  from dim_count a inner join cxo_dim_a04 b on (a.id = b.id) group by	b.name
				select		@dim_growth = cast((sum(total_records)*100.0 / @base_count) as int) / 100.0 from dim_overall_count
				insert into all_dimensions (overall_count, [Growth factor], [dim], [name], [occurrences], [total_records], [Parent], [Parent + 1], [Parent + 2], [Parent + 3], [Parent + 4], [Parent + 5], [Parent + 6], [Parent + 7], [Parent + 8], [Parent + 9], [Parent + 10])
				select  @base_count, @dim_growth [Growth factor], a.*, c.Name as Parent, d.Name as [Parent + 1], e.Name as [Parent + 2], f.Name as [Parent + 3], g.Name as [Parent + 4], h.Name as [Parent + 5], i.Name as [Parent + 6], j.Name as [Parent + 7], k.Name as [Parent + 8], l.Name as [Parent + 9], m.Name as [Parent + 10]
				from		dim_overall_count a inner join	cxo_dim_a04 b on (a.name = b.Name)
				left join cxo_dim_a04 c on (b.Parent = c.Child)
				left join cxo_dim_a04 d on (c.Parent = d.Child)
				left join cxo_dim_a04 e on (d.Parent = e.Child)
				left join cxo_dim_a04 f on (e.Parent = f.Child)
				left join cxo_dim_a04 g on (f.Parent = g.Child)
				left join cxo_dim_a04 h on (g.Parent = h.Child)
				left join cxo_dim_a04 i on (h.Parent = i.Child)
				left join cxo_dim_a04 j on (i.Parent = j.Child)
				left join cxo_dim_a04 k on (j.Parent = k.Child)
				left join cxo_dim_a04 l on (k.Parent = l.Child)
				left join cxo_dim_a04 m on (l.Parent = m.Child)

				truncate table dim_count
				truncate table dim_overall_count
				insert into	dim_count select a05, count(*) from cxo_fact group by a05
				insert into	dim_overall_count select 'a05' dimension, b.name, count(*) occurrences, sum(a.recordcount) total_records  from dim_count a inner join cxo_dim_a05 b on (a.id = b.id) group by	b.name
				select		@dim_growth = cast((sum(total_records)*100.0 / @base_count) as int) / 100.0 from dim_overall_count
				insert into all_dimensions (overall_count, [Growth factor], [dim], [name], [occurrences], [total_records], [Parent], [Parent + 1], [Parent + 2], [Parent + 3], [Parent + 4], [Parent + 5], [Parent + 6], [Parent + 7], [Parent + 8], [Parent + 9], [Parent + 10])
				select  @base_count, @dim_growth [Growth factor], a.*, c.Name as Parent, d.Name as [Parent + 1], e.Name as [Parent + 2], f.Name as [Parent + 3], g.Name as [Parent + 4], h.Name as [Parent + 5], i.Name as [Parent + 6], j.Name as [Parent + 7], k.Name as [Parent + 8], l.Name as [Parent + 9], m.Name as [Parent + 10]
				from		dim_overall_count a inner join	cxo_dim_a05 b on (a.name = b.Name)
				left join cxo_dim_a05 c on (b.Parent = c.Child)
				left join cxo_dim_a05 d on (c.Parent = d.Child)
				left join cxo_dim_a05 e on (d.Parent = e.Child)
				left join cxo_dim_a05 f on (e.Parent = f.Child)
				left join cxo_dim_a05 g on (f.Parent = g.Child)
				left join cxo_dim_a05 h on (g.Parent = h.Child)
				left join cxo_dim_a05 i on (h.Parent = i.Child)
				left join cxo_dim_a05 j on (i.Parent = j.Child)
				left join cxo_dim_a05 k on (j.Parent = k.Child)
				left join cxo_dim_a05 l on (k.Parent = l.Child)
				left join cxo_dim_a05 m on (l.Parent = m.Child)

				truncate table dim_count
				truncate table dim_overall_count
				insert into	dim_count select a06, count(*) from cxo_fact group by a06
				insert into	dim_overall_count select 'a06' dimension, b.name, count(*) occurrences, sum(a.recordcount) total_records  from dim_count a inner join cxo_dim_a06 b on (a.id = b.id) group by	b.name
				select		@dim_growth = cast((sum(total_records)*100.0 / @base_count) as int) / 100.0 from dim_overall_count
				insert into all_dimensions (overall_count, [Growth factor], [dim], [name], [occurrences], [total_records], [Parent], [Parent + 1], [Parent + 2], [Parent + 3], [Parent + 4], [Parent + 5], [Parent + 6], [Parent + 7], [Parent + 8], [Parent + 9], [Parent + 10])
				select  @base_count, @dim_growth [Growth factor], a.*, c.Name as Parent, d.Name as [Parent + 1], e.Name as [Parent + 2], f.Name as [Parent + 3], g.Name as [Parent + 4], h.Name as [Parent + 5], i.Name as [Parent + 6], j.Name as [Parent + 7], k.Name as [Parent + 8], l.Name as [Parent + 9], m.Name as [Parent + 10]
				from		dim_overall_count a inner join	cxo_dim_a06 b on (a.name = b.Name)
				left join cxo_dim_a06 c on (b.Parent = c.Child)
				left join cxo_dim_a06 d on (c.Parent = d.Child)
				left join cxo_dim_a06 e on (d.Parent = e.Child)
				left join cxo_dim_a06 f on (e.Parent = f.Child)
				left join cxo_dim_a06 g on (f.Parent = g.Child)
				left join cxo_dim_a06 h on (g.Parent = h.Child)
				left join cxo_dim_a06 i on (h.Parent = i.Child)
				left join cxo_dim_a06 j on (i.Parent = j.Child)
				left join cxo_dim_a06 k on (j.Parent = k.Child)
				left join cxo_dim_a06 l on (k.Parent = l.Child)
				left join cxo_dim_a06 m on (l.Parent = m.Child)

				truncate table dim_count
				truncate table dim_overall_count
				insert into	dim_count select a07, count(*) from cxo_fact group by a07
				insert into	dim_overall_count select 'a07' dimension, b.name, count(*) occurrences, sum(a.recordcount) total_records  from dim_count a inner join cxo_dim_a07 b on (a.id = b.id) group by	b.name
				select		@dim_growth = cast((sum(total_records)*100.0 / @base_count) as int) / 100.0 from dim_overall_count
				insert into all_dimensions (overall_count, [Growth factor], [dim], [name], [occurrences], [total_records], [Parent], [Parent + 1], [Parent + 2], [Parent + 3], [Parent + 4], [Parent + 5], [Parent + 6], [Parent + 7], [Parent + 8], [Parent + 9], [Parent + 10])
				select  @base_count, @dim_growth [Growth factor], a.*, c.Name as Parent, d.Name as [Parent + 1], e.Name as [Parent + 2], f.Name as [Parent + 3], g.Name as [Parent + 4], h.Name as [Parent + 5], i.Name as [Parent + 6], j.Name as [Parent + 7], k.Name as [Parent + 8], l.Name as [Parent + 9], m.Name as [Parent + 10]
				from		dim_overall_count a inner join	cxo_dim_a07 b on (a.name = b.Name)
				left join cxo_dim_a07 c on (b.Parent = c.Child)
				left join cxo_dim_a07 d on (c.Parent = d.Child)
				left join cxo_dim_a07 e on (d.Parent = e.Child)
				left join cxo_dim_a07 f on (e.Parent = f.Child)
				left join cxo_dim_a07 g on (f.Parent = g.Child)
				left join cxo_dim_a07 h on (g.Parent = h.Child)
				left join cxo_dim_a07 i on (h.Parent = i.Child)
				left join cxo_dim_a07 j on (i.Parent = j.Child)
				left join cxo_dim_a07 k on (j.Parent = k.Child)
				left join cxo_dim_a07 l on (k.Parent = l.Child)
				left join cxo_dim_a07 m on (l.Parent = m.Child)

				truncate table dim_count
				truncate table dim_overall_count
				insert into	dim_count select a08, count(*) from cxo_fact group by a08
				insert into	dim_overall_count select 'a08' dimension, b.name, count(*) occurrences, sum(a.recordcount) total_records  from dim_count a inner join cxo_dim_a08 b on (a.id = b.id) group by	b.name
				select		@dim_growth = cast((sum(total_records)*100.0 / @base_count) as int) / 100.0 from dim_overall_count
				insert into all_dimensions (overall_count, [Growth factor], [dim], [name], [occurrences], [total_records], [Parent], [Parent + 1], [Parent + 2], [Parent + 3], [Parent + 4], [Parent + 5], [Parent + 6], [Parent + 7], [Parent + 8], [Parent + 9], [Parent + 10])
				select  @base_count, @dim_growth [Growth factor], a.*, c.Name as Parent, d.Name as [Parent + 1], e.Name as [Parent + 2], f.Name as [Parent + 3], g.Name as [Parent + 4], h.Name as [Parent + 5], i.Name as [Parent + 6], j.Name as [Parent + 7], k.Name as [Parent + 8], l.Name as [Parent + 9], m.Name as [Parent + 10]
				from		dim_overall_count a inner join	cxo_dim_a08 b on (a.name = b.Name)
				left join cxo_dim_a08 c on (b.Parent = c.Child)
				left join cxo_dim_a08 d on (c.Parent = d.Child)
				left join cxo_dim_a08 e on (d.Parent = e.Child)
				left join cxo_dim_a08 f on (e.Parent = f.Child)
				left join cxo_dim_a08 g on (f.Parent = g.Child)
				left join cxo_dim_a08 h on (g.Parent = h.Child)
				left join cxo_dim_a08 i on (h.Parent = i.Child)
				left join cxo_dim_a08 j on (i.Parent = j.Child)
				left join cxo_dim_a08 k on (j.Parent = k.Child)
				left join cxo_dim_a08 l on (k.Parent = l.Child)
				left join cxo_dim_a08 m on (l.Parent = m.Child)

				truncate table dim_count
				truncate table dim_overall_count
				insert into	dim_count select a09, count(*) from cxo_fact group by a09
				insert into	dim_overall_count select 'a09' dimension, b.name, count(*) occurrences, sum(a.recordcount) total_records  from dim_count a inner join cxo_dim_a09 b on (a.id = b.id) group by	b.name
				select		@dim_growth = cast((sum(total_records)*100.0 / @base_count) as int) / 100.0 from dim_overall_count
				insert into all_dimensions (overall_count, [Growth factor], [dim], [name], [occurrences], [total_records], [Parent], [Parent + 1], [Parent + 2], [Parent + 3], [Parent + 4], [Parent + 5], [Parent + 6], [Parent + 7], [Parent + 8], [Parent + 9], [Parent + 10])
				select  @base_count, @dim_growth [Growth factor], a.*, c.Name as Parent, d.Name as [Parent + 1], e.Name as [Parent + 2], f.Name as [Parent + 3], g.Name as [Parent + 4], h.Name as [Parent + 5], i.Name as [Parent + 6], j.Name as [Parent + 7], k.Name as [Parent + 8], l.Name as [Parent + 9], m.Name as [Parent + 10]
				from		dim_overall_count a inner join	cxo_dim_a09 b on (a.name = b.Name)
				left join cxo_dim_a09 c on (b.Parent = c.Child)
				left join cxo_dim_a09 d on (c.Parent = d.Child)
				left join cxo_dim_a09 e on (d.Parent = e.Child)
				left join cxo_dim_a09 f on (e.Parent = f.Child)
				left join cxo_dim_a09 g on (f.Parent = g.Child)
				left join cxo_dim_a09 h on (g.Parent = h.Child)
				left join cxo_dim_a09 i on (h.Parent = i.Child)
				left join cxo_dim_a09 j on (i.Parent = j.Child)
				left join cxo_dim_a09 k on (j.Parent = k.Child)
				left join cxo_dim_a09 l on (k.Parent = l.Child)
				left join cxo_dim_a09 m on (l.Parent = m.Child)

				truncate table dim_count
				truncate table dim_overall_count
				insert into	dim_count select a10, count(*) from cxo_fact group by a10
				insert into	dim_overall_count select 'a10' dimension, b.name, count(*) occurrences, sum(a.recordcount) total_records  from dim_count a inner join cxo_dim_a10 b on (a.id = b.id) group by	b.name
				select		@dim_growth = cast((sum(total_records)*100.0 / @base_count) as int) / 100.0 from dim_overall_count
				insert into all_dimensions (overall_count, [Growth factor], [dim], [name], [occurrences], [total_records], [Parent], [Parent + 1], [Parent + 2], [Parent + 3], [Parent + 4], [Parent + 5], [Parent + 6], [Parent + 7], [Parent + 8], [Parent + 9], [Parent + 10])
				select  @base_count, @dim_growth [Growth factor], a.*, c.Name as Parent, d.Name as [Parent + 1], e.Name as [Parent + 2], f.Name as [Parent + 3], g.Name as [Parent + 4], h.Name as [Parent + 5], i.Name as [Parent + 6], j.Name as [Parent + 7], k.Name as [Parent + 8], l.Name as [Parent + 9], m.Name as [Parent + 10]
				from		dim_overall_count a inner join	cxo_dim_a10 b on (a.name = b.Name)
				left join cxo_dim_a10 c on (b.Parent = c.Child)
				left join cxo_dim_a10 d on (c.Parent = d.Child)
				left join cxo_dim_a10 e on (d.Parent = e.Child)
				left join cxo_dim_a10 f on (e.Parent = f.Child)
				left join cxo_dim_a10 g on (f.Parent = g.Child)
				left join cxo_dim_a10 h on (g.Parent = h.Child)
				left join cxo_dim_a10 i on (h.Parent = i.Child)
				left join cxo_dim_a10 j on (i.Parent = j.Child)
				left join cxo_dim_a10 k on (j.Parent = k.Child)
				left join cxo_dim_a10 l on (k.Parent = l.Child)
				left join cxo_dim_a10 m on (l.Parent = m.Child)

				truncate table dim_count
				truncate table dim_overall_count
				insert into	dim_count select gap, count(*) from cxo_fact group by gap
				insert into	dim_overall_count select 'gap' dimension, b.name, count(*) occurrences, sum(a.recordcount) total_records  from dim_count a inner join cxo_dim_gap b on (a.id = b.id) group by	b.name
				select		@dim_growth = cast((sum(total_records)*100.0 / @base_count) as int) / 100.0 from dim_overall_count
				insert into all_dimensions (overall_count, [Growth factor], [dim], [name], [occurrences], [total_records], [Parent], [Parent + 1], [Parent + 2], [Parent + 3], [Parent + 4], [Parent + 5], [Parent + 6], [Parent + 7], [Parent + 8], [Parent + 9], [Parent + 10])
				select  @base_count, @dim_growth [Growth factor], a.*, c.Name as Parent, d.Name as [Parent + 1], e.Name as [Parent + 2], f.Name as [Parent + 3], g.Name as [Parent + 4], h.Name as [Parent + 5], i.Name as [Parent + 6], j.Name as [Parent + 7], k.Name as [Parent + 8], l.Name as [Parent + 9], m.Name as [Parent + 10]
				from		dim_overall_count a inner join	cxo_dim_gap b on (a.name = b.Name)
				left join cxo_dim_gap c on (b.Parent = c.Child)
				left join cxo_dim_gap d on (c.Parent = d.Child)
				left join cxo_dim_gap e on (d.Parent = e.Child)
				left join cxo_dim_gap f on (e.Parent = f.Child)
				left join cxo_dim_gap g on (f.Parent = g.Child)
				left join cxo_dim_gap h on (g.Parent = h.Child)
				left join cxo_dim_gap i on (h.Parent = i.Child)
				left join cxo_dim_gap j on (i.Parent = j.Child)
				left join cxo_dim_gap k on (j.Parent = k.Child)
				left join cxo_dim_gap l on (k.Parent = l.Child)
				left join cxo_dim_gap m on (l.Parent = m.Child)

				truncate table dim_count
				truncate table dim_overall_count
				insert into	dim_count select mea, count(*) from cxo_fact group by mea
				insert into	dim_overall_count select 'mea' dimension, b.name, count(*) occurrences, sum(a.recordcount) total_records  from dim_count a inner join cxo_dim_mea b on (a.id = b.id) group by	b.name
				select		@dim_growth = cast((sum(total_records)*100.0 / @base_count) as int) / 100.0 from dim_overall_count
				insert into all_dimensions (overall_count, [Growth factor], [dim], [name], [occurrences], [total_records], [Parent], [Parent + 1], [Parent + 2], [Parent + 3], [Parent + 4], [Parent + 5], [Parent + 6], [Parent + 7], [Parent + 8], [Parent + 9], [Parent + 10])
				select  @base_count, @dim_growth [Growth factor], a.*, c.Name as Parent, d.Name as [Parent + 1], e.Name as [Parent + 2], f.Name as [Parent + 3], g.Name as [Parent + 4], h.Name as [Parent + 5], i.Name as [Parent + 6], j.Name as [Parent + 7], k.Name as [Parent + 8], l.Name as [Parent + 9], m.Name as [Parent + 10]
				from		dim_overall_count a inner join	cxo_dim_mea b on (a.name = b.Name)
				left join cxo_dim_mea c on (b.Parent = c.Child)
				left join cxo_dim_mea d on (c.Parent = d.Child)
				left join cxo_dim_mea e on (d.Parent = e.Child)
				left join cxo_dim_mea f on (e.Parent = f.Child)
				left join cxo_dim_mea g on (f.Parent = g.Child)
				left join cxo_dim_mea h on (g.Parent = h.Child)
				left join cxo_dim_mea i on (h.Parent = i.Child)
				left join cxo_dim_mea j on (i.Parent = j.Child)
				left join cxo_dim_mea k on (j.Parent = k.Child)
				left join cxo_dim_mea l on (k.Parent = l.Child)
				left join cxo_dim_mea m on (l.Parent = m.Child)

				truncate table dim_count
				truncate table dim_overall_count
				insert into	dim_count select flo, count(*) from cxo_fact group by flo
				insert into	dim_overall_count select 'flo' dimension, b.name, count(*) occurrences, sum(a.recordcount) total_records  from dim_count a inner join cxo_dim_flo b on (a.id = b.id) group by	b.name
				select		@dim_growth = cast((sum(total_records)*100.0 / @base_count) as int) / 100.0 from dim_overall_count
				insert into all_dimensions (overall_count, [Growth factor], [dim], [name], [occurrences], [total_records], [Parent], [Parent + 1], [Parent + 2], [Parent + 3], [Parent + 4], [Parent + 5], [Parent + 6], [Parent + 7], [Parent + 8], [Parent + 9], [Parent + 10])
				select  @base_count, @dim_growth [Growth factor], a.*, c.Name as Parent, d.Name as [Parent + 1], e.Name as [Parent + 2], f.Name as [Parent + 3], g.Name as [Parent + 4], h.Name as [Parent + 5], i.Name as [Parent + 6], j.Name as [Parent + 7], k.Name as [Parent + 8], l.Name as [Parent + 9], m.Name as [Parent + 10]
				from		dim_overall_count a inner join	cxo_dim_flo b on (a.name = b.Name)
				left join cxo_dim_flo c on (b.Parent = c.Child)
				left join cxo_dim_flo d on (c.Parent = d.Child)
				left join cxo_dim_flo e on (d.Parent = e.Child)
				left join cxo_dim_flo f on (e.Parent = f.Child)
				left join cxo_dim_flo g on (f.Parent = g.Child)
				left join cxo_dim_flo h on (g.Parent = h.Child)
				left join cxo_dim_flo i on (h.Parent = i.Child)
				left join cxo_dim_flo j on (i.Parent = j.Child)
				left join cxo_dim_flo k on (j.Parent = k.Child)
				left join cxo_dim_flo l on (k.Parent = l.Child)
				left join cxo_dim_flo m on (l.Parent = m.Child)

				update all_dimensions set [Root Member] =
				case when [Parent + 10] is null then
				case when [Parent + 9] is null then
				case when [Parent + 8] is null then
				case when [Parent + 7] is null then
				case when [Parent + 6] is null then
				case when [Parent + 5] is null then
				case when [Parent + 4] is null then
				case when [Parent + 3] is null then
				case when [Parent + 2] is null then
				case when [Parent + 1] is null then
				[Parent]
				else [Parent + 1] end
				else [Parent + 2] end
				else [Parent + 3] end
				else [Parent + 4] end
				else [Parent + 5] end
				else [Parent + 6] end
				else [Parent + 7] end
				else [Parent + 8] end
				else [Parent + 9] end
				else [Parent + 10] end

				update all_dimensions set [Root Member - 1] =
				case when [Parent + 10] is null then
				case when [Parent + 9] is null then
				case when [Parent + 8] is null then
				case when [Parent + 7] is null then
				case when [Parent + 6] is null then
				case when [Parent + 5] is null then
				case when [Parent + 4] is null then
				case when [Parent + 3] is null then
				case when [Parent + 2] is null then
				case when [Parent + 1] is null then
				null
				else [Parent] end
				else [Parent + 1] end
				else [Parent + 2] end
				else [Parent + 3] end
				else [Parent + 4] end
				else [Parent + 5] end
				else [Parent + 6] end
				else [Parent + 7] end
				else [Parent + 8] end
				else [Parent + 9] end

				select [Growth factor], dim [Dimension], [name] [Member Name], occurrences [Times Shared], total_records [No of Fact Records], round(100.0 * total_records / (1.0 * overall_count),2) [% of Total],[Root Member],

				[Root Member - 1],


				Parent, [Parent + 1], [Parent + 2], [Parent + 3], [Parent + 4], [Parent + 5], [Parent + 6], [Parent + 7], [Parent + 8], [Parent + 9], [Parent + 10]
				from all_dimensions order by	2 asc, 1 desc, 5 desc

				/*

				adding a third root-level:

				update all_dimensions set [Root Member - 2] =
				case when [Parent + 10] is null then --1
				case when [Parent + 9] is null then --2
				case when [Parent + 8] is null then --3
				case when [Parent + 7] is null then --4
				case when [Parent + 6] is null then --5
				case when [Parent + 5] is null then --6
				case when [Parent + 4] is null then --7
				case when [Parent + 3] is null then --8
				case when [Parent + 2] is null then --9
				null
				else [Parent] end --9
				else [Parent + 1] end --8
				else [Parent + 2] end --7
				else [Parent + 3] end --6
				else [Parent + 4] end --5
				else [Parent + 5] end --4
				else [Parent + 6] end --3
				else [Parent + 7] end --2
				else [Parent + 8] end --1

			*/

Was this article helpful?

We're sorry to hear that.