I have an issue that i face every time i decide to build a cube, and i haven't found a way to over come it yet.
the issue is how to allow the user to define Range of things automatically without having the need to hard code them in the dimension.
i will explain my problem in an example
i have a table call
and this is the Data in the table
Now if i want to display the data in a Pivot style and group up the Salary and Age in Defined ranges like below
i write this script and define the Ranges
SELECT [CustId] ,[CustName] ,[Age] ,[Salary] ,[SalaryRange] = case when cast(salary as float) <= 500 then '0 - 500' when cast(salary as float) between 501 and 1000 then '501 - 1000' when cast(salary as float) between 1001 and 2000 then '1001 - 2000' when cast(salary as float) > 2000 then '2001+' end, [AgeRange] = case when cast(age as float) < 15 then 'below 15' when cast(age as float) between 15 and 19 then '15 - 19' when cast(age as float) between 20 and 29 then '20 - 29' when cast(age as float) between 30 and 39 then '30 - 39' when cast(age as float) >= 40 then '40+' end FROM [Customers] GO
Now you see my ranges are hard coded and defined. and when i copy the data to excel sheet and view it in pivot table it appear like below
My Problem is when i want to create a Cube by converting the
Customers table into a Fact table and creating 2 dimension tables
SalaryDim table has 2 columns
(SalaryKey,SalaryRange) and the
AgeDim table is similar as well
customer fact table has
Customer [CustId] [CustName] [AgeKey] --> foreign Key to AgeDim [Salarykey] --> foreign Key to SalaryDim
I still have to define my ranges inside these dimensions and every time i connect excel pivot to my cube, i can only see these hardcoded defined ranges.
My question is how to Define Ranges dynamically from the pivot table directly, without creating Their Range Dimensions like
AgeDim Range or
and i dont want to only be stuck to the ranges defined in the dimension
Now the range defined is
'0-25' , '26-30' , '31- 50'
if i want to change it to
'0-20', '21-31' , '32-42'
and so on, and users request different ranges every time
every time i change it i have to change the dimension, how can i improve this process