Cross-tab the same dimension

Last Post 07 Feb 2008 01:18 AM by zigge. 8 Replies.
AddThis - Bookmarking and Sharing Button Printer Friendly
  •  
  •  
  •  
  •  
  •  
Sort:
PrevPrev NextNext
You are not authorized to post a reply.
Author Messages
zigge
New Member
New Member

--
17 Jan 2008 08:05 AM
Hi,

Is it possible and if so what is the best solution for the following scenario.

I have an app/database where the the dimensions are dynamic - the most important dimensions are questions and answers from surveys.

I would like to compare the question dimension on it self. For ex. of the respondents who answered
1 on question 1, 3 answered 3 on question 2.

Thankful for any help!

Rgds Anders
SQLUSA
New Member
New Member

--
17 Jan 2008 11:00 AM
Make the question one dimension (rows), make the answer the other dimension (columns).

Make the data the COUNT .


Kalman Toth - Database, Data Warehouse & Business Intelligence Architect
SQLUSA: http://www.sqlusa.com/orderbi/ The Best SQL Server 2005 Training in the World!
zigge
New Member
New Member

--
18 Jan 2008 02:07 AM
Thanks for the response,

But, what I really want to do is this:

Resp=respondent
q=question
ao=answer option

Two respondents who have answered like:
Resp 1 q1:ao1 q2:ao2 q3:ao1
Resp 2 q1:ao1 q2:ao1 q3:ao1

I have 2 dimensions: question and answer option
1 measure which is a count.

I want to get the following cross-tab matrix:
the questions and answer options in this case are the same on both rows and cols, but they can also be
different ex rows q1, q2, cols q4, q5. But the thing I cant solve is that question as well as answer option on
both rows and cols are the same dimension...

.........................q1......................q2....................q3...
.................ao1.......ao2.........ao1......ao2.......ao1.....ao2
q1.....ao1.......x.........x...........1.........1...........2.........0
.........ao2......x.........x...........0.........0...........0.........0
q2.....ao1.......1.........0...........x.........x...........1.........0
........ao2.......1.........0...........x.........x...........1.........0
q3....ao1........2.........0...........1.........1...........x.........x
........ao2.......0.........0...........0.........0...........x.........x


Regards Zigge
SQLUSA
New Member
New Member

--
18 Jan 2008 06:21 AM
You can do this, but you get a meaningless pattern.

Put the questions across the columns (horizontal).

Put the answers over the rows (vertical).

That gives you a meaningful crosstab.

Kalman Toth - Database, Data Warehouse & Business Intelligence Architect
SQLUSA: http://www.sqlusa.com/order2005repo...gservices/ The Best SQL Server 2005 Training in the World!
SQLUSA
New Member
New Member

--
22 Jan 2008 12:22 PM
Normaly, you put

over columns: DimA level1 (level2....), DimB

over rows: DimX level1, (level2....), Dim Y.......

Putting the same dimension over columns and rows does not give you any insight into data.

Kalman Toth - Database, Data Warehouse & Business Intelligence Architect
SQLUSA: http://www.sqlusa.com/order2005repo...gservices/ The Best SQL Server 2005 Training in the World!
zigge
New Member
New Member

--
07 Feb 2008 01:18 AM
Hi, thanks for the responses

SQLUSA:
I know this is the normal way of doing it but this does not give me any meaning in this case.

I understand that cross-tabbing the exact same dimension on itself does not give any meaning like
question 1 on question 1, thats why I have marked these with an x in my example matrix. But what I mean is that my question dimesions are the rows in for ex. a tQuestions table. Having the questions in separate tables (which would solve things) is not an option because there can be thousands+
of them.

One thing I have tried is to have two question tables with the exact same data, or split the question
data in two tables which I fill with questions that I now I want to be able to compare agains eachother, but
have not succeeded with this.

inik, I see what you mean but how would I do this in practise?

Rgds Zigge
SQLUSA
New Member
New Member

--
07 Feb 2008 04:35 AM
>Having the questions in separate tables (which would solve things) is not an option because there can be thousands+ of them.


I am missing something. SQL Server 2000/2005 can handle MILLIONS of QUESTIONS.

Normally when you build a data warehouse you separate data into fact tables and dimension tables.

The dimension tables can be as low as 2 rows, as large as 20,000 (you can go higher, but performance issues arise).

The fact table is usually millions of rows (this is the big one).

Kalman Toth - Database, Data Warehouse & Business Intelligence Architect
SQLUSA: http://www.sqlusa.com/order2005repo...gservices/ The Best SQL Server 2005 Training in the World!
SwePeso
New Member
New Member

--
18 Feb 2008 03:51 AM
SQLUSA
New Member
New Member

--
20 Feb 2008 08:31 AM
I tried to drop the SAME DIMENSION over the rows, then across the columns.

The Cube browser did not even allow that.

Can you give us your dimension and fact tables?

We maybe be able to help you better.

Cognos PowerPlay allows it, but all you get is diagonal numbers repeating.

Kalman Toth - Database, Data Warehouse & Business Intelligence Architect
SQLUSA: http://www.sqlusa.com/BI2005WorkShop/ The Best SQL Server 2005 Training in the World!
You are not authorized to post a reply.

Acceptable Use Policy