Hitachi Consulting BI Blog

Adding a Where Clause to a Partition

When you create a cube, the designer creates a default partition that references the fact table. If you’re going to add more partitions, each of which points to a different fact table, it’s easy—you just add the partition and select the fact table. But what if you want to have multiple cube partitions coming from a single fact table.

In AS2K, you could add a Slice. The Slice would then create a Where clause on the fact table query (and make queries more efficient by not searching a partition that has no relevant data). In Yukon, you don’t have to create an explicit slice, because the engine automatically figures out which partitions contain which slice(s).

So you need to add an explicit Where clause. But you can’t because when you try to edit the partition, it doesn’t get to Page 2, where you can type in the Where clause because it tells you that the desired fact table is already used.

So how do you change a partition from a table to a query? Simple answer: you don’t. Instead, delete the existing partition and create a new one. The new one lets you pick the fact table (because it’s not used yet) and then you can get to Page 2 where you can enter the Where clause. You can add additional partitions the same way, because it lets you select a fact table if existing partitions use a query against the table.

In a warped kind of way, it all makes sense.

Reed