(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

(Newbie) Trying to get rid of NonEmptyCrossJoin

Hi everyone,

As I have said before, I am new to MDX. Any help much appreciated.

I am trying to substitute the Exists() function for the NonEmptyCrossJoin() function, but Exists is not producing the results that I would expect. The test query that I am running is:

SELECT {[Measures].[Value]} ON COLUMNS,

NonEmpty({[Fact].[Name].[All].CHILDREN}) ON ROWS

FROM [AS Test1]

WHERE

NonEmptyCrossJoin({[Start Date].[Month Hierarchy].[2006-01-01 00:00:00]:[Start Date].[Month Hierarchy].[2006-06-02 00:00:00]},

{[End Date].[Month Hierarchy].[2006-06-15 00:00:00]:[End Date].[Month Hierarchy].[2006-12-31 00:00:00]})

This is not ideal code, but performs correctly, only listing members with start and end dates with the appropriate values. I tried to substitute that query for this:

SELECT {[Measures].[Value]} ON COLUMNS,

NonEmpty({[Fact].[Name].[All].CHILDREN}) ON ROWS

FROM [AS Test1]

WHERE

Exists(NonEmpty({[Start Date].[Month Hierarchy].[2006-01-01 00:00:00]:[Start Date].[Month Hierarchy].[2006-06-02 00:00:00]}),

NonEmpty({[End Date].[Month Hierarchy].[2006-06-15 00:00:00]:[End Date].[Month Hierarchy].[2006-12-31 00:00:00]}))

This query effectively ignores the second set (i.e. the End Date set), listing all values with start dates in the given range. Can anyone tell me what I am doing wrong?

Any help much appreciated.

Edit: Adding a measure group at the end of the Exists function and/or removing the NonEmpty functions do not correct the problem.

Not sure whether you've read this blog entry, which discusses different scenarios in which NECJ was used. The closest equivalent is Exists() with measure group, but it depends on the scenario in which NECJ was being used:

http://sqljunkies.com/WebLog/mosha/archive/2006/10/09/nonempty_exists_necj.aspx

>>

MDX: NonEmpty, Exists and evil NonEmptyCrossJoin

...

NonEmptyCrossJoin(set1, set2, ..., setN, K) is equivalent to Exists(set1*...*setK, set(K+1)*...*setN, "measuregroupname")

where "measuregroupname" is the name of the measure group to which the current measure belongs.

...

>>

So, in your case, it should be like:

Exists({[Start Date].[Month Hierarchy].[2006-01-01 00:00:00]:[Start Date].[Month Hierarchy].[2006-06-02 00:00:00]}

* {[End Date].[Month Hierarchy].[2006-06-15 00:00:00]:[End Date].[Month Hierarchy].[2006-12-31 00:00:00]},, "measuregroupname")

|||

Hi Ed,

First of all, why are you trying to replace NECJ with Exists rather than NonEmpty - are you setting the NullProcessing property anywhere? I think you're better off using the NonEmpty function here.

Secondly, in my experience you should always include a measure somewhere in either of the sets you pass into NonEmpty - especially if you have multiple measure groups in your cube. If you don't then I think what happens is that you remove all the items in your set which are empty for all measures in the cube, which can be confusing when you're only showing one measure and can also slow your query down.

So... does the following do what you want?

SELECT {[Measures].[Value]} ON COLUMNS,

NonEmpty({[Fact].[Name].[All].CHILDREN},[Measures].[Value]) ON ROWS

FROM [AS Test1]

WHERE

(NonEmpty({[Start Date].[Month Hierarchy].[2006-01-01 00:00:00]:[Start Date].[Month Hierarchy].[2006-06-02 00:00:00]} *

{[End Date].[Month Hierarchy].[2006-06-15 00:00:00]:[End Date].[Month Hierarchy].[2006-12-31 00:00:00]},[Measures].[Value]))

HTH,

Chris

|||Thank you very much Chris and Deepak for your replies. I'll give those approaches a try.

(Newbie) Outer Join Simulation

I have two tables invoice(fact table) and agents(dimension). Not all invoices have an agent. How do I use the agents table as an outer join? When I include agents as a dimension and look at all agents, I only get invoices that have an agent. I know I could create a view in SQL but I want to do this in the cube.

Thanks,

Bill

You can create an unknown member in your agent dimension table and connect all fact records without an agent key to that member. You will have to use TSQL to this.

SSAS2005 can help you with doing this without writing code. Have a look at inferred members in BOL!

Regards

Thomas Ivarsson

(newbie) copying rows between tables via notepad

Hi
newbie question I'm sure
I have 2 databases on 2 different machines, there is no
connection between the 2. What I need is a way of
extracting particular rows from particular tables and adding them
to another database. The only viable way of doing this is via
email for unimportant reasons.
Via query analyser I can do a select and get the results in
text then manually adjust these back into an insert statement.
There must be a better way but I'm too new to this to know how
Any ideas?

Thanks
Fhttp://vyaskn.tripod.com/code.htm#inserts

--
David Portas
SQL Server MVP
--|||You can use bcp.exe or DTS to export the data to file, then the same
mechanism to import them at the destination - see Books Online for the
details. Or if you want to use INSERTs, then check this out:

http://vyaskn.tripod.com/code.htm#inserts

Simon|||Simon Hayes wrote:
> You can use bcp.exe or DTS to export the data to file, then the same
> mechanism to import them at the destination - see Books Online for
the
> details. Or if you want to use INSERTs, then check this out:
> http://vyaskn.tripod.com/code.htm#inserts
> Simon

Thanks for the reply, this looked like it would have been ideal but
this is the output I get, it gets cut off (column names changed to
protect
the innocent :-))

INSERT INTO [XXXXXXXXXXXXXX]
([XXXXXXXXXXXXX],[XXXXXXXXXXXX],[XXXXXXXXXXXXXXX],[XXXXXXXXXXXXXXXXXXX],[XXXXXXXXXXXXXXXXX],[XXXXXXXXXXXXXX],[XXXXXXXXXXX],[XXXXXXXXXXXXXXXXX],[XXXXXXXXXXXXXXX],[XXXXXXXXXXXXXXXXXXX],[XXXXXXXXXXXXXXXXXX])VALUES('7CC60B79-1B27-4|||(foldface@.yahoo.co.uk) writes:
> Thanks for the reply, this looked like it would have been ideal but
> this is the output I get, it gets cut off (column names changed to
> protect

Sounds like you should go into to Tools->Options->Results and adjust
"Maximum characters per column".

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||Thanks for the reply, yes, that was it, it was in the faq for that SP
as well but I assumed it was the SP that wasn't working because I
briefly saw this comment, huge is a relative thing!
Works fine now, very handy

NOTE:This procedure may not work with tables with too many columns.
Results can be unpredictable with huge text columns

(newbie) Cant connect to msde Database,

Hi,
I am an ASP.net newbie learning thru databasing now.
When setting up and connectiong to Database, i am prompted at SQL Server
Authentication for a user name and password
When I installed, thru the CMD under Start: Run in Windows 2000, i believe i
specified "sa" and "password" for user name and password. but now i get an
error, trying to input that.
"unable to connect to database.
Login failed for user "sa" Reason : not associated with a trusted SQL Server
connection"

What am i doing wrong? if i mistyped the password info when setting up,
where can i check it? or reset it?

Please someone help me

RaphaelSo are you using a ASP.Net script to connect to the DB?

The SQL connection should look like:

server=localhost;Trusted_Connection=true;database=theDB;user id=sa;password=password

Does that make sense to you?

Adrian|||um,

No not at first site, I am a newbie, remember!
I am trying to run from the web matrix project, and i simply cannot create a new msde database.

Do you have any further advice?

Thanks

Raphael

(newbie) - Stored Procedure Problem

Can anyone tell me what is wrong with this stored procedure?

CREATE PROCEDURE [dbo].[AddGroupPermission]
@.Perm varchar(16)
AS
ALTER TABLE tblUserGroups ADD @.Perm VARCHAR(1) NULL
GO

When I click on Check Syntax, I get 'Error 170 - Line 4: Incorrect syntax near @.Perm'

I have checked the syntax for the ALTER command, and it looks correct to me...

This is my first day at using SQL Server in anger, so any help appreciated :)You must use dynamic sql for that, check out :

http://www.sqlteam.com/item.asp?ItemID=4599

The following will work

CREATE PROCEDURE [dbo].[AddGroupPermission]
@.Perm varchar(16)
AS
exec ('ALTER TABLE tblUserGroups ADD ' + @.Perm + 'VARCHAR(1) NULL')
GO|||Oh wow! Thanks for that - I didn't even know you could do that directly in SQL server - I'm used to doing it in ASP pages, of course, but this DB is new to me :)

I'm off to check your link now - thanks very much for taking the time to post.

Mark.sql

(newbie ques) Access 2000 to SQL Server migrate sync

I am a db newbie and am looking for advice on how to migrate a (fairly large - 900mb) Access b/e to SQL server 2000. The f/e is Access. I've read most of the upsizing info, but need help in understanding how to merge in new records/data which occur while the upsizing is done. (i estimate it will take several days to sort out the upsizing mess). This is a very active b/e, and not a lot of downtime is avail.

thanks for any expert advice

crunchy f.alter the access tables to add an extra column, let's call it Converted, with Required=yes, Allow zero length=No, Default='n'

run the extract to begin the upsizing process, then set Converted='y' for all records before putting the access database back online

all your existing apps can then continue as before

when the first stage of upsizing is done, do another extract, pulling only records where Converted='n' and set these to 'y' before putting the database back online

lather, rinse, repeat

rudy
http://rudy.ca/|||I am also interested in this. But just for my curiosity, can you let me know the significance of this new column etc.,
By the way, my knowledge of access and SQL and Vb is limited as I have learnt through forums like this
Thanks for your time and patience|||the significance of this new column? it marks which rows have been upsized

when you first add the column to the table, all rows have N

then you upsize all rows, and mark them Y (converted)

then the access application adds some more rows into the table, and they all get N by default

now you want to upsize only the recent ones, so you simply select them with converted='N' so that you don't pick up any rows that have already been upsized

rudy

(Complete newbie) Please help! Installation problem (I think)

Hello,

I most likely am being incredibly stupid here, but I have installed SQL 2005 Enterprise and Service Pack 2 on my Acer laptop running Vista Business and cannot for the life of me figure out how to start the thing.

When I hit the start button on the toolbar, and go to Microsoft SQL Server 2005, there is one folder that expands down called Configuration Tools. This has the options Notification Services, Configuration Management, Error and Usage Reports, and Surface Area Configuration. But no where can I find an icon that actually starts the database thing so I can create a database and learn some SQL stuff.

I would be most grateful if anybody could point out where I am going wrong or if there is anything I need to do.

Many thanks,

Chima

The default installation does NOT install the client tools.

Using the install media, run Setup again, and this time choose Client Tools.

|||Marvellous thanks Arnie!

"sa" account and domain level rights

Hey everyone,
I apologize for the newbie question but I'm looking for the correct answer. We have 4 production SQL servers at this time. When we had originally set them up the "sa" account belonged to the domain administrators group. Since we have a SQL admin team and a domain admin team we would like to remove this privilege. Is this something we can and should do? Our SQL servers use mixed mode authentication and some databases are configured for Windows authentication. I would appreciate any input from the community.

You are mixing some things up:

"When we had originally set them up the "sa" account belonged to the domain administrators group."

As sa is a sql server login it cannot belong to any domain group.

"Our SQL servers use mixed mode authentication and some databases are configured for Windows authentication. "

Authentication takes place at the server level not the database level, so although you switched on Mixed Auth on Server level you can still have autorized users in the database who are authenticated via SQL Server authentication.

Server --Authentication
Database --Authorization

HT, Jens Suessmeyer.

|||Sorry for the confusion. Thanks for the response Jen. We have a domain account called "sa" which was used to create databases on our SQL server so it is the current db owner. We use Windows authentication so that credentials are passed via Active Directory from an end user's network logon to our SQL server. Also, we use the Active Directory "sa" account to log in to SQL and it allows our SQL administrators to create Active Directory / SQL accounts to facilitate Windows authentication. My concern is that if we remove domain administration rights from this account we risk breaking applications that have the "sa" account as the database owner.
|||

OK, lets clarify something.

For the User DOmain account sa, the following schema is valid:

Domain --Domain Adminstrator --> (inherits) local administrative rights on the SQL Server -- inherits serverole systemadministrator (in sql server because the local admin group and therefore also the domain admins) are systemadministrators --> inherits dbowner role on the database, because systemadministrator are in the role downers by default.

So breaking the chains:

-With revoking domain administrative rights from the user sa won′t break the chain if you grant him local admin rights.

-With revoking domain administrative rights from the user sa won′t, not giving him local administrative rights on the server won′t break the chain if you put him to the systemadmistrator role on the server.

-With revoking domain administrative rights from the user sa, not giving him local administrative rights on the server, not putting him in the role systeadminstrators break the chain, though he only has downer access to the database now.

It hoped I clarified that a bit. If you didn′t understand my explanation or you have a further question, don′t hesitate to ask :-)

HTH, Jens Suessmeyer.

|||That's a great explanation Jens. Thank you very much. We are going to schedule an off hours test. First step will be to add the domain "sa" account to the local administrator group on our SQL servers. The second step will be removing the account from the domain administrators group. Then we will test and hope for the best. From what I've read, I did not see anything about the "sa" account requiring domain level rights (only local). So, you re-affirmed my thinking. I really appreciate your input.
|||You don′t even need to put him in the local admin group. Local admins are by default in the systemadministrators groups. But you can also put specific users in that role. So you can further restrict the user to have local admin rights.

HTH, Jens Suessmeyer.

<Newbie> Problem with Server-Name in Connect to Remote Server

Dear group,

kindly please answer me my newbie question :-)

I want to connect to a remote SQL-Server using sp_addlinkedservers and
"openquery". I tried and tried and I couldn't get it to work. Then i
found out, that it works with a different server which doesn't have
the '-' character within its servername.

So

sp_addlinkedserver 'ULI-SERVER'
select * from openquery(uli-server, 'select * from
northwind.dbo.sometable')

yields a syntax error ("Incorrect syntax near '-'")

sp_addlinkedserver 'someotherserver'
select * from openquery(someotherserver, 'select * from
northwind.dbo.sometable')

is working

Am I doing something wrong ? Is there a workaround ? Do I have to
rename servers which would be tedious for us.

Thanks for any hints

Greetings from Vienna

UliUli (uli2003wien@.lycos.at) writes:
> kindly please answer me my newbie question :-)
> I want to connect to a remote SQL-Server using sp_addlinkedservers and
> "openquery". I tried and tried and I couldn't get it to work. Then i
> found out, that it works with a different server which doesn't have
> the '-' character within its servername.
> So
> sp_addlinkedserver 'ULI-SERVER'
> select * from openquery(uli-server, 'select * from
> northwind.dbo.sometable')
> yields a syntax error ("Incorrect syntax near '-'")
> sp_addlinkedserver 'someotherserver'
> select * from openquery(someotherserver, 'select * from
> northwind.dbo.sometable')
> is working
>
> Am I doing something wrong ? Is there a workaround ? Do I have to
> rename servers which would be tedious for us.

- is not a legal character in identifiers. Whence the syntax error.

Fortunately, the rememdy is simple:

select * from openquery([uli-server],
'select * from northwind.dbo.sometable')

Whenever you have a name in SQL Server which does not adhere to the
grammar for identifiers, but the name in brackets. (Or in double quotes,
if you want stick to ANSI standards.)

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp

# vs ## (beginner question)

Newbie question here. Could someone tell me what difference prefixing a table with # vs. ## is? Is there a difference? Does this signify anything?
Thanks, Matt# means a local temp table, only available to you, ## means a global temp table, available to other connections too.

That may not be 100% right, but I think that's basically it.

If you need global and you specify local, you will probably notice at some point, since only 1 user at a time ever be able to use it. If you need local and specify global, you will probably only ever notice if you get a name clash (global temp tables must have unique names).