cube partitions strategy

Hi,
I have a product dimension with the following hierarchy:
Category->Brand->Type->...->UPC
There are 10 category items in the hierarchy. In every update, we will add some new products under 1 or 2 categories, which will cause rebuilding the whole cube, which takes very long time.
Can I partition the cube along the category level so that only the affected partition needs to be rebuild in each update?
or what's better partition strategy here?
Thanks for your attention.
That's probably what I would do, cut the partition the along categories
so only affected category will be reprocessed, then create a virtual
cube on top so from users' point of view, there's one cube.
Or you can incrementally update your dimension but it's more complicated
because you have to be careful about the members and calculated members
to avoid double count.
Eric
yongliyang wrote:

> Hi,
> I have a product dimension with the following hierarchy:
> Category->Brand->Type->...->UPC
> There are 10 category items in the hierarchy. In every update, we will add some new products under 1 or 2 categories, which will cause rebuilding the whole cube, which takes very long time.
> Can I partition the cube along the category level so that only the affected partition needs to be rebuild in each update?
> or what's better partition strategy here?
> Thanks for your attention.
Eric Li
SQL DBA
MCDBA

cube partitions strategy

Hi,
I have a product dimension with the following hierarchy:
Category->Brand->Type->...->UPC
There are 10 category items in the hierarchy. In every update, we will add
some new products under 1 or 2 categories, which will cause rebuilding the w
hole cube, which takes very long time.
Can I partition the cube along the category level so that only the affected
partition needs to be rebuild in each update?
or what's better partition strategy here?
Thanks for your attention.That's probably what I would do, cut the partition the along categories
so only affected category will be reprocessed, then create a virtual
cube on top so from users' point of view, there's one cube.
Or you can incrementally update your dimension but it's more complicated
because you have to be careful about the members and calculated members
to avoid double count.
Eric
yongliyang wrote:

> Hi,
> I have a product dimension with the following hierarchy:
> Category->Brand->Type->...->UPC
> There are 10 category items in the hierarchy. In every update, we will ad
d some new products under 1 or 2 categories, which will cause rebuilding the
whole cube, which takes very long time.
> Can I partition the cube along the category level so that only the affecte
d partition needs to be rebuild in each update?
> or what's better partition strategy here?
> Thanks for your attention.
Eric Li
SQL DBA
MCDBA

cube design/MDX for Product Owns vs does not Own

I would really appreciate if you could give us your input for following design, I am having hard time to visualize the cube design.

I have a fact table and dimension tables as follows with rows.

CREATE TABLE [dbo].[Fact_ProductOwnership](
[OrgID] [int] NULL,
[SegmentID] [int] NULL,
[SubSegmentID] [int] NULL,
[ProductID] [int] NOT NULL
) ON [PRIMARY]

OrgID SegmentID SubSegmentID ProductID 1 1 1 2 1 1 1 3 1 1 1 6 2 2 2 2 2 2 2 3 2 2 2 6 3 1 1 1 3 1 1 3 3 1 1 5

Segment table rows

CREATE TABLE [dbo].[Segment](
[ID] [int] NULL,
[SegmentName] [varchar](20) NULL
) ON [PRIMARY]

ID SegmentName

1 Enterprize

2 Small Business

SubgSegment table and Rows

CREATE TABLE [dbo].[SubSegment](
[ID] [int] NULL,
[SubSegmentName] [varchar](20) NULL
) ON [PRIMARY]

ID SubSegmentName

1 Enterprize

2 Small Business

Product table and Rows

CREATE TABLE [dbo].[Products](
[ID] [int] IDENTITY(1,1) NOT NULL,
[ProductDesc] [varchar](20) NULL
) ON [PRIMARY]

ID ProductDesc

1 Access_Yes

2 Access_No

3 Excel_Yes

4 Excel_No

5 SqlServer_Yes

6 SqlServer_No

What I would like achieve from above design is to get the list orgs for the following scenarios

1) Show the companies who owns Access_Yes & Excel_No

Above should display 2 orgs (1 and 2 qualify for this scenario)

2) Show the companies who owns SqlServer_yes & SqlServer_Ye

Above query should display 0 orgs(no org qualifies)

Could you please suggest me thtat is my above design wrong or is it too hard to ahieve from cube? Is there any alogorithm this can solve above problem ?

I would really appreciate if you could answer to above problem ?

You can design your cube with dimensions for Org and Product (and optionally Segment and SubSegment) and then create a row count measure. Then you would query for Orgs and use NONEMPTYCROSSJOIN to filter by products.

Something like:

SELECT NONEMPTYCROSSJOIN(NONEMPTYCROSSJOIN([Org].Members, {[Product].[Access_Yes]}, 1) {[Product].[Excel_No], 1) ON COLUMNS,

{} ON ROWS

FROM [MyCube]

Cube Count Question

Hi,
I have the following data in table, OrderNO and Product
OrderNO Product
2004001 Coca Cola
Sprite
7-UP
2004002 Coca Cola
Sprite
1. By Order, I could count 2 Orders
2. By Product, I could count 5 Products
3. By Brand, I wish could count 3 Brands!
So, is any function or anyway could get result 3 (3 Brands) in Cube of
Analysis Services?
Thanks!
Angiangi,
I don't know about Analysis Services, but a select distinct Product from
yourtable will show you a unique list of products.
--
Mark Allison, SQL Server MVP
http://www.markallison.co.uk
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
angi wrote:
> Hi,
> I have the following data in table, OrderNO and Product
> OrderNO Product
> 2004001 Coca Cola
> Sprite
> 7-UP
> 2004002 Coca Cola
> Sprite
> 1. By Order, I could count 2 Orders
> 2. By Product, I could count 5 Products
> 3. By Brand, I wish could count 3 Brands!
> So, is any function or anyway could get result 3 (3 Brands) in Cube of
> Analysis Services?
> Thanks!
> Angi
>|||If you are looking for a query
select distinct product from yourtable
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"angi" <angi@.microsoft.com> wrote in message
news:%23w9GvBrfEHA.644@.tk2msftngp13.phx.gbl...
> Hi,
> I have the following data in table, OrderNO and Product
> OrderNO Product
> 2004001 Coca Cola
> Sprite
> 7-UP
> 2004002 Coca Cola
> Sprite
> 1. By Order, I could count 2 Orders
> 2. By Product, I could count 5 Products
> 3. By Brand, I wish could count 3 Brands!
> So, is any function or anyway could get result 3 (3 Brands) in Cube of
> Analysis Services?
> Thanks!
> Angi
>|||Thanks Mark and Wayne,
1. So, my requirement can't implement in AS cube?
In AS " Cube Edit ", there is a " Distinct Count " aggreagate function
(in Basic tag of Properties).
How to use this Distinct Count function? is any restrict during use this
function?
2. My requirement can implement in MDX query?
If can be, how should I modify this syntax?
3. Here I provide some information of Distinct Count aggregate function from
"Fast Track to MDX" this book.
In Chapter 12, page 152 write..
--
Distinct Count is an unusual function because it can only be used by a
measure.
It's used in measures to produce an aggregated value and it does this by
aggregating all instances of the same entity
in order to ascertain the number of unique values.
--
I dont understand what's real mean in second sentence, any idea?
Thanks
Angi
"angi" <angi@.microsoft.com> ¼¶¼g©ó¶l¥ó·s»D
:#w9GvBrfEHA.644@.tk2msftngp13.phx.gbl...
> Hi,
> I have the following data in table, OrderNO and Product
> OrderNO Product
> 2004001 Coca Cola
> Sprite
> 7-UP
> 2004002 Coca Cola
> Sprite
> 1. By Order, I could count 2 Orders
> 2. By Product, I could count 5 Products
> 3. By Brand, I wish could count 3 Brands!
> So, is any function or anyway could get result 3 (3 Brands) in Cube of
> Analysis Services?
> Thanks!
> Angi
>