Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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 Customers

Table Structure

and this is the Data in the table

Table with data

Now if i want to display the data in a Pivot style and group up the Salary and Age in Defined ranges like below

Table with Data with Defined Range

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

Data in Pivot Table

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 & AgeDim

the SalaryDim table has 2 columns (SalaryKey,SalaryRange) and the AgeDim table is similar as well (ageKey,AgeRange) and my 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 SalaryDim Range and i dont want to only be stuck to the ranges defined in the dimension

No Range Defined

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

share|improve this question
1  
Is the goal to get this right in Excel or to get it right in the Cube? –  RBarryYoung Jul 1 '12 at 15:10
    
It would be great if u have a solution suggestion for a design in the cube, so that whatever BI client tool that connects to the cube can define the ranges, but i wouldn't mind if you have a good way in excel only –  AmmarR Jul 1 '12 at 15:24
add comment

3 Answers

up vote 9 down vote accepted
+150

HOW TO DO THIS WITH T-SQL:

As requested this is an alternative to my previous answer that showed how to do it per-user with Excel. This answer shows how to do the same thing shared/centrally using T-SQL instead. I do not know how to do Cubes, MDX or the SSAS stuff for this, so maybe Benoit or someone who does know that can post its equivalent...

1. Add SalaryRanges SQL Table and View

Create a new table called "SalaryRangeData" with the following command:

Create Table SalaryRangeData(MinVal INT Primary Key)

Add calculated columns by wrapping it in a View with this command:

CREATE VIEW SalaryRanges As
WITH
  cteSequence As
(
    Select  MinVal,
            ROW_NUMBER() OVER(Order By MinVal ASC) As Sequence
    From    SalaryRangeData
)
SELECT 
    D.Sequence,
    D.MinVal,
    COALESCE(N.MinVal - 1, 2147483645)  As MaxVal,
    CAST(D.MinVal As Varchar(32))
    + COALESCE(' - ' + CAST(N.MinVal - 1 As Varchar(32)), '+')
                        As RangeVals
FROM        cteSequence As D 
LEFT JOIN   cteSequence As N ON N.Sequence = D.Sequence + 1

Right-click on the table in SSMS and select "Edit Top 200 Rows". Then enter the following values into the MinVal cells: 0, 501, 1001, and 2001 (order doesn't matter for SQL Server, it will create it for us). Close the table-row editor and do a SELECT * FROM SalaryRanges to see all of the rows and range information.

2. Add AgeRanges SQL Table and View

Do the exact same steps as in #1 above, except replace all occurrences of "Salary" with "Age". This should make the table "AgeRangeData" and the view "AgeRanges".

Enter the following values into the AgeRangeData [MinVal] column: 0, 15, 20, 30, and 40.

3. Add Ranges to The Data

Replace your SELECT statement with CASE expressions for retrieving the data and ranges with the following one:

SELECT [CustId]
      ,[CustName]
      ,[Age]
      ,[Salary]
      ,[SalaryRange] = (
            Select RangeVals From SalaryRanges
            Where [Salary] Between MinVal And MaxVal)
      ,[AgeRange] = (
            Select RangeVals From AgeRanges
            Where [Age] Between MinVal And MaxVal)
  FROM [Customers]

4. Everything Else, The Same As Now

From here on, just do everything the same as you currently are. The ranges should all show up in your PivotTable as they currently do.

5. Test The Magic

Go to the SalaryRangeData table-row editor in SSMS again and delete the existing rows and then insert the following values: 0, 101, 201, 301, ... 2001 (again, order doesn't matter for the T-SQL solution). Go back to your PivotTable and refresh the data. And just like the Excel solution, the PivotTable ranges should be automatically changed.


Addition

HOW ADD IT TO A CUBE:

1. Create a View

CREATE VIEW CustomerView As
SELECT [CustId]
      ,[CustName]
      ,[Age]
      ,[Salary]
      ,[SalaryRange] = (
            Select RangeVals From SalaryRanges
            Where [Salary] Between MinVal And MaxVal)
      ,[AgeRange] = (
            Select RangeVals From AgeRanges
            Where [Age] Between MinVal And MaxVal)
  FROM [Customers]

1. Create a a BI Project in Visual studio and add the CustomerView

Connect to the Database, and add the CustomerView View in the Data Source Views to be the Fact table

Data Source Views

2. Create A cube and Define Measure & Dimension

we only need customerId, as a measure for customer count and will have the same fact table as a dimension

Measures

Dimensions

3. Add Attributes to the Dimension

Add ranges as Attributes to the Dimension

4. Connect to Cube from Excel

Add SSAS source to Excel

Select the Cube

5. View the Data of the cube in the Excel

View the Cube in Excel

6. for Any changes in the Ranges just reprocess the Dimension & cube

if you need to change the Ranges, change the data in the SalaryRangeData and AgeRangeData and then just reprocess the dimensions and the cube

share|improve this answer
    
+1 hi RBarryYoung, your solution worked perfect, i did a small addition that allowed me to add it to the cube, if you allow me i will edit your post with the small addition –  AmmarR Jul 4 '12 at 6:59
    
Yes, please do! –  RBarryYoung Jul 4 '12 at 13:42
    
hi RBarryYoung, i added the method on how to add it to a cube, hope it helps, thanks for the support –  AmmarR Jul 9 '12 at 11:15
add comment

With the MDX language you can create custom members that will defines the ranges. The following expression defined a calculated member that represents all the salaries between 501 and 1000:

MEMBER [Salary].[between_500_and_1000] AS Aggregate(Filter([Salary].Members, [Salary].CurrentMember.MemberValue > 500 AND [Salary].CurrentMember.MemberValue <= 1000))

You can do the same thing with the age dimension:

MEMBER [Age].[between_0_and_25] AS Aggregate(Filter([Age].Members, [Age].CurrentMember.MemberValue <= 25))

This article explains how to add theses calculted members in Excel (see 'Creating calculated members/measures and sets in Excel 2007 OLAP PivotTables' section). Sadly there is no UI in Excel for this. Nevertheless you can find BI clients that support the MDX language, which allow to define your Ranges in the queries.

share|improve this answer
4  
Whoever downvoted this answer, please add a comment explaining why. –  Mark Storey-Smith Jul 1 '12 at 23:50
    
thanks @Benoit, am trying to add calculated fields in the cube it self with the same concept you are suggesting but i doesn't seem to work yet, the process is a bit lengthy and am not familiar with it i will try it with excel as well, –  AmmarR Jul 2 '12 at 13:12
    
+1 for addressing the SSAS/Cube side of things. Sure wish I knew this stuff... –  RBarryYoung Jul 3 '12 at 14:40
    
Thanks @RBarryYoung. @MarkStorey-Smith: I can improve the formula efficiency, if you give me the list of the levels that are in the Salary and Age dimension. –  Benoit Jul 3 '12 at 21:27
    
hi benoit, you can use the same levels or any level, the main thing is , i want to be able to change the ranges any time as per users demand, the answer by RBarryYoung is perfect if only i could link it to the cube, –  AmmarR Jul 4 '12 at 4:39
add comment

HOW TO DO THIS WITH EXCEL

Here's how I would do it in Excel...

1. Add SalaryRanges Excel Table

Insert a new worksheet, call it "Salary Ranges". In row one add the text headers "Min", "Max" and "Range" in that order (should be cells A1, A2, A3, respectively).

In cell B2 add the following formula:

=IF(A2="","",IF(A3="","+",A3-1))

In cell C2 add this formula:

=IF(B2="","",A2 & IF(B2="+",""," - ") & B2)

Autofill these two formulas down the B and C columns for the maximum number of rows that you may ever need (lets say 30).

Next, select the entire range (A1..C31). Got to the Insert tab and click the Table button to change this range into an Excel Table (these used to be called "Lists"). In the Table Tools Design tab, change the name of this table to "SalaryRanges".

Now, go to cell A2 in the Min column and enter "0", "501" in A3, "1001" in cell A4 and finally "2001" in cell A5. Note that as you do this the MAx and Range columns are automatically filled-in.

2. Add AgeRanges Excel Table

Now make another new worksheet named "Age Ranges", and do the exact same steps as in #1 above, except call this table "AgeRanges" and in the Min column fill cells A2 to A6 with 0, 15, 20, 30, and 40, in order. Again, the Max and Range values should automatically fill-in as you go.

3. Get the Data

Get the data from the database into your Excel workbook just as you did before (do not make the PivotTable yet, we do that below), except you should remove the AgeRange and SalaryRange case function columns.

4. Add the Salary and Age Range columns to your Data

In the sheet where your data is, add a "SalaryRange" and "AgeRange" column. In the SalaryRange column, autofill the following formula (assumes that "D" is the Salary column):

=LOOKUP(D2,SalaryRanges)

And autofill this formula to the AgeRange column (assuming that "C" is the Age column):

=LOOKUP(C2,AgeRanges)

5. Make your PivotTable

Do this just as you did before. Note that the Age and Salary range value/labels match the ranges that you choose.

6. Test The Magic

Now the fun part. Go to the SalaryRanges worksheet and re-enter the Min column, starting at 0, then 101, 201, 301, ... 2001. Go back to you PivotTable and just refresh it. Shazaam!


I should mention that of course you can also achieve the same effect by putting the Tables in SQL and changing your SELECT statement to do the LOOKUP(..)s as subquery's (a little messy because of the range-matching, but definitely do-able). The reason that I did it this way (in Excel) is

  1. Changing the ranges is a bit easier for most people. Even for DBA's and SQL Developers (like us), this way is a little bit easier just because it's closer to the UI/results.
  2. This allows your users to change their own ranges without having to bother you. (a BIG plus in my life)
  3. This also allows each user to define their own ranges.

However, sometimes it's actually undesirable to have users defining their own ranges. If that's the case for you, I'll be happy to demonstrate how to do it centrally, in SQL instead.

share|improve this answer
    
+1 and Thanks alot the solution works amazing, connecting the table with the data with the ranges all in excel, Is there a way to connect this defined ranges with the pivot table that is connected to the Cube, my pivots are directly connected to the cube in SSAS, and also it would be great if you can show "how to do it centrally". –  AmmarR Jul 3 '12 at 5:06
    
I can show you how to do it centrally with SQL expressions, I'll post that as an alternative answer. I cannot address the Cube/SSAS issues because unfortunately I do not know them. Yes, I should know them and I wish I did, but I do not, so someone else will have to address that. –  RBarryYoung Jul 3 '12 at 13:43
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.