SQL Server – Generate and Use Size / Storage Dimension

Please check out my previous and related post on “Generating and using Duration dimension”.

If you are reading this post, you know “Dimensions” as they relate to BI. In this post, we will specifically see how we can create and generate a “Storage” dimension table so that we can then use it in PowerBI or another BI tool to slice and dice based on that dimension. By Storage, I specifically mean Bytes.

Depending on what you are measuring and doing analytics on, the size can vary wildly. For my purposes, I was working with Database Backups and I knew that the sizes I had to measure were between 0 and 20 TB. In your case, if that number is higher or lower, you can adjust the script to generate more/less data.  The granularity of the dimension was set at 10 MB

The backups dashboard (built using PowerBI) which used this “Storage dimension” is shown here.

backupsdashboard

In the middle there, one of the bar charts is “Backup Count & Duration by Size”. As the title says, this chart helps me determine which backups are small/large and determine how many backups are in each of those “Duration” buckets. The duration bucket that I used in this case could have been easily changed from GB ranges to TB ranges. For example, I filtered the chart to check counts of backups that are over 1 TB.  As one can see, I have a couple of databases that are in the 2.5 to 3 TB backup size range.

storage

Before we look at the script, let us take a look at the table layout

durationdimensiontable

…and some sample data to get an idea (although the table is too wide to “see” data meaningfully on the web)


StorageKey           BeginBytes           EndBytes             Bytes                KB                   MB                   GB                   TB                   BeginKBSmallRange    EndKBSmallRange      BeginKBMediumRange   EndKBMediumRange     BeginKBLargeRange    EndKBLargeRange      BeginKBHugeRange     EndKBHugeRange       BeginMBSmallRange    EndMBSmallRange      BeginMBMediumRange   EndMBMediumRange     BeginMBLargeRange    EndMBLargeRange      BeginMBHugeRange     EndMBHugeRange       BeginGBSmallRange    EndGBSmallRange      BeginGBMediumRange   EndGBMediumRange     BeginGBLargeRange    EndGBLargeRange      BeginGBHugeRange     EndGBHugeRange       BeginTBSmallRange    EndTBSmallRange      BeginTBMediumRange   EndTBMediumRange     BeginTBLargeRange    EndTBLargeRange      BeginTBHugeRange     EndTBHugeRange       ByteRange                                          KBSmallRange                                       KBMediumRange                                      KBLargeRange                                       KBHugeRange                                        MBSmallRange                                       MBMediumRange                                      MBLargeRange                                       MBHugeRange                                        GBSmallRange                                       GBMediumRange                                      GBLargeRange                                       GBHugeRange                                        TBSmallRange                                       TBMediumRange                                      TBLargeRange                                       TBHugeRange
-------------------- -------------------- -------------------- -------------------- -------------------- -------------------- -------------------- -------------------- -------------------- -------------------- -------------------- -------------------- -------------------- -------------------- -------------------- -------------------- -------------------- -------------------- -------------------- -------------------- -------------------- -------------------- -------------------- -------------------- -------------------- -------------------- -------------------- -------------------- -------------------- -------------------- -------------------- -------------------- -------------------- -------------------- -------------------- -------------------- -------------------- -------------------- -------------------- -------------------- -------------------------------------------------- -------------------------------------------------- -------------------------------------------------- -------------------------------------------------- -------------------------------------------------- -------------------------------------------------- -------------------------------------------------- -------------------------------------------------- -------------------------------------------------- -------------------------------------------------- -------------------------------------------------- -------------------------------------------------- -------------------------------------------------- -------------------------------------------------- -------------------------------------------------- -------------------------------------------------- --------------------------------------------------
0                    -10485760            0                    0                    0                    0                    0                    0                    0                    10000                0                    25000                0                    50000                0                    100000               0                    10                   0                    50                   0                    100                  0                    500                  0                    5                    0                    50                   0                    100                  0                    500                  0                    1                    0                    3                    0                    5                    0                    10                   [N/A]                                              [N/A]                                              [N/A]                                              [N/A]                                              [N/A]                                              [N/A]                                              [N/A]                                              [N/A]                                              [N/A]                                              [N/A]                                              [N/A]                                              [N/A]                                              [N/A]                                              [N/A]                                              [N/A]                                              [N/A]                                              [N/A]
1                    0                    10485760             10485760             10240                10                   0                    0                    10000                20000                0                    25000                0                    50000                0                    100000               10                   20                   0                    50                   0                    100                  0                    500                  0                    5                    0                    50                   0                    100                  0                    500                  0                    1                    0                    3                    0                    5                    0                    10                   0 Bytes to 10485760 Bytes                          10000 KB to 20000 KB                               0 KB to 25000 KB                                   0 KB to 50000 KB                                   0 KB to 100000 KB                                  10 MB to 20 MB                                     0 MB to 50 MB                                      0 MB to 100 MB                                     0 MB to 500 MB                                     0 GB to 5 GB                                       0 GB to 50 GB                                      0 GB to 100 GB                                     0 GB to 500 GB                                     0 TB to 1 TB                                       0 TB to 3 TB                                       0 TB to 5 TB                                       0 TB to 10 TB
2                    10485760             20971520             20971520             20480                20                   0                    0                    20000                30000                0                    25000                0                    50000                0                    100000               20                   30                   0                    50                   0                    100                  0                    500                  0                    5                    0                    50                   0                    100                  0                    500                  0                    1                    0                    3                    0                    5                    0                    10                   10485760 Bytes to 20971520 Bytes                   20000 KB to 30000 KB                               0 KB to 25000 KB                                   0 KB to 50000 KB                                   0 KB to 100000 KB                                  20 MB to 30 MB                                     0 MB to 50 MB                                      0 MB to 100 MB                                     0 MB to 500 MB                                     0 GB to 5 GB                                       0 GB to 50 GB                                      0 GB to 100 GB                                     0 GB to 500 GB                                     0 TB to 1 TB                                       0 TB to 3 TB                                       0 TB to 5 TB                                       0 TB to 10 TB
3                    20971520             31457280             31457280             30720                30                   0                    0                    30000                40000                25000                50000                0                    50000                0                    100000               30                   40                   0                    50                   0                    100                  0                    500                  0                    5                    0                    50                   0                    100                  0                    500                  0                    1                    0                    3                    0                    5                    0                    10                   20971520 Bytes to 31457280 Bytes                   30000 KB to 40000 KB                               25000 KB to 50000 KB                               0 KB to 50000 KB                                   0 KB to 100000 KB                                  30 MB to 40 MB                                     0 MB to 50 MB                                      0 MB to 100 MB                                     0 MB to 500 MB                                     0 GB to 5 GB                                       0 GB to 50 GB                                      0 GB to 100 GB                                     0 GB to 500 GB                                     0 TB to 1 TB                                       0 TB to 3 TB                                       0 TB to 5 TB                                       0 TB to 10 TB
4                    31457280             41943040             41943040             40960                40                   0                    0                    40000                50000                25000                50000                0                    50000                0                    100000               40                   50                   0                    50                   0                    100                  0                    500                  0                    5                    0                    50                   0                    100                  0                    500                  0                    1                    0                    3                    0                    5                    0                    10                   31457280 Bytes to 41943040 Bytes                   40000 KB to 50000 KB                               25000 KB to 50000 KB                               0 KB to 50000 KB                                   0 KB to 100000 KB                                  40 MB to 50 MB                                     0 MB to 50 MB                                      0 MB to 100 MB                                     0 MB to 500 MB                                     0 GB to 5 GB                                       0 GB to 50 GB                                      0 GB to 100 GB                                     0 GB to 500 GB                                     0 TB to 1 TB                                       0 TB to 3 TB                                       0 TB to 5 TB                                       0 TB to 10 TB
5                    41943040             52428800             52428800             51200                50                   0                    0                    50000                60000                50000                75000                50000                100000               0                    100000               50                   60                   50                   100                  0                    100                  0                    500                  0                    5                    0                    50                   0                    100                  0                    500                  0                    1                    0                    3                    0                    5                    0                    10                   41943040 Bytes to 52428800 Bytes                   50000 KB to 60000 KB                               50000 KB to 75000 KB                               50000 KB to 100000 KB                              0 KB to 100000 KB                                  50 MB to 60 MB                                     50 MB to 100 MB                                    0 MB to 100 MB                                     0 MB to 500 MB                                     0 GB to 5 GB                                       0 GB to 50 GB                                      0 GB to 100 GB                                     0 GB to 500 GB                                     0 TB to 1 TB                                       0 TB to 3 TB                                       0 TB to 5 TB                                       0 TB to 10 TB
6                    52428800             62914560             62914560             61440                60                   0                    0                    60000                70000                50000                75000                50000                100000               0                    100000               60                   70                   50                   100                  0                    100                  0                    500                  0                    5                    0                    50                   0                    100                  0                    500                  0                    1                    0                    3                    0                    5                    0                    10                   52428800 Bytes to 62914560 Bytes                   60000 KB to 70000 KB                               50000 KB to 75000 KB                               50000 KB to 100000 KB                              0 KB to 100000 KB                                  60 MB to 70 MB                                     50 MB to 100 MB                                    0 MB to 100 MB                                     0 MB to 500 MB                                     0 GB to 5 GB                                       0 GB to 50 GB                                      0 GB to 100 GB                                     0 GB to 500 GB                                     0 TB to 1 TB                                       0 TB to 3 TB                                       0 TB to 5 TB                                       0 TB to 10 TB
7                    62914560             73400320             73400320             71680                70                   0                    0                    70000                80000                50000                75000                50000                100000               0                    100000               70                   80                   50                   100                  0                    100                  0                    500                  0                    5                    0                    50                   0                    100                  0                    500                  0                    1                    0                    3                    0                    5                    0                    10                   62914560 Bytes to 73400320 Bytes                   70000 KB to 80000 KB                               50000 KB to 75000 KB                               50000 KB to 100000 KB                              0 KB to 100000 KB                                  70 MB to 80 MB                                     50 MB to 100 MB                                    0 MB to 100 MB                                     0 MB to 500 MB                                     0 GB to 5 GB                                       0 GB to 50 GB                                      0 GB to 100 GB                                     0 GB to 500 GB                                     0 TB to 1 TB                                       0 TB to 3 TB                                       0 TB to 5 TB                                       0 TB to 10 TB

I have transposed the first three rows to show the actual data generated

StorageKey 0 1 2
BeginBytes -10485760 0 10485760
EndBytes 0 10485760 20971520
Bytes 0 10485760 20971520
KB 0 10240 20480
MB 0 10 20
GB 0 0 0
TB 0 0 0
BeginKBSmallRange 0 10000 20000
EndKBSmallRange 10000 20000 30000
BeginKBMediumRange 0 0 0
EndKBMediumRange 25000 25000 25000
BeginKBLargeRange 0 0 0
EndKBLargeRange 50000 50000 50000
BeginKBHugeRange 0 0 0
EndKBHugeRange 100000 100000 100000
BeginMBSmallRange 0 10 20
EndMBSmallRange 10 20 30
BeginMBMediumRange 0 0 0
EndMBMediumRange 50 50 50
BeginMBLargeRange 0 0 0
EndMBLargeRange 100 100 100
BeginMBHugeRange 0 0 0
EndMBHugeRange 500 500 500
BeginGBSmallRange 0 0 0
EndGBSmallRange 5 5 5
BeginGBMediumRange 0 0 0
EndGBMediumRange 50 50 50
BeginGBLargeRange 0 0 0
EndGBLargeRange 100 100 100
BeginGBHugeRange 0 0 0
EndGBHugeRange 500 500 500
BeginTBSmallRange 0 0 0
EndTBSmallRange 1 1 1
BeginTBMediumRange 0 0 0
EndTBMediumRange 3 3 3
BeginTBLargeRange 0 0 0
EndTBLargeRange 5 5 5
BeginTBHugeRange 0 0 0
EndTBHugeRange 10 10 10
ByteRange [N/A] 0 Bytes to 10485760 Bytes 10485760 Bytes to 20971520 Bytes
KBSmallRange [N/A] 10000 KB to 20000 KB 20000 KB to 30000 KB
KBMediumRange [N/A] 0 KB to 25000 KB 0 KB to 25000 KB
KBLargeRange [N/A] 0 KB to 50000 KB 0 KB to 50000 KB
KBHugeRange [N/A] 0 KB to 100000 KB 0 KB to 100000 KB
MBSmallRange [N/A] 10 MB to 20 MB 20 MB to 30 MB
MBMediumRange [N/A] 0 MB to 50 MB 0 MB to 50 MB
MBLargeRange [N/A] 0 MB to 100 MB 0 MB to 100 MB
MBHugeRange [N/A] 0 MB to 500 MB 0 MB to 500 MB
GBSmallRange [N/A] 0 GB to 5 GB 0 GB to 5 GB
GBMediumRange [N/A] 0 GB to 50 GB 0 GB to 50 GB
GBLargeRange [N/A] 0 GB to 100 GB 0 GB to 100 GB
GBHugeRange [N/A] 0 GB to 500 GB 0 GB to 500 GB
TBSmallRange [N/A] 0 TB to 1 TB 0 TB to 1 TB
TBMediumRange [N/A] 0 TB to 3 TB 0 TB to 3 TB
TBLargeRange [N/A] 0 TB to 5 TB 0 TB to 5 TB
TBHugeRange [N/A] 0 TB to 10 TB 0 TB to 10 TB

You would use the “StorageKey” column in your Fact tables after joining to “Dim.StorageDimension” on BeginByte and EndByte values.

The code itself is below. I start at 0 bytes and go up to 20 TB in 10 MB increments. You can adjust these at the top of the script to customize the buckets to fit your needs


------------------------------------------------------------------------------------------------
--Version History
--    v1.0 - 20161010 - Created by Jana Sattainathan | Twitter @SQLJana | WordPress: SQLJana.WordPress.com
------------------------------------------------------------------------------------------------

DECLARE @StartSizeMB INT = 0,
		@IncrementMB INT = 10,
		@MaxSizeMB INT = 20971520; /* 20 TB */

DECLARE @RangeKBSmall INT = 10000,
		@RangeKBMedium INT = 25000,
		@RangeKBLarge INT = 50000,
		@RangeKBHuge INT = 100000,
		@RangeMBSmall INT = 10,
		@RangeMBMedium INT = 50,
		@RangeMBLarge INT = 100,
		@RangeMBHuge INT = 500,
		@RangeGBSmall INT = 5,
		@RangeGBMedium INT = 50,
		@RangeGBLarge INT = 100,
		@RangeGBHuge INT = 500,
		@RangeTBSmall INT = 1,
		@RangeTBMedium INT = 3,
		@RangeTBLarge INT = 5,
		@RangeTBHuge INT = 10;

DECLARE @Increments INT = @MaxSizeMB/@IncrementMB;

IF EXISTS (
	SELECT *
	FROM sys.objects so
	WHERE SCHEMA_NAME(so.schema_id) = 'Dim' AND so.name = 'StorageDimension'
)
	DROP TABLE Dim.StorageDimension;

CREATE TABLE [Dim].[StorageDimension](
	[StorageKey] [bigint] NOT NULL PRIMARY KEY,
	[BeginBytes] [bigint] NOT NULL,
	[EndBytes] [bigint] NOT NULL,
	[Bytes] [bigint] NOT NULL,
	[KB] [bigint] NOT NULL,
	[MB] [bigint] NOT NULL,
	[GB] [bigint] NOT NULL,
	[TB] [bigint] NOT NULL,
	[BeginKBSmallRange] [bigint] NOT NULL,
	[EndKBSmallRange] [bigint] NOT NULL,
	[BeginKBMediumRange] [bigint] NOT NULL,
	[EndKBMediumRange] [bigint] NOT NULL,
	[BeginKBLargeRange] [bigint] NOT NULL,
	[EndKBLargeRange] [bigint] NOT NULL,
	[BeginKBHugeRange] [bigint] NOT NULL,
	[EndKBHugeRange] [bigint] NOT NULL,
	[BeginMBSmallRange] [bigint] NOT NULL,
	[EndMBSmallRange] [bigint] NOT NULL,
	[BeginMBMediumRange] [bigint] NOT NULL,
	[EndMBMediumRange] [bigint] NOT NULL,
	[BeginMBLargeRange] [bigint] NOT NULL,
	[EndMBLargeRange] [bigint] NOT NULL,
	[BeginMBHugeRange] [bigint] NOT NULL,
	[EndMBHugeRange] [bigint] NOT NULL,
	[BeginGBSmallRange] [bigint] NOT NULL,
	[EndGBSmallRange] [bigint] NOT NULL,
	[BeginGBMediumRange] [bigint] NOT NULL,
	[EndGBMediumRange] [bigint] NOT NULL,
	[BeginGBLargeRange] [bigint] NOT NULL,
	[EndGBLargeRange] [bigint] NOT NULL,
	[BeginGBHugeRange] [bigint] NOT NULL,
	[EndGBHugeRange] [bigint] NOT NULL,
	[BeginTBSmallRange] [bigint] NOT NULL,
	[EndTBSmallRange] [bigint] NOT NULL,
	[BeginTBMediumRange] [bigint] NOT NULL,
	[EndTBMediumRange] [bigint] NOT NULL,
	[BeginTBLargeRange] [bigint] NOT NULL,
	[EndTBLargeRange] [bigint] NOT NULL,
	[BeginTBHugeRange] [bigint] NOT NULL,
	[EndTBHugeRange] [bigint] NOT NULL,
	[ByteRange] [varchar](50) NOT NULL,
	[KBSmallRange] [varchar](50) NOT NULL,
	[KBMediumRange] [varchar](50) NOT NULL,
	[KBLargeRange] [varchar](50) NOT NULL,
	[KBHugeRange] [varchar](50) NOT NULL,
	[MBSmallRange] [varchar](50) NOT NULL,
	[MBMediumRange] [varchar](50) NOT NULL,
	[MBLargeRange] [varchar](50) NOT NULL,
	[MBHugeRange] [varchar](50) NOT NULL,
	[GBSmallRange] [varchar](50) NOT NULL,
	[GBMediumRange] [varchar](50) NOT NULL,
	[GBLargeRange] [varchar](50) NOT NULL,
	[GBHugeRange] [varchar](50) NOT NULL,
	[TBSmallRange] [varchar](50) NOT NULL,
	[TBMediumRange] [varchar](50) NOT NULL,
	[TBLargeRange] [varchar](50) NOT NULL,
	[TBHugeRange] [varchar](50) NOT NULL
) ON [PRIMARY];

--Generate the ranges and populate the dimension table
WITH keys
AS
(
	SELECT
		  TOP (@Increments+1)
		  (ROW_NUMBER() OVER (ORDER BY o1.[object_id])) - 1 AS StorageKey
	-------------
	--IMPORTANT: Your database needs enough objects (in sys.objects) to be able to generate millions of rows..
	--				...and hence the 3 CROSS JOINS to sys.objects
	-------------
	FROM (SELECT [object_id] FROM sys.objects) AS o1
		CROSS JOIN (SELECT [object_id] FROM sys.objects) AS o2
		CROSS JOIN (SELECT [object_id] FROM sys.objects) AS o3
		CROSS JOIN (SELECT [object_id] FROM sys.objects) AS o4
	ORDER BY o1.[object_id]
)
INSERT INTO
	Dim.StorageDimension
SELECT
	rangeData.*,
	ByteRange = CASE WHEN StorageKey <=0 THEN '[N/A]'  					WHEN StorageKey >= @Increments THEN '[MAX]'
					ELSE LTRIM(CAST(BeginBytes AS NVARCHAR(15))) + ' Bytes to ' + LTRIM(CAST(EndBytes AS NVARCHAR(15))) + ' Bytes'
				END,
	KBSmallRange = CASE WHEN StorageKey <=0 THEN '[N/A]'  					WHEN StorageKey >= @Increments THEN '[MAX]'
					ELSE LTRIM(CAST(BeginKBSmallRange AS NVARCHAR(15))) + ' KB to ' + LTRIM(CAST(EndKBSmallRange AS NVARCHAR(15))) + ' KB'
				END,
	KBMediumRange = CASE WHEN StorageKey <=0 THEN '[N/A]'  					WHEN StorageKey >= @Increments THEN '[MAX]'
					ELSE LTRIM(CAST(BeginKBMediumRange AS NVARCHAR(15))) + ' KB to ' + LTRIM(CAST(EndKBMediumRange AS NVARCHAR(15))) + ' KB'
				END,
	KBLargeRange = CASE WHEN StorageKey <=0 THEN '[N/A]'  					WHEN StorageKey >= @Increments THEN '[MAX]'
					ELSE LTRIM(CAST(BeginKBLargeRange AS NVARCHAR(15))) + ' KB to ' + LTRIM(CAST(EndKBLargeRange AS NVARCHAR(15))) + ' KB'
				END,
	KBHugeRange = CASE WHEN StorageKey <=0 THEN '[N/A]'  					WHEN StorageKey >= @Increments THEN '[MAX]'
					ELSE LTRIM(CAST(BeginKBHugeRange AS NVARCHAR(15))) + ' KB to ' + LTRIM(CAST(EndKBHugeRange AS NVARCHAR(15))) + ' KB'
				END,
	MBSmallRange = CASE WHEN StorageKey <=0 THEN '[N/A]'  					WHEN StorageKey >= @Increments THEN '[MAX]'
					ELSE LTRIM(CAST(BeginMBSmallRange AS NVARCHAR(15))) + ' MB to ' + LTRIM(CAST(EndMBSmallRange AS NVARCHAR(15))) + ' MB'
				END,
	MBMediumRange = CASE WHEN StorageKey <=0 THEN '[N/A]'  					WHEN StorageKey >= @Increments THEN '[MAX]'
					ELSE LTRIM(CAST(BeginMBMediumRange AS NVARCHAR(15))) + ' MB to ' + LTRIM(CAST(EndMBMediumRange AS NVARCHAR(15))) + ' MB'
				END,
	MBLargeRange = CASE WHEN StorageKey <=0 THEN '[N/A]'  					WHEN StorageKey >= @Increments THEN '[MAX]'
					ELSE LTRIM(CAST(BeginMBLargeRange AS NVARCHAR(15))) + ' MB to ' + LTRIM(CAST(EndMBLargeRange AS NVARCHAR(15))) + ' MB'
				END,
	MBHugeRange = CASE WHEN StorageKey <=0 THEN '[N/A]'  					WHEN StorageKey >= @Increments THEN '[MAX]'
					ELSE LTRIM(CAST(BeginMBHugeRange AS NVARCHAR(15))) + ' MB to ' + LTRIM(CAST(EndMBHugeRange AS NVARCHAR(15))) + ' MB'
				END,
	GBSmallRange = CASE WHEN StorageKey <=0 THEN '[N/A]'  					WHEN StorageKey >= @Increments THEN '[MAX]'
					ELSE LTRIM(CAST(BeginGBSmallRange AS NVARCHAR(15))) + ' GB to ' + LTRIM(CAST(EndGBSmallRange AS NVARCHAR(15))) + ' GB'
				END,
	GBMediumRange = CASE WHEN StorageKey <=0 THEN '[N/A]'  					WHEN StorageKey >= @Increments THEN '[MAX]'
					ELSE LTRIM(CAST(BeginGBMediumRange AS NVARCHAR(15))) + ' GB to ' + LTRIM(CAST(EndGBMediumRange AS NVARCHAR(15))) + ' GB'
				END,
	GBLargeRange = CASE WHEN StorageKey <=0 THEN '[N/A]'  					WHEN StorageKey >= @Increments THEN '[MAX]'
					ELSE LTRIM(CAST(BeginGBLargeRange AS NVARCHAR(15))) + ' GB to ' + LTRIM(CAST(EndGBLargeRange AS NVARCHAR(15))) + ' GB'
				END,
	GBHugeRange = CASE WHEN StorageKey <=0 THEN '[N/A]'  					WHEN StorageKey >= @Increments THEN '[MAX]'
					ELSE LTRIM(CAST(BeginGBHugeRange AS NVARCHAR(15))) + ' GB to ' + LTRIM(CAST(EndGBHugeRange AS NVARCHAR(15))) + ' GB'
				END,
	TBSmallRange = CASE WHEN StorageKey <=0 THEN '[N/A]'  					WHEN StorageKey >= @Increments THEN '[MAX]'
					ELSE LTRIM(CAST(BeginTBSmallRange AS NVARCHAR(15))) + ' TB to ' + LTRIM(CAST(EndTBSmallRange AS NVARCHAR(15))) + ' TB'
				END,
	TBMediumRange = CASE WHEN StorageKey <=0 THEN '[N/A]'  					WHEN StorageKey >= @Increments THEN '[MAX]'
					ELSE LTRIM(CAST(BeginTBMediumRange AS NVARCHAR(15))) + ' TB to ' + LTRIM(CAST(EndTBMediumRange AS NVARCHAR(15))) + ' TB'
				END,
	TBLargeRange = CASE WHEN StorageKey <=0 THEN '[N/A]'  					WHEN StorageKey >= @Increments THEN '[MAX]'
					ELSE LTRIM(CAST(BeginTBLargeRange AS NVARCHAR(15))) + ' TB to ' + LTRIM(CAST(EndTBLargeRange AS NVARCHAR(15))) + ' TB'
				END,
	TBHugeRange = CASE WHEN StorageKey <=0 THEN '[N/A]'  					WHEN StorageKey >= @Increments THEN '[MAX]'
					ELSE LTRIM(CAST(BeginTBHugeRange AS NVARCHAR(15))) + ' TB to ' + LTRIM(CAST(EndTBHugeRange AS NVARCHAR(15))) + ' TB'
				END
FROM
(
	SELECT
		keyData.*,
		--BeginMB = (@IncrementMB * StorageKey) - (@IncrementMB),
		--EndMB = (@IncrementMB * StorageKey),
		------------------------------------------------------
		BeginKBSmallRange = KB - (KB  % @RangeKBSmall),
		EndKBSmallRange = (KB + @RangeKBSmall) - (KB  % @RangeKBSmall),
		BeginKBMediumRange = KB - (KB  % @RangeKBMedium),
		EndKBMediumRange = (KB + @RangeKBMedium) - (KB  % @RangeKBMedium),
		BeginKBLargeRange = KB - (KB  % @RangeKBLarge),
		EndKBLargeRange = (KB + @RangeKBLarge) - (KB  % @RangeKBLarge),
		BeginKBHugeRange = KB - (KB  % @RangeKBHuge),
		EndKBHugeRange = (KB + @RangeKBHuge) - (KB  % @RangeKBHuge),
		------------------------------------------------------
		BeginMBSmallRange = MB - (MB  % @RangeMBSmall),
		EndMBSmallRange = (MB + @RangeMBSmall) - (MB  % @RangeMBSmall),
		BeginMBMediumRange = MB - (MB  % @RangeMBMedium),
		EndMBMediumRange = (MB + @RangeMBMedium) - (MB  % @RangeMBMedium),
		BeginMBLargeRange = MB - (MB  % @RangeMBLarge),
		EndMBLargeRange = (MB + @RangeMBLarge) - (MB  % @RangeMBLarge),
		BeginMBHugeRange = MB - (MB  % @RangeMBHuge),
		EndMBHugeRange = (MB + @RangeMBHuge) - (MB  % @RangeMBHuge),
		------------------------------------------------------
		BeginGBSmallRange = GB - (GB  % @RangeGBSmall),
		EndGBSmallRange = (GB + @RangeGBSmall) - (GB  % @RangeGBSmall),
		BeginGBMediumRange = GB - (GB  % @RangeGBMedium),
		EndGBMediumRange = (GB + @RangeGBMedium) - (GB  % @RangeGBMedium),
		BeginGBLargeRange = GB - (GB  % @RangeGBLarge),
		EndGBLargeRange = (GB + @RangeGBLarge) - (GB  % @RangeGBLarge),
		BeginGBHugeRange = GB - (GB  % @RangeGBHuge),
		EndGBHugeRange = (GB + @RangeGBHuge) - (GB  % @RangeGBHuge),
		------------------------------------------------------
		BeginTBSmallRange = TB - (TB  % @RangeTBSmall),
		EndTBSmallRange = (TB + @RangeTBSmall) - (TB  % @RangeTBSmall),
		BeginTBMediumRange = TB - (TB  % @RangeTBMedium),
		EndTBMediumRange = (TB + @RangeTBMedium) - (TB  % @RangeTBMedium),
		BeginTBLargeRange = TB - (TB  % @RangeTBLarge),
		EndTBLargeRange = (TB + @RangeTBLarge) - (TB  % @RangeTBLarge),
		BeginTBHugeRange = TB - (TB  % @RangeTBHuge),
		EndTBHugeRange = (TB + @RangeTBHuge) - (TB  % @RangeTBHuge)
	FROM
	(
		SELECT
			StorageKey,
			BeginBytes = (@IncrementMB * StorageKey * 1024 * 1024) - (@IncrementMB * 1024 * 1024),
			EndBytes = (@IncrementMB * StorageKey * 1024 * 1024),
			Bytes = (@IncrementMB * StorageKey * 1024 * 1024),
			KB = (@IncrementMB * StorageKey * 1024),
			MB = @IncrementMB * StorageKey,
			GB = (@IncrementMB * StorageKey / 1024),
			TB = (@IncrementMB * StorageKey / 1024 / 1024)
		FROM
			Keys
	) keyData
) rangeData;

GO

/****** Object:  Index [StorageDimension_BeginAndEndBytes_NCUIDX01]    Script Date: 10/10/2016 2:47:37 PM ******/
CREATE UNIQUE NONCLUSTERED INDEX [StorageDimension_BeginAndEndBytes_NCUIDX01] ON [Dim].[StorageDimension]
(
	[BeginBytes] ASC,
	[EndBytes] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON)
GO

You can slice and dice in ranges that are in Bytes, KB’s, MB’s, GB’s and TB’s. The underlying ugliness is needed to support the pretty BI front-end that will show no signs of this madness.

Fair Warning: Your database needs enough objects (in sys.objects) to be able to generate millions of rows..

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s