(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

(GUID problem) What is wrong with this code ?

Hi, just learning SQL2000.
I have this code:
mSQL = "UPDATE Contactpersonen SET Naam=? WHERE ContactpersoonID=?"
Command = New SqlClient.SqlCommand(mSQL, C_CP)
Command.Parameters.Add("Naam", txtNaam.Text)
Command.Parameters.Add("CPID", SqlDbType.UniqueIdentifier).Value = m_CP_ID

where m_CP_ID is defined as a GUID in:
Public Property m_CP_ID() As Guid
Get
If Not viewstate("m_CP_ID") Is Nothing Then
Return viewstate("m_CP_ID")
End If
'Return 0
End Get
Set(ByVal Value As Guid)
viewstate("m_CP_ID") = Value
End Set
End Property

When running the app I got this error:
Server Error in '/4D' Application.
------------------------

Line 1: Incorrect syntax near 'Naam'. Line 1: Incorrect syntax near '?'.(points to mSQL above)
I know it has something to do with the GUID, but I cannot guess what.
Help is appreciated, Ger.

Try this:
mSQL = "UPDATE Contactpersonen SETNaam=@.Naam WHEREContactpersoonID=@.CPID"
Command = New SqlClient.SqlCommand(mSQL, C_CP)
Command.Parameters.Add("@.Naam", txtNaam.Text)
Command.Parameters.Add("@.CPID", SqlDbType.UniqueIdentifier).Value = m_CP_ID
|||Hey SonuKapoor, that works !!!!!!
Thanks a lot, every day I learn more and more... thanks to guys as you,
regards from the North Sea,
Ger.

(3) e.mail of security

yesterday and today receive e.mail from Microsoft sec.
not sure of sender. there are no update security for me
in HELP AND SUPPORT. Hare those e.mail realy from
MICROSOFTMicrosoft NEVER sends emails with security update attachments. You can
subscribe to mailing lists to receive Microsoft security bulletins or read
Microsoft security bulletins on the web. These bulletins NEVER contain
executable attachments, only references to web pages where you can access
Windows Update, download patches, or request to receive patches from
Microsoft Product Support Services.
You should never use any tool other than Automatic Updates, the Microsoft
Download Center, the Windows Update web site, or a response to your request
to Microsoft Product Support Services to install Windows security updates
or hotfixes. See
http://www.microsoft.com/technet/se.../patch_hoax.asp for an
explanation from Microsoft about these hoax email messages.
http://www.microsoft.com/technet/tr...chnet/security/
secnews/faq/faq1.asp
Thanks,
Kevin McDonnell
Microsoft Corporation
This posting is provided AS IS with no warranties, and confers no rights.

"Process Update" and "Process Incremental" no longer supported for SQL 2005

I've installed SQL 2005 service pack 1, and I found that process update and process incremental is disappeared in the process option. Is this no longer supported?

Thanks!

Kelvin Jor

There got to be some mistake. What object you are talking about.

Different objects have different processing options applied to them.

Take a look at this paper, it has a table listing objects and options applied to them: http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dnsql90/html/sql2k5_asprocarch.asp

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

"CREATE OR ALTER" works in oracle, but not in ms sql

Hi

Normally we deliver scripts and the installers may NOT update the scripts.

This was fine for SQL scripts for oracle: because we are not aware of the situation in production, whether a stored proc was already previously installed or not.

therefore we wrote:

CREATE OR ALTER PROCEDURE ....... (or something like that).

but it seems in SQL server I can only write one of both:

CREATE PROCEDURE ....

or

ALTER PROCEDURE

this means that the script fails if it's NOT yet installed when and i use ALTER, and it fails when it IS already installed and I write CREATE

isn't there a solution to use "CREATE OR ALTER" in one script ?

IF EXISTS (SELECT name FROM sysobjects WHERE name = 'myStoredProc' AND type = 'P')

BEGIN

DROP PROCEDURE myStoredProc

END

GO

CREATE PROCEDURE myStoredProc (@.Params ...)

AS

BEGIN

...

END

GO

##

can i update tables on a linked oracle server from SQL server.
if so do i use tsql or PLSQL?mat
T-SQL
UPDATE OPENQUERY(OracleServer, 'SELECT col1, col3 FROM tbl..')
SET col1 = @.param1
WHERE col2 = param2
"mat" <mat@.discussions.microsoft.com> wrote in message
news:E1C9CA61-006F-4154-A30D-DC1BCD8DFF5E@.microsoft.com...
> can i update tables on a linked oracle server from SQL server.
> if so do i use tsql or PLSQL?|||"Uri Dimant" <urid@.iscar.co.il> wrote in message
news:%23vctyn2uEHA.3200@.TK2MSFTNGP14.phx.gbl...
> mat
> T-SQL
> UPDATE OPENQUERY(OracleServer, 'SELECT col1, col3 FROM tbl..')
> SET col1 = @.param1
> WHERE col2 = param2
>
Both, actually.
The UPDATE is TSQL and the argument to OPENQUERY is a passthrough query in
Oracle dialect.
For instance, compare
select * from OPENQUERY(oracle,'select * from scott.emp where
ename=''smith''')
select * from oracle..SCOTT.EMP where ename='smith'
The first query returns 0 rows since the where clause is evaluated by Oracle
and is case-sensative. The second returns 1 row since it's evaluated by SQL
Server as non-case sensative. Second query also returns all rows from
Oracle to Sql Sever and filters them there.
David

##

can i update tables on a linked oracle server from SQL server.
if so do i use tsql or PLSQL?mat
T-SQL
UPDATE OPENQUERY(OracleServer, 'SELECT col1, col3 FROM tbl..')
SET col1 = @.param1
WHERE col2 = param2
"mat" <mat@.discussions.microsoft.com> wrote in message
news:E1C9CA61-006F-4154-A30D-DC1BCD8DFF5E@.microsoft.com...
> can i update tables on a linked oracle server from SQL server.
> if so do i use tsql or PLSQL?|||"Uri Dimant" <urid@.iscar.co.il> wrote in message
news:%23vctyn2uEHA.3200@.TK2MSFTNGP14.phx.gbl...
> mat
> T-SQL
> UPDATE OPENQUERY(OracleServer, 'SELECT col1, col3 FROM tbl..')
> SET col1 = @.param1
> WHERE col2 = param2
>
Both, actually.
The UPDATE is TSQL and the argument to OPENQUERY is a passthrough query in
Oracle dialect.
For instance, compare
select * from OPENQUERY(oracle,'select * from scott.emp where
ename=''smith''')
select * from oracle..SCOTT.EMP where ename='smith'
The first query returns 0 rows since the where clause is evaluated by Oracle
and is case-sensative. The second returns 1 row since it's evaluated by SQL
Server as non-case sensative. Second query also returns all rows from
Oracle to Sql Sever and filters them there.
David

##

can i update tables on a linked oracle server from SQL server.
if so do i use tsql or PLSQL?
mat
T-SQL
UPDATE OPENQUERY(OracleServer, 'SELECT col1, col3 FROM tbl..')
SET col1 = @.param1
WHERE col2 = param2
"mat" <mat@.discussions.microsoft.com> wrote in message
news:E1C9CA61-006F-4154-A30D-DC1BCD8DFF5E@.microsoft.com...
> can i update tables on a linked oracle server from SQL server.
> if so do i use tsql or PLSQL?
|||"Uri Dimant" <urid@.iscar.co.il> wrote in message
news:%23vctyn2uEHA.3200@.TK2MSFTNGP14.phx.gbl...
> mat
> T-SQL
> UPDATE OPENQUERY(OracleServer, 'SELECT col1, col3 FROM tbl..')
> SET col1 = @.param1
> WHERE col2 = param2
>
Both, actually.
The UPDATE is TSQL and the argument to OPENQUERY is a passthrough query in
Oracle dialect.
For instance, compare
select * from OPENQUERY(oracle,'select * from scott.emp where
ename=''smith''')
select * from oracle..SCOTT.EMP where ename='smith'
The first query returns 0 rows since the where clause is evaluated by Oracle
and is case-sensative. The second returns 1 row since it's evaluated by SQL
Server as non-case sensative. Second query also returns all rows from
Oracle to Sql Sever and filters them there.
David

# of CPU for SQL Server

I have a question about sizing of a SQL 2000 server.
If my no. of transaction I need to update does not changes, when the data growth, do I need to add more CPU or memory to the SQL 2000 Server
Hi,
No need to increase the RAM or CPU directly. You measure the usage of MEMORY
and CPU for some time during peak usage situation
using Windows performance monitor or system Monitor.
If you find that CPU usage is always higher than 70% and memry usage is
higher than 80%, then add additional hardware resources.
Thanks
Hari
MCDBA
"Kelvin" <anonymous@.discussions.microsoft.com> wrote in message
news:28DD3CCE-93F0-46BA-AC99-FE60285A2275@.microsoft.com...
> I have a question about sizing of a SQL 2000 server.
> If my no. of transaction I need to update does not changes, when the data
growth, do I need to add more CPU or memory to the SQL 2000 Server
|||If it is possible that although the number of transactions does not change,
but the number of rows affected by the transactions changes or the amount of
data that people need to see increases over time, then you may need to
increase memory and processor as data volumes increase...
Index maintenance will also take more resources( as will the DBCCs)
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
"Kelvin" <anonymous@.discussions.microsoft.com> wrote in message
news:28DD3CCE-93F0-46BA-AC99-FE60285A2275@.microsoft.com...
> I have a question about sizing of a SQL 2000 server.
> If my no. of transaction I need to update does not changes, when the data
growth, do I need to add more CPU or memory to the SQL 2000 Server