Re: 800 Dimensions... Too many?

Great - I think I can work with this. My dimensions are all separate tables
(800+ dim tables) and my keys are all integers. I can convert all of them to
smallint (but not tinyInt - they don't all start at 0 even though no question
has more than 20 possible answers).

Thanks!
--
Bob Hodgman


"Jéjé" wrote:

yes, using separated tables for the dimensions is a first step.
I also recommand to use integer (or smallint) for the key column of the
table. (if you use string type columns now)
Like:
Answer1 Label DisplayOrder
1 Yes 1
2 No 2
3 Unknown 3

the fact table will have only these integer / smalint / tinyint... values.
this reduce the size of the cube and provides a better process performance.

Sometimes there is a logic in the questions.
Like Question 1 and Question 2 are related (Q1:"do you have a car?"
Q2:"Which model?")
in this case creating a dimension with more then 1 level (1 level by
question) will help you.

another option I have created in the past...
some questions become dimensions for classification (like gender, age...)
and some other questions are flattened and put into 1 dimension with 2
levels:
level 1: Question
Level 2: Answer

This is good when the user want to see the result of 1 question and don't
want to cross analysis multi questions at the same time.
this design could be good when all the questions uses the same answers, in
this case the Answer become 1 dimension and the question another one.


"Akshai Mirchandani [MS]" <akshaim@xxxxxxxxxxxxxxxxxxxx> wrote in message
news:O8FOIGeWGHA.3448@xxxxxxxxxxxxxxxxxxxxxxx
Yes, lets take a non-survey example:

Country
\
State
\
City Age
\ /
Customer

If this is your dimension definition, and you want to find the Sales for
Customers living in [State].[WA] in the Age group [20-30], you can just
say:

SELECT [Measures].[Sales] ON 0
FROM [Sales]
WHERE
( Customers.State.WA, Customers.Age.[20-30] )

The server will aggregate up the data from the customers that match *both*
conditions.

The same thing can be done for the survey example except that here the key
wouldn't really be a very useful attribute except for the unification of
the survey results.

I would suggest building a small sample to prove the concept and then
extend it to your full domain.

In addition, I would recommend building the dimensions from real dimension
tables where possible so that processing of the dimensions doesn't involve
the fact table -- in fact, I would also recommend having integer keys for
the yes/no type answers to improve processing speed of the partition data.

Thanks,
Akshai
--
This posting is provided "AS IS" with no warranties, and confers no rights
Please do not send email directly to this alias. This alias is for
newsgroup
purposes only.

"Bob H." <BobH@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message
news:53C671E1-135F-4157-82D3-5F91015E34F8@xxxxxxxxxxxxxxxx
The 800 dimensions represent questions on a questionaire. The answers are
all
mutiple-choice. The answers can be Yes/No/Unknown or an item from some
other
static list of possible answers (like red/green/blue/pink...).

The purpose of the cube is to allow a group of analysts to find out how
many
answered "yes" to question #95, "blue" to question #566 and "Chocolate
chip"
to question #122 (for example).

There are logical groups of questions. Would creating combined keys still
allow the full flexibility to interrogate the data?

Thanks!





--
Bob Hodgman


"Akshai Mirchandani [MS]" wrote:

My interpretation of the question on attributes was: can you group the
dimensions together into larger entities?

For example, if your 800 dimensions look a lot like:
[Flag1], [Flag2], [IsDeleted], [IsAvailable], etc.
Then can you combine all these types of attributes into somehow logical
groups and create a dimension that has a large composite key attribute
and
lots of related attributes with the actual flags?

Can you give a summary of what your 800 dimensions look like?

Thanks,
Akshai
--
This posting is provided "AS IS" with no warranties, and confers no
rights
Please do not send email directly to this alias. This alias is for
newsgroup
purposes only.

"Bob H." <BobH@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message
news:434D5089-8955-4136-94CB-5336D55971DF@xxxxxxxxxxxxxxxx
Would attributes be more efficient than dimensions? I've got this up
and
running, but the response time is slow (60 seconds to display
results).

Visual Studio (2005 - 32-bit Visual Studio on the dual Opteron x64
system)
could not deploy the cube (system out of memory!). I had to write
scripts
and
run them through xmla query windows in Management Studio.

Thanks!
--
Bob Hodgman


"Jéjé" wrote:

you are right... 800 is really huge!!!

does it's really dimensions or attributes?
how the system performs?

your problems come from the end user access, this a lot of metadata
to
play
with.


"Bob H." <BobH@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message
news:CE84563F-3528-4504-827A-FDB9EAB163B1@xxxxxxxxxxxxxxxx
...on one cube?

I'm developing in SSAS 2005 running on dual core dual Opteron 64
bit
machine.

I know. It sounds like a lot of dimensions. But sometimes the world
is
really complex and your cubes get complex, too.

Anyone have any experience with this? Anyone have any advice on
this.

--
Bob Hodgman











.

Relevant Pages

  • Re: Help creating OLAP cube - attribute key cannot be found
    ... All keys are defined as UniqueIdentifiers, a few 1-n and n-m relations exist ... The dimensions seem to be correct, ... missing keys. ... Windows Update doesn't find any updates needed, ...
    (microsoft.public.sqlserver.olap)
  • Re: Help creating OLAP cube - attribute key cannot be found
    ... dimensions and facts I want to base upon are differentiated only by a column ... All keys are defined as UniqueIdentifiers, a few 1-n and n-m relations exist ... missing keys. ... Windows Update doesn't find any updates needed, ...
    (microsoft.public.sqlserver.olap)
  • Pivottable Filtering Bug crashes IE!!
    ... dimensions for a special substring. ... After that i set that keys to ... faulting module OWC10.dll, version 10.0.2621.0, fault ... Developer name the cause or even a solution for this ...
    (microsoft.public.office.developer.web.components)
  • Re: 800 Dimensions... Too many?
    ... using separated tables for the dimensions is a first step. ... this reduce the size of the cube and provides a better process performance. ... I would recommend building the dimensions from real dimension ... Please do not send email directly to this alias. ...
    (microsoft.public.sqlserver.olap)
  • Re: 800 Dimensions... Too many?
    ... a sample of your dimensions can help us. ... what is this cube? ... Please do not send email directly to this alias. ... Bob Hodgman ...
    (microsoft.public.sqlserver.olap)