Refresh Data In Cube

Hi,
How to refresh data for a day in a cube. I want to do this without deleting
the records from the fact tables for the specified day.
Regards,
Yash
You do this with an incremental build of the cube... You must be able to
write a sql expression which identifies WHICH rows in the fact table are
new. If you allow rows to come in twice your cube data will be inaccurate.
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
"Yash" <Yash@.discussions.microsoft.com> wrote in message
news:8909B51B-58CB-48FD-BF42-3E226354B578@.microsoft.com...
> Hi,
> How to refresh data for a day in a cube. I want to do this without
deleting
> the records from the fact tables for the specified day.
> --
> Regards,
> Yash
>

Refresh cube after data changed?

After I created a cube in BI Studio, I added more data to the fact table, modified dimension table, and refreshed DSV. I got Refresh Data Source View message ‘No changes have been found’, but I didn’t see the data that I just added in from cube browser.

(Database connecting and database structure didn’t change)

My questions:

1. Is refresh DSV only for database structure change?
2. Which step I was missing for refreshing cube data?

Thanks in advance.

Yes, refreshing is to pull database structure changes into the DSV. To get the new data, "Process" the cube.

You'll find that MSAS provides a rich set of capabilities and abilities to update the data. For now, always do a "Full Process", until you get more comfortable with the technology. Depending on the size of your cube, plan on spending a little time learning about incremental processing and partitions. For anything other than small cubes, expect to define / manage partitions are part of the definitions. The impact on performance is dramatic.

|||Thank you very much Dave.

It is very helpful information.

Refresh cube after data changed?

After I created a cube in BI Studio, I added more data to

the fact table, modified dimension table, and refreshed DSV. I got Refresh Data

Source View message ‘No changes have been found’, but I didn’t see the data that

I just added in from cube browser.

(Database connecting and database structure didn’t change)

My questions:

1. Is refresh

   DSV only for database structure change?

2. Which

   step I was missing for refreshing cube data?

Thanks in advance.

Yes, refreshing is to pull database structure changes into the DSV. To get the new data, "Process" the cube.

You'll find that MSAS provides a rich set of capabilities and abilities to update the data. For now, always do a "Full Process", until you get more comfortable with the technology. Depending on the size of your cube, plan on spending a little time learning about incremental processing and partitions. For anything other than small cubes, expect to define / manage partitions are part of the definitions. The impact on performance is dramatic.

|||Thank you very much Dave.

It is very helpful information.

Refresh cube after data changed?

After I created a cube in BI Studio, I added more data to the fact table, modified dimension table, and refreshed DSV. I got Refresh Data Source View message ‘No changes have been found’, but I didn’t see the data that I just added in from cube browser.

(Database connecting and database structure didn’t change)

My questions:

1. Is refresh DSV only for database structure change?
2. Which step I was missing for refreshing cube data?

Thanks in advance.

Yes, refreshing is to pull database structure changes into the DSV. To get the new data, "Process" the cube.

You'll find that MSAS provides a rich set of capabilities and abilities to update the data. For now, always do a "Full Process", until you get more comfortable with the technology. Depending on the size of your cube, plan on spending a little time learning about incremental processing and partitions. For anything other than small cubes, expect to define / manage partitions are part of the definitions. The impact on performance is dramatic.

|||Thank you very much Dave.

It is very helpful information.

Reference dimensions are creating too many joins

I have orders fact; customer, location and age dimensions. I am using age and location as reference dimensions through customer dimension. If I don't use reference dimension cube processing SQL would be like following

select * from factOrders

If I use reference dimensions SQL would be like following

select * from factOrders, dimCustomer as customer1, dimCustomer as customer2

where factOrders.customerid = customer1.customerid and

factorders.customerid = customer2.customerid

If I have 6 reference dimensions through customer there will be 6 joins. Is there any way I can eliminate these joins as I need only one join to get all the information?

I can create a view between factOrders and dimCustomer and use the reference dimensions as regular dimensions which will avoid all the joins but I feel I am not using the intutive features.

Processing time is significantly higher if I use reference dimensions compared to using as a view.

We had the same problem. We resolved it as below.

Add the customerid as another attribute in the age and location dimensions.

( you may the FK relation netween customer and location/age dimension tables.).

Now instead of using the referrence dimension relationship for age/location, use regular dimension and customerid as granular attribute to connect to fact table. This improved the performance a lot.

Hope this may solve your problem.

Regards,

Butchi Satuluri

|||Thank you Butchi!

Reference dimensions are creating too many joins

I have orders fact; customer, location and age dimensions. I am using age and location as reference dimensions through customer dimension. If I don't use reference dimension cube processing SQL would be like following

select * from factOrders

If I use reference dimensions SQL would be like following

select * from factOrders, dimCustomer as customer1, dimCustomer as customer2

where factOrders.customerid = customer1.customerid and

factorders.customerid = customer2.customerid

If I have 6 reference dimensions through customer there will be 6 joins. Is there any way I can eliminate these joins as I need only one join to get all the information?

I can create a view between factOrders and dimCustomer and use the reference dimensions as regular dimensions which will avoid all the joins but I feel I am not using the intutive features.

Processing time is significantly higher if I use reference dimensions compared to using as a view.

We had the same problem. We resolved it as below.

Add the customerid as another attribute in the age and location dimensions.

( you may the FK relation netween customer and location/age dimension tables.).

Now instead of using the referrence dimension relationship for age/location, use regular dimension and customerid as granular attribute to connect to fact table. This improved the performance a lot.

Hope this may solve your problem.

Regards,

Butchi Satuluri

|||Thank you Butchi!

Reference Dimension causing long processing times

I have a reference dimension that is linked to a regular dimension that is linked to the fact table. For some reason, when I process the database with some small sample data, it take about 5 minutes to process, where it should take 10 seconds. I am using a reference dimension because it is a child-parent hierarchy, and I don't think I can merge it into the regular dimension. My work-around is to just include the link to this reference dimension in the fact table, so I can create the child-parent dimension as regular. Now is processes in 10 seconds. It doesn't seem ideal since I have what seems to me an unessary dimension now.

If I turn "materialize" off, it processes in 10 seconds, but I don't know the implications of this.

Does anyone have any idea of what is going on or have any advice?

Thanks,

Ernie

Are you using a referenced dimension here because the parent column is in another table from the child column? If so, you could create a view on top of them that encapsulates the relationship. What are the other issues you see regarding not being able to merge these columns into a regular dimension?|||

Thanks for the reply Scott.

The child and parent are in the same view. I've created another dimiension because the child attribute has to be a key. The child attribute, or key of this dimension, is not what joins directly to the fact table. There is an intermediate "field" that has to create this link, which is a foriegn key in the regular dimension, which has a key to link to the fact table. Hmmm, did I say that right? I may not be explaining things well, as I'm a bit new at this. Anyway, this is why I "think" I can't merge them.

Maybe this is a valid example:

Fact table has column SubAccount

SubAccount table has Account column (multiple sub-accounts per account)

Account table has AccountParent column which created the child parent relationship.

In this example, I would create a regular SubAccount dimension and a reference dimension called Account. My "workaround" is to put both the Account and SubAccount columns in the Fact table (or view).

Ernie

|||

It sounds like you should merge the sub account and account into a single dimension (make it a parent-child). You'll use a snowflake schema which will describe the join between the account and sub account tables as a source for the dimension, yet only have a single dimension.

Without doing this, if you tried going down the road of having two account dimensions (subaccount and account), then you'd end up with cases where you would get confusing results (like subaccounts that are not really related to an account showing up together, etc.)

Hope that helps

|||

Scott (or anyone else):

I am running into a problem that seems to be related to the one described by Ernie above. Here is the situation:

1) I have a referenced dimension (dimA) that joins to the fact table (factB) through an intermediate dimension table (dimC).

2) The referenced dimension (dimA) has a parent-child heirarchy. The primary key of this table (keySelf) and the parent key (keyParent) are in the same table (dimA) i.e. the parent-child hierarchy is stored in the same table and not in a separate table.

3) I am able to deploy the cube and it gives correct results.

4) I am currently in the design stage so I am dealing with fairly small data sets. Therefore, I donot know if it is performing poorly during deployment.

5) My problem occurs when I try to design aggregations. I get the error at the end of the wizard when I am deploying the aggregations. The error is on the Measure group that is based on factB. The error states:

"Errors in the high-level relational engine. The binding for the 'standardId' column is not a ColumnBinding type."

Note that 'standardId' is the primary key (keySelf) in the dimA table. i.e. it is the key that maps factB to dimA (through dimC).

6) The error goes away if I uncheck the "Materialize" option in the Define Relationship window for the relationship between factB and dimA.

7) I have other referenced dimensions in the cube that relate to factB through dimC that do not have a parent-child hierarchy and they do not give this problem.

So it seems that if a referenced dimension has a parent-child hierarchy and is materialized then the aggregations run into problem. I realize that I can eliminate the issue by making dimA a regular dimension of factB but I am curious to understand why this is happening.

Is this expected behaviour or am I doing something wrong?

Thanks beforhand.

Regards,

Amardeep

|||

Amardeep, this sounds like it could be a bug that you're hitting. I'll forward this on to our test team to verify.

One thing you could do to work around the issue that you're seeing though (since not materializing the dimension will be slower at query time) would be to not use a referenced dimension and instead use a regular dimension. To do this you'll need to modify your data source view (DSV) to include additional relationships in order to allow the join to work properly.

Hope this helps.