Hi,

When i try to create a new dimension (time) over a fact table quite large
(about
500,000 rows), in the 'Save' phase, after about 30 seconds Analysis Manager
stops the process sending an error. Unable to count the member of the 'Year'
level. Unable to open the record set. Error: [Microsoft] [ ODBC SQL Server
Driver] Timeout expired.

This does not happen when i process a pre-existing dimension, only when i
build a new dimension.

Does anyone knows how you can change the timeout setting ?

Thanks for your help

Alesya.

I have this problem right now, did you find a solution?

"Alesya" wrote:

Hi Paul,

I did find solution to that problem - install service Pack 3 and 3a. It
updates the timeout value in the registry to a higher number (gives it
enough time to count the rows).
Or you can update the timeout value in the registry manually. I am not
sure what exactly is the name of that registry entry.

I would highly recommend installing service pack 3a since it fixed other
bugs as well.

Good luck,
Alesya.

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!

Thanks for the reply

I have service pack 3 on, but maybe not 3a, I'll try that. Thanks

"Alesya Bajoria" wrote:

However, the underlying best practice is that you shouldn't really be doing
this if at all possible. There are many good reasons why you should always
have an independent standalong dimension table rather than using the fact
table. For example:
-- limited scaleability as dimension processing increases significantly (as
you have a SELECT DISTINCT scan over the entire fact table)
-- major problems if you start partitioning the fact table -- which is an
important tool for getting good quality, fast, linear query response time
-- you can't easily add additional metadata about the member -- for example,
suppose you built your time dimension off of a timestamp in the fact
table -- but if you had an independent dimension table, you could have
things like an indicator if the day is a weekend, company holiday, etc.
-- lots of others

--
Dave Wickert [MSFT]
dwickert (AT) online (DOT) microsoft.com
Program Manager
BI SystemsTeam
SQL BI Product Unit (Analysis Services)
--
This posting is provided "AS IS" with no warranties, and confers no rights.

"Paul" <Paul (AT) discussions (DOT) microsoft.com> wrote

Ok, I'm certainly open to best practice ideas.
I have a fact table with 140 million rows, and I'm setting up my date
dimension with levels: year, quarter, month.
How should I be going about setting up an independant dimension table? Is it
just a view off of the fact table that would SELECT DISTINCT MY_DATE, MY_ID,
or is there a better way?



"Dave Wickert [MSFT]" wrote:

The recommended approach is a separate time dimension table, populated
with all relevant dates. This paper by Tom Chester discusses why:

http://www.sqljunkies.com/Article/D1...F20D60951395.s
cuk
Conclusion

To summarize the main recommendations:

Always use a dimension table for time; do not derive the dimension from
the fact table.

Do not derive the dimension from a date/time column.

Use a meaningful key for dates, e.g., YYYYMMDD. This makes it easier to
backload historical data.

Decide in advance if the All member is meaningful; remove it, if not.

- Deepak

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!