2005 Slice

Hi,

We are trying to migrate AS2000 cube to AS2005 cube. We have several AS2000 cubes using partition slices (eg. Policy_Type_Id = 4) and filters (Month_end_Date_Id = 20060630). Here's the sql it generated in 2000:

" FROM table1, table2, table3 WHERE (Month_End_Date_Id = 20060630) AND ("dbo"."Summary_Policy"."Policy_Type_Id"=?)......................

The migration generated the filter but did not create the slice for the AS2005 cube. I have done some research and understand AS2005 cubes do not use Slice if they are MOLAP. However, the partitions show different result between AS2000 cubes and AS2005 cubes (it did not slice the partition). If I manually added it to the Slice property in AS2005, it generated errors when I process the AS2005 partition.

My solution to the problem is to add the dimension table in the query binding, joins the table to the fact table, and add the slice to the where clause. It seems to be very inefficient and I don't really understand the statement extracted from the REAL project: "In SQL Server 2005, this(slice) is no longer necessary. Processing the partition now automatically builds a histogram-like structure in the MOLAP storage. This structure identifies which members from all dimensions are included in the partition. Thus, so long as the storage method is MOLAP, the data slice is an optional (and unused) property".

Could you please tell what the best practices for AS2005 partition needs to slice/filter by the dimension member? Did my solution make sense?

Thanks, Chih Huang

You should try and understand the reason the partition processing gave you errors when you tried to add just a slice to your partition binding.

It is probably because your relational database has some inconsistencies. You can work around using the solution you mention above, but it might be slow. See what is the perofrmance on the relational side.

Edward Melomed.
--
This posting is provided "AS IS" with no warranties, and confers no rights.

|||

Thanks for the information. I have verified the relational data and its consistencies. They are ok.

The problem is if your partition uses MOLAP and add slices to the partition, it will generate the following error when you process it.

Errors in the OLAP storage engine: The slice specified for the Policy Type 1 attribute 1 attribute is incorrect.

Is this a bug in AS2005? Without the slice, the AS2005 partition data is different from the AS2000. Is there any other way to limit the partition data based on your dimension members? Do I have to use query binding or name queries (join fact tables to dimension tables) to achieve the same result as AS2000?

The following statement does not seem to match what I have experienced with the AS2005 partition.

"Processing the partition now automatically builds a histogram-like structure in the MOLAP storage. This structure identifies which members from all dimensions are included in the partition. Thus, so long as the storage method is MOLAP, the data slice is an optional (and unused) property".

Thanks, Chih Huang

|||

It is not a bug in AS2005.

It is important to understand the difference between AS2000 and AS2005. In AS2000 you specify the slice on the partition object and that is translated to the where condition server sends to the RDBMS. In AS2005 the paradigm is different. You have the control over what query you would like Analysis Server to send when processing the partition. You can simply point partition to a single table, you can use query binding and specify the query for partition to load data from.

The partition slice serves different role in AS2005, it is there for ROLAP schenario and there for optimization purposes.

Edward Melomed.
--
This posting is provided "AS IS" with no warranties, and confers no rights.