Re: #ERR when trying to view detail of time dimension

Tech-Archive recommends: Fix windows errors by optimizing your registry


I am using HOLAP.

I erased the aggregations as a test only. I wanted to see if it would cause
the error. Normally I would keep the aggregations. The problem is, once I
include all of the dimensions that I would like in the cube, I can't compute
enough aggregations to prevent this error. (It takes way too long and AS
freezes)

Thanks,
Paul

"Denny Lee" wrote:

> No problem at all, just to ask a few more questions - are you using ROLAP to
> build your cube vs. MOLAP or HOLAP? I noticed that you were talking about
> "level of detail to be computed on the fly". As well, why is it necessary
> to erase the aggregations and fully reprocess the cube? In general, you
> would want to keep the aggregations within the cube?
>
> --
> HTH!
> Denny Lee
> <dennyglee_at_hotmail_dot_com>
>
> Blog at:: http://spaces.msn.com/members/denster/
>
>
>
> "pcortens" <pcortens@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message
> news:94B5397A-6E1B-4C63-AFE2-9BC3E34B439E@xxxxxxxxxxxxxxxx
> > First: Thank you very much for all your help!!!
> >
> > I tried creating an entirely new cube (also based on the same data). I
> > used
> > all new dimensions (and none of them are shared dimensions). I included no
> > distinct counts, just 3 sums. I had put in a time dimension using the
> > built
> > in wizard. I designed aggregations and processed the cube. It worked
> > perfectly. Then I erased the aggregations and reprocessed the cube. I got
> > the
> > error.
> >
> > So now I am thinking that there is some limitation that won't allow that
> > level of detail to be computed on the fly. (Could it be that I don't have
> > enough memory or processing power?)
> >
> > This makes sense because when I use the old method (pre calculated columns
> > for Year, Quarter, and Month) the server doesn't need to calculate these
> > things based on the datetime column, this is already done.
> >
> > I guess the reason I really lean towards this though is because in the
> > newest cube I built (with totally separate dimensions) works when I use
> > aggregations and doesn't when I don't. And if I calculate some
> > aggregations
> > (but not many), then it work for Quarterly but not monthly. Also, the more
> > other dimensions I add, the more aggregations I need for it to work
> > properly.
> >
> > Thanks again,
> >
> > Paul
> >
> > "Denny Lee" wrote:
> >
> >> Wow - okay, thanks for the explanation! Okay, I'm going to focus on the
> >> one
> >> portion of below
> >>
> >> > I built a dimension that was exactly the same as the original time
> >> > dimension
> >> > I had, but with a different name (because the new time dimension had
> >> > that
> >> > name) and it worked fine. To clarify, I have columns that store the
> >> > Year,
> >> > Quarter, and Month. My old time dimension used these columns (each was
> >> > a
> >> > level).
> >>
> >> Just to verify, the new time dimension but with the different name works
> >> fine. And if you go ahead and go down the year, quarter, and month
> >> hierarchy of this new time dimension with a different name, there are no
> >> problems.
> >>
> >> In that case, I would hazard to guess that somehow the original time
> >> dimension itself got corrupted. What I would do to make sure everything
> >> is
> >> okay is to delete the original time dimension altogether. And then
> >> recreate
> >> it and make sure to process the dimensions individually instead of
> >> processing them all at once. If it gets iffy, you may want to go
> >> directly
> >> into the c:\Program Files\Microsoft Analysis Services\Data\%OlapdB% and
> >> delete any of the remnant dimension files - for example, there could be
> >> S'
> >> dimension files which are temporary dimension files that should have been
> >> deleted by AS2k (this could be what's causing the corruption)
> >>
> >> --
> >> HTH!
> >> Denny Lee
> >> <dennyglee_at_hotmail_dot_com>
> >>
> >> Blog at:: http://spaces.msn.com/members/denster/
> >>
> >>
> >>
> >> "pcortens" <pcortens@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message
> >> news:36AA1D3D-22E5-482E-9794-BE22635FA2F6@xxxxxxxxxxxxxxxx
> >> > To further explain: I have a second cube that is based on exactly the
> >> > same
> >> > table. The first cube has all the dimensions that I use. This second
> >> > cube
> >> > uses only the most frequently used dimensions (so that it calculates
> >> > faster).
> >> > In this second cube, the time dimension also comes up with an error.
> >> >
> >> > Both cubes have more than one time dimension (based on different
> >> > columns).
> >> > None of them work properly when I used the built in time dimension from
> >> > the
> >> > Dimension Wizard. (All of them worked when I stored the year, quarter,
> >> > and
> >> > month data in columns and built the dimension manually.)
> >> >
> >> > I had a distinct count in both cubes. However, I removed it from the
> >> > second
> >> > cube, (and all calculated measures that depended on it). That deleted
> >> > the
> >> > aggregations. I designed new ones (up to 5-6% efficiency) and
> >> > reprocessed.
> >> > The error was the same.
> >> >
> >> > I must note: In this second cube (the smaller one with fewer
> >> > dimensions),
> >> > I
> >> > can view data down at the Quarter level on some of the time dimensions.
> >> > On
> >> > one time dimension I can actually get monthly data (but for daily I get
> >> > #ERR). Does this mean it is possible that it is a memory issue?
> >> >
> >> > I built a dimension that was exactly the same as the original time
> >> > dimension
> >> > I had, but with a different name (because the new time dimension had
> >> > that
> >> > name) and it worked fine. To clarify, I have columns that store the
> >> > Year,
> >> > Quarter, and Month. My old time dimension used these columns (each was
> >> > a
> >> > level).
> >> >
> >> > I don't know how to delete the primary partition. I thought that
> >> > everything
> >> > was erased and rebuilt when I did a Full Process.
> >> >
> >> > I also tried creating a new time dimension using the Dimension Wizard,
> >> > this
> >> > time for Year and Week. This one would only show data at the Year
> >> > level.
> >> >
> >> > Thank you very much for all your help,
> >> >
> >> > Paul
> >> >
> >> > "Denny Lee" wrote:
> >> >
> >> >> Hmm - what happens if you do actually build some aggregations?
> >> >> Continuing
> >> >> the debug:
> >> >> > if you were to put the original time dimension back, would the cube
> >> >> > work
> >> >> > then?
> >> >>
> >> >> > are you using any distinct counts? (sometimes this comes into play
> >> >> > with
> >> >> > that type of result)
> >> >>
> >> >> > are there any other dimensions in which you get the #ERR result?
> >> >>
> >> >> > did you by any chance delete the primary partition of the cube and
> >> >> > create
> >> >> > a new one instead? (this causes a corruption for the Olap cube and
> >> >> > gives
> >> >> > you the result you are seeing).
> >> >>
> >> >> --
> >> >> HTH!
> >> >> Denny Lee
> >> >> <dennyglee_at_hotmail_dot_com>
> >> >>
> >> >> Blog at:: http://spaces.msn.com/members/denster/
> >> >>
> >> >>
> >> >>
> >> >> "pcortens" <pcortens@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message
> >> >> news:37790462-4E94-4355-B54C-64A34D7A2ECA@xxxxxxxxxxxxxxxx
> >> >> >I actually didn't calculate any aggregations after I changed the
> >> >> >dimension
> >> >> >so
> >> >> > all of the previous aggregations were deleted. Also I did a full
> >> >> > process
> >> >> > of
> >> >> > all of the shared dimensions, and then a full process of the cube.
> >> >> >
> >> >> > Thanks,
> >> >> >
> >> >> > Paul
> >> >> >
> >> >> > "Denny Lee" wrote:
> >> >> >
> >> >> >> Out of curiosity, did you rebuild the aggregations for this cube as
> >> >> >> well?
> >> >> >> Sometimes the result that you are seeing is caused by OLAP
> >> >> >> referring
> >> >> >> to
> >> >> >> the
> >> >> >> original aggregations even though you need new ones because of the
> >> >> >> swapping
> >> >> >> of dimensions (even though, in effect they are the same dimension,
> >> >> >> they
> >> >> >> are
> >> >> >> different because of the change).
> >> >> >>
> >> >> >> --
> >> >> >> HTH!
> >> >> >> Denny Lee
> >> >> >> <dennyglee_at_hotmail_dot_com>
> >> >> >>
> >> >> >> Blog at:: http://spaces.msn.com/members/denster/
> >> >> >>
> >> >> >>
> >> >> >>
> >> >> >> "pcortens" <pcortens@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message
> >> >> >> news:6EABC6D7-529A-48B8-BC3E-5DDE85D6D6CF@xxxxxxxxxxxxxxxx
> >> >> >> > Hi,
> >> >> >> > I'm a bit of a SQL Server newbie.
> >> >> >> >
> >> >> >> > I have a Cube based on transactional data, and I previously built
> >> >> >> > a
> >> >> >> > time
> >> >> >> > dimension manually by using a query to pull out the year,
> >> >> >> > quarter,
> >> >> >> > and
> >> >> >> > month
> >> >> >> > from the transaction date field. Then I just used these results
> >> >> >> > as
> >> >> >> > different
> >> >> >> > layers in the dimension. This worked fine (other than the fact
> >> >> >> > that
> >> >> >> > my
> >> >> >> > months
> >> >> >> > weren't sorted properly).
> >> >> >> >
> >> >> >> > I then switched to using the built in Time Dimension based
> >> >> >> > directly
> >> >> >> > on
> >> >> >> > the
> >> >> >> > transaction date field. So I removed the old dimension, built the
> >> >> >> > new
> >> >> >> > one,
> >> >> >> > and reprocessed the cube. Now when I try to browse the data, if I
> >> >> >> > look
> >> >> >> > at
> >> >> >> > anything other than yearly data (i.e. quarterly or monthly) then
> >> >> >> > the
> >> >> >> > cube
> >> >> >> > browser returns #ERR.
> >> >> >> >
> >> >> >> > What is causing this and how do I fix it? I am running SQL Server
> >> >> >> > 2000
> >> >> >> > Standard Edition on Windows 2003 Server Standard Edition.
> >> >> >> >
> >> >> >> > Thanks
> >> >> >>
> >> >> >>
> >> >> >>
> >> >>
> >> >>
> >> >>
> >>
> >>
> >>
>
>
>
.

Relevant Pages

  • Re: SSAS2005 - When do partitions need to be processed?
    ... My guess here is that it is not aggregations but rather indexes being ... The actual processing of partitions doesn't appear to be significant here ... Processing Cube 'Sales Current' completed successfully. ... aggregations when the dimension is updated. ...
    (microsoft.public.sqlserver.olap)
  • Re: distinct count through calculated measure = slow client process...
    ... I have 2 dcount measures in test: ... I presume I have to aggregate the time dimension at the month level to ... What's appends if I optimize to 100% the aggregations? ... > Last but not the least: create a seperate cube for just the distinct ...
    (microsoft.public.sqlserver.olap)
  • Re: Cube processing
    ... I only like to add that you can fully process a cube with aggregations, ... > structure of the dimension is the same. ... The same with a changed structure of a dimension ...
    (microsoft.public.sqlserver.olap)
  • Re: Cube data incorrect when using more than 20 aggregations
    ... Check for column names in the fact table and the dimension tables that, ... can see this in the cube editor. ... > ROLAP storage,everything comes out OK. ... > problem,because accessing cubes with little aggregations is really slow. ...
    (microsoft.public.sqlserver.olap)
  • Re: Calculating Max Usage with OLAP
    ... In that way we might benefit from aggregations that will be done for a ... dimension with two levels and 24 members. ... > your time dimension could well be part of the problem here. ...
    (microsoft.public.sqlserver.olap)