(newbie) updating dimensions

Hi,

I apologise if I have missed something obvious here, but I am having a problem finding how to set up a cube to update dimensions automatically when data is changed in the source database. From what I can see in Books Online, dimension writeback does not do this because the update is initiated by the user. I have got storage settings for measures figured out, and we are using low-latency MOLAP. In BIDS, there does not seem to be a similar way to set up the dimensions to be updated. Can anyone advise me on how this is done?

Any help much appreciated.

Hello. The feature that you are looking for is maybe "proactive caching".

My advice is to be very careful about this feature. You will need to be an experienced SSAS2005(Analysis Services 2005) consultant to set this up properly.

I also think that it is only a feature in the enterprise edition.

The budget version is to have an SSIS-package(Integration Services) that detects changes in the database and automatically start an SSAS2005 processing task after that.

Looks like you are planning to build a cube on top of an OLTP-system(transaction system) and ignore the option to build a data warehouse/ data mart?

I always recommend a datamart separate from the source system as the minimum requirement to build a good analytic solution.

HTH

Thomas Ivarsson

Have a look at proactive caching here:

http://www.microsoft.com/technet/prodtechnol/sql/2005/rtbissas.mspx

|||Thanks for your reply, Thomas. We already have our options worked out re: proactive caching as far as measures are concerned, where the BIDS UI is perfectly clear. It is for dimensions specifically that we are having trouble. Can anyone shed any light on that specifically? Thanks very much for any help.|||

Hello again Ed.

I have not used this feature myself and have no plans to do it in the near future.

You have all dimensions as ROLAP?

I you buy "MS SQL server 2005 Analysis Services"(SAMS) there is almost a whole chapter(24) with recommendations of how to configure this properly.

HTH
Thomas Ivarsson

|||Thank you Thomas. I'll definitely take a look at that book.sql

"Real-time" ROLAP partitions with MOLAP dimensions

As I've described in another thread, I've doing some work with ROLAP partitions and dimensions in an attempt to have a small set of "real-time" data accessible along with our historical MOLAP cubes. I never found a solution to the problem I described in that thread, so I'm trying a new approach - adding a measure group with a ROLAP partition to an existing cube (in many ways this is preferable anyway, since we could then query across both the historical and real-time stuff).

What I was unclear on is how dimensions would be handled in this case, since they would be the same MOLAP dimensions that the historical cube is using. Since the ROLAP partition will contain facts that are associated with dimension members that haven't been processed yet, how would it handle it? Well, the answer seems to be "not that well". When I browse the ROLAP measures that reference dimension members that haven't been processed yet, I get the error:

"Errors in the OLAP storage engine: The attribute key cannot be found: Table: <fact table name>, Column: <dimension key>"

Is this a totally unreasonable thing to do? Is there some other approach I'm not considering?

This is with SSAS SP2, if it matters.

Do you have some sort of process inserting the new dimension records into the dimesion tables or are you building directly off your source system.

If you have an SSIS package inserting new records you could use a mutlicast transform and send one copy of the rows to the dimension table and another copy to the dimension processing destination. If you then set the dimension processing destination to ProcessAdd it will just append these new rows to the dimension.

|||We have a custom application inserting the dimension records into a traditional star-schema data mart (sort of a domain-specific ETL engine).

Are you talking about using push-mode processing for the new dimension records? Or a standard ProcessAdd processing command?
|||I am talking about push mode processing. The dimension processing destination in SSIS simply generates the appropriate XMLA. If you are not using SSIS it might be easier to generate the XMLA. Greg Galloway has some XMLA samples of doing push mode processing. http://www.artisconsulting.com/Blogs/tabid/94/EntryID/2/Default.aspx|||Interesting idea, but I don't think it will work for us. Aside from general concerns about the limited information about push-mode processing out there, and its potential performance impacts, Greg's posts say "Push mode processing is only allowed on partitions or dimensions which use one table from the DSV". Our dimensional structure is heavily snowflakes, so it sounds like it's not really an option for us.

Is our scenario (ROLAP partitions with MOLAP dimensions) an unreasonable one? I don't see anything in the docs that indicate that. Or are most people just not adding unprocessed dimension members before they query their ROLAP partitions?
|||

You could probably mix ROLAP partitions and MOLAP dimensions only if your dimensions are relatively stable. For each fact record it has to do the equivalent of a join to the MOLAP dimension and if you have records that have not been processed into the MOLAP structure yet you will get the error you discribed.

I am begining to feel like we are being squeezed between a rock and a hard place.

You could switch the affected dimensions over to ROLAP storage. I'm honestly not sure what impact this would have on performance - as I think I've mentioned in other threads I have not had to use ROLAP storage with any of my clients in AS2005. I'm guessing the dimension with new members is going to be something like a large customer dimension or something similar.

"A loop was found in the data source view at the 'dbo_....' table"

Argh! I was just adding and refactoring a few dimensions, and when I process one of my partitions, I get this error:

"A loop was found in the data source view at the 'dbo_Account' table"

What does this mean, and any ideas on where I should debug to repair it? All the SQL queries in my DSVs look sound...Ok. I went back and looked at the 2 dimensions I added - they were referenced. They were set to be materialized. I changed them so that they were no longer materialized. Everything works great now. I have no idea why.

The only bad part is that I feel like I wasted 3 hours.

# of Tables in a DB

If I want to build an architected data warehouse, otherwise known as a star
constellation of multiple facts and conformed dimensions, I'm trying to figu
re out how to separate the physical tables by database. Think about modelin
g the constellation in ERwi
n. You want to have mutiple fact tables with foreign key relationships to s
hared conformed dimensions. The question is - AS A GENERAL RULE, when does
a single database have too many tables? Does each star in the constellation
(for each business process
) merit its own database in the SQL Server instance? If so, how do you set
foreign key relationships across database boundaries, since not having the r
elationships will cause performance to suffer? I have found no way to set f
oreign key relationships ac
ross database boundaries in ERwin.I'm not sure why you would want to have multiple databases. You can already
use filegroups to isolate certain tables or non-clustered indexes across var
ious spindles. Regardless, you cannot have FK constraints that span database
s.

# of Tables in a DB

If I want to build an architected data warehouse, otherwise known as a star constellation of multiple facts and conformed dimensions, I'm trying to figure out how to separate the physical tables by database. Think about modeling the constellation in ERwi
n. You want to have mutiple fact tables with foreign key relationships to shared conformed dimensions. The question is - AS A GENERAL RULE, when does a single database have too many tables? Does each star in the constellation (for each business process
) merit its own database in the SQL Server instance? If so, how do you set foreign key relationships across database boundaries, since not having the relationships will cause performance to suffer? I have found no way to set foreign key relationships ac
ross database boundaries in ERwin.
I'm not sure why you would want to have multiple databases. You can already use filegroups to isolate certain tables or non-clustered indexes across various spindles. Regardless, you cannot have FK constraints that span databases.