Errors in the OLAP storage engine

Dear all

I have one table 'Sales' as dimension in a cube for an analysis. In that table one column exist ' DateofOrder'. I want to generate the analysis as Year, Quarter, Month like that using the column DateOfOrder....

What I have to do

Please help ...

Hi,

You can ceate an attribute as DateOfOrder with composite columns Year, Quater, Month, and define the key attribute columns for this attribute as well. (in this case, you could define the key columns as year, quater, month).

Regards,

|||

Thank a lot Helen..

But please help me how to can I create an attrrbute in a dimension 'Sales' for orderdate

|||

Go to the dimension in the data source view, if you are using Analysis Services 2005, and add new named calculation(right click on the dimension table). You use TSQL time functions like YEAR(MyDateColumn), QUARTER(MyDateColumn) and DATEPART(mm,MyDateColumn) to create these levels.

HTH

Thomas Ivarsson

|||

The name of the table 'Sales' does not sound like a "dimension table". Did you mean "Sales" is your Fact table, which is the source of the measure group? What other columns in Sales table would be the source of attributes in the dimension?

Suppose my suspecion is right and Sales is your fact table, then...

The easiest way is to use Server Time generated dimension. Start creating new dimension. The dimension wizard will show up. Select "Build the dimension without data source", navigate to the next page of the wizard. Select "Server time dimension". After that follow the wizard to specify which attributes your dimension will have. Once you have your time dimension you need to relate it to your measure group. Go to Dimension Usage panel of the Cube Designer and click the cell at the intersection of your generated Time dimension and Sales measure group. In the dialog box, which will show up, select Regular relationship and select DateOfOrder column as a measure group column.

If my guess is not right and you indeed need to have a dimension out of Sales table then you need to follow the suggestions you already got.

|||

Sir,

Many Thanks to u sir.... Many thanks ... ..

With regards

Polachan

|||

Error 4

When I am deploying the project I got the following error. Please help me sir

Errors in the OLAP storage engine: An error occurred while processing the 'Product Tran Header' partition of the 'Product Tran Header' measure group for the 'Test' cube from the productreport database. 0 0

I did the following steps

1. added new measure selecting new source table

2. selected one column dateoforder

3. Add new dimension as without using data source

4. selected server time dimension

5. Selected Year/Month/Quarter/date

6 Selected fiscal year

7.Selected dimension usage in cube desgn

8. selected time dimension and selected regular relation, Granulary attribute as Date

9. Measure group table 'Product tran header' new measure group

10 selected measure group column as dateoforder.

after that while deploying the above mentioned error will come

Please help

|||

There are 2 things. Did i understand right that on the step 2 you selected [dateoforder] column as a source of measure? What is your measure? Is it count of transactions? If you measure something like [Order Amount] then you nees to drag [Order Amount] column onto measure group tree view and not [dateoforder]. This column will just be used later in Dimension Usage step, which you made right in step 10.

If you open your server generated dimension in the designer and select tree node related to the dimension itself you can browse the properties of that dimension. There is one of the properties (forgot its exact name), which describes the time range of the dimension like 1/1/2000 - 12/31/2005. Please make sure that this range covers all the instances of [dateoforder] from your fact table.

|||

Dear Sir,

I wil explain what I need

I have one table 'Transactioheader' with the following column

1. id

2. DateofTransaction

Another table with the following column

1. headerid

2. qty

3. productcode

4. areacode

I wanto get the report with sqlanalyis with following format

Quarter1 Quarter2 Quarter3

Qty Qty Qty

Area

Omagh 200 10 100

Belfast 10 05 04

The Quarter 1, Quarter2, Quarter 3 have to be derived from the basis of DateofTransaction column with mapping of Servertime dimension..Please help me sir in this situation what I have to do to use servertime dimension

Thanks a lot for ur caring my queries...

Error: The MDX function ROOT failed because...

I'm having problem calculating sales for all customer minus certain occupations.

This Adventureworks query is something like what Excel 2003 produces.

Code Snippet

WITH MEMBER [Customer].[Occupation].[Exclude Occupations] AS

'AGGREGATE({

[Customer].[Occupation].&[Clerical],

[Customer].[Occupation].&[Management]

})'

MEMBER Measures.[Other Occupation Sales] as (ROOT(Customer), [Measures].[Internet Sales Amount]) - [Measures].[Internet Sales Amount]

SELECT

{Measures.[Other Occupation Sales]} ON COLUMNS,

[Product].[Category].[Category].MEMBERS ON ROWS

FROM [Adventure Works]

WHERE

([Customer].[Occupation].[Exclude Occupations])

Error: The MDX function ROOT failed because the coordinate for the 'Occupation' attribute contains a set.

If you delete this line, the query works!

[Customer].[Occupation].&[Clerical],

Does anyone know what this means? Is there another way to get Excel to formulate an equivalent query?

The problem is because there is a set in the current coordinate, and Root has problem dealing with it (although it shouldn't!). The best solution is to replace Root(Customer) with [All Customers] - which will have exactly same functionality, better performance, and won't generate the error that started this thread.|||

Yes, I replaced Root(Customer) with [Customer].[Occupation].[All Customers] and it works. The problem is that I wanted to create a general purpose calculated measure that would override every attribute in the customer dimension with "All". I thought Root did precisely that. If I can't use Root() then I will need to remember to update this calculation every time an attribute is added or removed from the customer dimension.

Thanks for the input.

|||

> The problem is that I wanted to create a general purpose calculated measure that would override every attribute in the customer dimension with "All"

[All Customers] does exactly that ! There is no need to update your calculation every time new attribute is added, [All Customers] (in any hierarchy of customers dimension) will automatically move coordinate to All member in every attribute.

|||

Try my AdventureWorks repro with [All Customers] instead of Root(). It doesn't work. It doesn't replace the aggregate set of occupations with the All tuple. I end up with zeros in the results because [All Customers] is doing absolutely nothing for me.

The only way I got it to work was with [Customer].[Occupation].[All Customers]. This member does, in fact, replace my aggregate set of occupations.

Put that in your pipe and smoke it!

I'm curious how [All Customers] is defined to work (vs. Root()).

|||Of course - you are right. I don't know what I was thinking. What I said is only true when dimension has single hierarchy which includes all attributes, which is obviously not the case for most of the dimensions, especially Customer.|||Thanks for the help. At least I have a work-around for now. It would be nice if Root(dimension) did the same thing as change coordinates to [All Customers] on every attribute. Maybe in the next version...