SQL server connection: view

Showing posts with label view. Show all posts

Tuesday, March 20, 2012

Another version of SQL Permission Question

How do I restrict update access to a table based on criteria within the
table. I'm trying to use a view and limit access to the base table
You can "update" a view in certain restricted conditions (like there
can't be any joins in the view for instance - see BOL for the complete
list of restrictions). Alternately, you can write an INSTEAD OF trigger
on the view so that the trigger code handles the updating of the
underlying tables when a user runs an UPDATE statement against the
view. You can use this method when the basic updatable view
requirements cannot be met.
So if your view limits access to only certain parts of the base table,
you can allow people to only update those unrestricted parts of your
base table by just letting them update the view (or if it's a complex
view, write an INSTEAD OF UPDATE trigger for the view).
Cheers,
*mike hodgson* |/ database administrator/ | mallesons stephen jaques
*T* +61 (2) 9296 3668 |* F* +61 (2) 9296 3885 |* M* +61 (408) 675 907
*E* mailto:mike.hodgson@.mallesons.nospam.com |* W* http://www.mallesons.com
KathyV wrote:

>How do I restrict update access to a table based on criteria within the
>table. I'm trying to use a view and limit access to the base table
>

Another version of SQL Permission Question

How do I restrict update access to a table based on criteria within the
table. I'm trying to use a view and limit access to the base tableYou can "update" a view in certain restricted conditions (like there
can't be any joins in the view for instance - see BOL for the complete
list of restrictions). Alternately, you can write an INSTEAD OF trigger
on the view so that the trigger code handles the updating of the
underlying tables when a user runs an UPDATE statement against the
view. You can use this method when the basic updatable view
requirements cannot be met.
So if your view limits access to only certain parts of the base table,
you can allow people to only update those unrestricted parts of your
base table by just letting them update the view (or if it's a complex
view, write an INSTEAD OF UPDATE trigger for the view).
Cheers,
*mike hodgson* |/ database administrator/ | mallesons stephen jaques
*T* +61 (2) 9296 3668 |* F* +61 (2) 9296 3885 |* M* +61 (408) 675 907
*E* mailto:mike.hodgson@.mallesons.nospam.com |* W* http://www.mallesons.com
KathyV wrote:

>How do I restrict update access to a table based on criteria within the
>table. I'm trying to use a view and limit access to the base table
>

Another version of SQL Permission Question

How do I restrict update access to a table based on criteria within the
table. I'm trying to use a view and limit access to the base tableThis is a multi-part message in MIME format.
--000602000304030604090506
Content-Type: text/plain; charset=UTF-8; format=flowed
Content-Transfer-Encoding: 7bit
You can "update" a view in certain restricted conditions (like there
can't be any joins in the view for instance - see BOL for the complete
list of restrictions). Alternately, you can write an INSTEAD OF trigger
on the view so that the trigger code handles the updating of the
underlying tables when a user runs an UPDATE statement against the
view. You can use this method when the basic updatable view
requirements cannot be met.
So if your view limits access to only certain parts of the base table,
you can allow people to only update those unrestricted parts of your
base table by just letting them update the view (or if it's a complex
view, write an INSTEAD OF UPDATE trigger for the view).
Cheers,
--
*mike hodgson* |/ database administrator/ | mallesons stephen jaques
*T* +61 (2) 9296 3668 |* F* +61 (2) 9296 3885 |* M* +61 (408) 675 907
*E* mailto:mike.hodgson@.mallesons.nospam.com |* W* http://www.mallesons.com
KathyV wrote:
>How do I restrict update access to a table based on criteria within the
>table. I'm trying to use a view and limit access to the base table
>
--000602000304030604090506
Content-Type: text/html; charset=UTF-8
Content-Transfer-Encoding: 8bit
<!DOCTYPE html PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN">
<html>
<head>
<meta content="text/html;charset=UTF-8" http-equiv="Content-Type">
</head>
<body bgcolor="#ffffff" text="#000000">
<tt>You can "update" a view in certain restricted conditions (like
there can't be any joins in the view for instance - see BOL for the
complete list of restrictions).Â Alternately, you can write an INSTEAD
OF trigger on the view so that the trigger code handles the updating of
the underlying tables when a user runs an UPDATE statement against the
view.Â You can use this method when the basic updatable view
requirements cannot be met. 
 
So if your view limits access to only certain parts of the base table,
you can allow people to only update those unrestricted parts of your
base table by just letting them update the view (or if it's a complex
view, write an INSTEAD OF UPDATE trigger for the view). 
 
Cheers, 
</tt>
<div class="moz-signature">
<title></title>
<meta http-equiv="Content-Type" content="text/html; ">
-- 
 mike
hodgson | database administrator
| mallesons stephen jaques 
T +61 (2) 9296 3668 | F +61
(2) 9296 3885 | M +61
(408) 675 907 
E
<a href="http://links.10026.com/?link=mailto:mike.hodgson@.mallesons.nospam.com">
mailto:mike.hodgson@.mallesons.nospam.com</a> | W <a href="http://links.10026.com/?link=/">http://www.mallesons.com">
http://www.mallesons.com</a> 
</div>
 
 
KathyV wrote:
<blockquote cite="mid282DBEFC-5100-4936-A1EA-A11561D1DDD4@.microsoft.com"
type="cite">
<pre wrap="">How do I restrict update access to a table based on criteria within the
table. I'm trying to use a view and limit access to the base table
</pre>
</blockquote>
</body>
</html>
--000602000304030604090506--

Monday, March 19, 2012

Another row to col issue

Hi,

I've got a Problem to craete a view which works like this:

I've got this Table:

USR_ID ATNAM ATVAL
-- -
2 USR_DEPARTMENT BS1
2 USR_MARITAL_STATE married
3 USR_DEPARTMENT BS1
3 USR_MARITAL_STATE single

and I have to display it in a view like this

USR_ID USR_DEPARTMENT USR_MARITAL_STATE
- - -
2 BS1 married
3 BS1 single

maybe there will be additional "ATNAMS" in future.

Has anybode an idea how i can achieve this?

thanks in advance

Raimund

Is there a reason that you cannot use PIVOT? Are you using SQL Server 2000 or SQL Server 2005?

With SQL Server 2005 you can create a view based on a pivot; it might look something like:

Code Snippet

create view dbo.aView
as
select USR_ID,
[USR_DEPARTMENT],
[USR_MARITAL_STATE]
from source
pivot( max(ATVAL) for ATNAM
in([USR_DEPARTMENT],[USR_MARITAL_STATE])
) pv

The MAX / CASE construct can be used either with SQL 2000 or SQL 2005; that version might look something like:

Code Snippet

create view dbo.aView
as

select USR_ID,
max( case when ATNAM = 'USR_DEPARTMENT'
then ATVAL end)
as USR_MARITAL_STATE,
max( case when ATNAM = 'USR_MARITAL_STATE'
then ATVAL end)
as USR_MARITAL_STATE
from source
group by USR_ID

|||

I just didn't know about the trick with te max statement used as aggregate function.

For dynamic adaption for new "ATNAMS" I'll try it with a trigger on the according definition table.

Thanks for fast reply

Raimund

Thursday, March 8, 2012

Another example of incomplete support of some legal database names

It has already been reported that the View tool in Enterprise Manager
cannot handle databases which have a period in their name (e.g.
"mydomain.com"). It doesn't show the individual field names.
Today I found a second problem: when scheduling a Job under SQL Server
Agent, in the New Step dialog you are prompted for a database name.
Databases with a period in their name are not listed. Tomorrow I'll
use ISQL to schedule the job in Windows Scheduler.
Hopefully MSFT will add these tests to the test scripts for the next
version and service pack (but don't hold your breath).
PS: I inherited this database name. It's too late (read: expensive)
now to fix it.
-Tom.Many of the tools do not support non-standard object names.
Even though SQL Server lets you create objects with invalid
names they aren't really legal names. You can find the rules
for object names in books online under the topic Using
Identifiers
-Sue
On Tue, 23 Mar 2004 19:39:31 -0700, Tom van Stiphout
<tom7744@.no.spam.cox.net> wrote:

>It has already been reported that the View tool in Enterprise Manager
>cannot handle databases which have a period in their name (e.g.
>"mydomain.com"). It doesn't show the individual field names.
>Today I found a second problem: when scheduling a Job under SQL Server
>Agent, in the New Step dialog you are prompted for a database name.
>Databases with a period in their name are not listed. Tomorrow I'll
>use ISQL to schedule the job in Windows Scheduler.
>Hopefully MSFT will add these tests to the test scripts for the next
>version and service pack (but don't hold your breath).
>PS: I inherited this database name. It's too late (read: expensive)
>now to fix it.
>-Tom.|||On Tue, 23 Mar 2004 19:39:31 -0700, Tom van Stiphout wrote:

>It has already been reported that the View tool in Enterprise Manager
>cannot handle databases which have a period in their name (e.g.
>"mydomain.com"). It doesn't show the individual field names.
>Today I found a second problem: when scheduling a Job under SQL Server
>Agent, in the New Step dialog you are prompted for a database name.
>Databases with a period in their name are not listed. Tomorrow I'll
>use ISQL to schedule the job in Windows Scheduler.
>Hopefully MSFT will add these tests to the test scripts for the next
>version and service pack (but don't hold your breath).
Hopefully not. I would prefer that MSFT removes the ability to call a
database "mydomain.com" or other pathological formats.

>PS: I inherited this database name. It's too late (read: expensive)
>now to fix it.
That's too bad.
I presume that you can still write scripts for all of the things that
you can't accomplish via the GUI? If so, then you have the tradeoff
costs of writing everything in scripts versus changing the name of the
database in the application (and all the associated testing).
--
"There is more to life than simply increasing its speed." - Mahatma Gandhi|||On Tue, 23 Mar 2004 21:32:35 -0700, Sue Hoegemeier
<Sue_H@.nomail.please> wrote:
You are correct. To my horror I found that:
sp_renamedb 'pubs', '123'
works, while BOL clearly has strict rules about the first character of
an identifier.
Would it kill MSFT to implement those rules, and not allow identifiers
that it later can't work with?
-Tom.
>Many of the tools do not support non-standard object names.
>Even though SQL Server lets you create objects with invalid
>names they aren't really legal names. You can find the rules
>for object names in books online under the topic Using
>Identifiers
>-Sue
>On Tue, 23 Mar 2004 19:39:31 -0700, Tom van Stiphout
><tom7744@.no.spam.cox.net> wrote:
>|||You could always send your request for this to
sqlwish@.microsoft.com
-Sue
On Wed, 24 Mar 2004 06:56:01 -0700, Tom van Stiphout
<tom7744@.no.spam.cox.net> wrote:
>On Tue, 23 Mar 2004 21:32:35 -0700, Sue Hoegemeier
><Sue_H@.nomail.please> wrote:
>You are correct. To my horror I found that:
>sp_renamedb 'pubs', '123'
>works, while BOL clearly has strict rules about the first character of
>an identifier.
>Would it kill MSFT to implement those rules, and not allow identifiers
>that it later can't work with?
>-Tom.
>

Friday, February 24, 2012

Annotated Mapping Schema

I have a stored procedure that uses the FOR XML EXPLICIT mode to return an xml view of my data. I then insert this xml into another table.
Instead of writing complicated T-SQL with the FOR XML EXPLICIT mode I'd like to use an xsd schema, however I can't find examples that let you apply the transformation in a stored procedure / test it in Query Analyzer. All the examples I've found involve m
apping the schema in the URL or using a SqlXmlCommand object.
Any help would be very much appreciated!
Cheers,
Paul
Mapping Schemas are a client-side technology - actually, they just generate
FOR XML EXPLICIT statements on the server (you can see this by running a
trace when retrieving data with a schema). As such, there's no way to
reference them from within a T-SQL sproc. Sorry!
--
Graeme Malcolm
Principal Technologist
Content Master Ltd.
www.contentmaster.com
www.microsoft.com/mspress/books/6137.asp
"Paul Bibby" <Paul Bibby@.discussions.microsoft.com> wrote in message
news:4B7E0882-5EED-4A7F-987D-125262683276@.microsoft.com...
I have a stored procedure that uses the FOR XML EXPLICIT mode to return an
xml view of my data. I then insert this xml into another table.
Instead of writing complicated T-SQL with the FOR XML EXPLICIT mode I'd like
to use an xsd schema, however I can't find examples that let you apply the
transformation in a stored procedure / test it in Query Analyzer. All the
examples I've found involve mapping the schema in the URL or using a
SqlXmlCommand object.
Any help would be very much appreciated!
Cheers,
Paul

AND query in single column

Hi group
I have a rather peculiar question, and I really don't know how to solve
this within an SQL statement:

Given a view (v), that results in:

IDX
------
1a
1b
2a
2c
3a

I'd like to query the view with something like:
SELECT ID FROM v WHERE (X='a' AND X='b') which would result in:

ID
------
1

or in another case:
SELECT ID FROM v WHERE (X='a' OR X='c')
would give:

ID
------
1
2
3

how can this be done?

TIA
bernhard

--
www.daszeichen.ch
remove nixspam to replyBernhard Sturm (sturmnixspam@.datacomm.ch) writes:
> I have a rather peculiar question, and I really don't know how to solve
> this within an SQL statement:
> Given a view (v), that results in:
> ID X
> ------
> 1 a
> 1 b
> 2 a
> 2 c
> 3 a
> I'd like to query the view with something like:
> SELECT ID FROM v WHERE (X='a' AND X='b') which would result in:

SELECT ID FROM v WHERE X = 'a'
INTERSECT
SELECT ID FROM v WHERE X = 'b'

Except that that syntax only works in SQL 2005. For SQL 2000, you
could do:

SELECT ID FROM v a
WHERE X = 'a'
AND EXISTS (SELECT *
FROM v b
WHERE a.ID = b.ID
AND b.X = 'b')

> ID
> ------
> 1
> or in another case:
> SELECT ID FROM v WHERE (X='a' OR X='c')
> would give:

SELECT ID FROM v WHERE X = 'a'
UNION
SELECT ID FROM v WHERE X = 'b'

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

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||Assuming the (id, x) is unique. The following gives the first result
you asked for:

SELECT id
FROM v
WHERE x IN ('A','B')
GROUP BY id
HAVING COUNT(*)=2

This operation is called Relational Division and you can find other
examples and discussion about it under that name.

Your second "OR" query:

SELECT id
FROM v
WHERE x IN ('A','C')
GROUP BY id

--
David Portas
SQL Server MVP
--|||David Portas wrote:
> SELECT id
> FROM v
> WHERE x IN ('A','B')
> GROUP BY id
> HAVING COUNT(*)=2
> This operation is called Relational Division and you can find other
> examples and discussion about it under that name.
> Your second "OR" query:
> SELECT id
> FROM v
> WHERE x IN ('A','C')
> GROUP BY id

thanks a lot for your answer. Departing from your suggestion I have come
to the following solution (which seem to work for my purposes):

SELECT id
FROM v
WHERE x='a' AND EXISTS
(SELECT id FROM v WHERE x='b')
GROUP BY id

This can easily adopted to the 'or' case:

SELECT id
FROM v
WHERE x='a' OR EXISTS
(SELECT id FROM v WHERE x='b')
GROUP BY id

What do you think?

bernhard

--
www.daszeichen.ch
remove nixspam to reply|||Bernhard Sturm (sturmnixspam@.datacomm.ch) writes:
> thanks a lot for your answer. Departing from your suggestion I have come
> to the following solution (which seem to work for my purposes):
> SELECT id
> FROM v
> WHERE x='a' AND EXISTS
> (SELECT id FROM v WHERE x='b')
> GROUP BY id

This does not look good. If you have:

DX
------
1a
10b
2a
2c
3a

you will get back 1, 2 and 3. You need to correlate the subquery with
the main query. Because the subquery is not correlated to the main
query, the EXISTS is just binary flip-flop.

> This can easily adopted to the 'or' case:
> SELECT id
> FROM v
> WHERE x='a' OR EXISTS
> (SELECT id FROM v WHERE x='b')
> GROUP BY id

If there is one row with x = 'b', you will get back all id:s. Again,
this is becaues the subquery is not correlated.

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

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||Erland Sommarskog wrote:
>>
>>SELECT id
>> FROM v
>> WHERE x='a' AND EXISTS
>> (SELECT id FROM v WHERE x='b')
>> GROUP BY id
>
> This does not look good. If you have:
> DX
> ------
> 1a
> 10b
> 2a
> 2c
> 3a
> you will get back 1, 2 and 3. You need to correlate the subquery with
> the main query. Because the subquery is not correlated to the main
> query, the EXISTS is just binary flip-flop.

true.. I just realised it as well... maybe I give your solution a try as
well. The thing is: this is all part of an other query, and I am pulling
the queries together via an ASP script, so they need to be simple. but
maybe may way of doing it is not simple ;-)

this

SELECT id
FROM v
WHERE x IN ('A','B')
GROUP BY id
HAVING COUNT(*)=2

doesn't seem to work... x IN ('a','b') provokes an SQL Server error.

cheers
bernhard

--
www.daszeichen.ch
remove nixspam to reply|||David Portas wrote:
> Assuming the (id, x) is unique. The following gives the first result
> you asked for:
> SELECT id
> FROM v
> WHERE x IN ('A','B')

can this be formulated using a LIKE '%a%' statement as well?

> GROUP BY id
> HAVING COUNT(*)=2

--
www.daszeichen.ch
remove nixspam to reply|||> doesn't seem to work... x IN ('a','b') provokes an SQL Server error.

Are you going to tell us what the error is, or just let us take a wild
guess?

It works for me if I run this code:
CREATE TABLE v (id INTEGER, x CHAR(1), PRIMARY KEY (id,x))

INSERT INTO v SELECT 1,'A' UNION SELECT 1,'B'

SELECT id
FROM v
WHERE x IN ('A','B')
GROUP BY id
HAVING COUNT(*)=2

Result:

(2 row(s) affected)

id
----
1

(1 row(s) affected)

So what did you do differently? Please post some code that will
actually reproduce the problem, tell us the error message and the
version, edition and service pack you are using.

--
David Portas
SQL Server MVP
--|||David Portas wrote:
>>doesn't seem to work... x IN ('a','b') provokes an SQL Server error.
>
> Are you going to tell us what the error is, or just let us take a wild
> guess?

sorry.. my fault:
I used the expression
x IN (LIKE '%a%', LIKE '%b%') which did result in an error (incorrect
syntax error)

> So what did you do differently? Please post some code that will
> actually reproduce the problem, tell us the error message and the
> version, edition and service pack you are using.

CODE:
SELECT id
FROM v
WHERE x IN (LIKE '%a%', LIKE'%b%')
GROUP BY id
HAVING COUNT(*)=2

Error:
Incorrect Syntax near the keyword 'LIKE'

SQL Server 8.0 MSDE S.Pack 3/S.Pack 3a

cheers
Bernhard

--
www.daszeichen.ch
remove nixspam to reply|||Maybe this is what you intended:

SELECT id
FROM v
WHERE x LIKE '%a%' OR x LIKE '%b%'
GROUP BY id
HAVING COUNT(*)=2

But are you sure you want to use LIKE here? This is not the same as
what you originally asked for because LIKE will catch any x that
*contains* the character "A" or "B", not just x = "A" or x = "B". If
that's what you want then that will probably invalidate my assumption
about the key of your table and my query won't work as I intended. You
may have to replace COUNT(*) with COUNT(DISTINCT x).

Note that the best way to get help with your problem is to post DDL (a
CREATE TABLE statement, including keys and constraints), sample data (a
few INSERT statements) and your required results. That way we won't
have to keep guessing exactly what you mean.

See: http://www.aspfaq.com/show.asp?id=2120

--
David Portas
SQL Server MVP
--|||David Portas wrote:
> Maybe this is what you intended:
> SELECT id
> FROM v
> WHERE x LIKE '%a%' OR x LIKE '%b%'
> GROUP BY id
> HAVING COUNT(*)=2

no, as my ID is unique I only want records 'sharing' the same ID AND
fullfilling the AND LIKE criteria.

ID X
------
1 auvw
1 buvw
2 axyz
2 cxyz
3 aklm

your solution would result in:

ID
--
1
2
3

but I only want to get (in my AND-problem):

ID
--
1

> But are you sure you want to use LIKE here? This is not the same as
> what you originally asked for because LIKE will catch any x that
> *contains* the character "A" or "B", not just x = "A" or x = "B".

Yes. This is correct. I have formulated the problem in a more
'simplified' way. My problem is far more 'complicated'. But I have
followed your suggestion about the relaional divison, and think I've
found a way to solve the problem (In the 'real world' I need to check
for 3 criteria):

SELECT id
FROM v AS FG1
WHERE x LIKE '%a%' AND EXISTS
(SELECT *
FROM v AS FG2
WHERE FG1.id = FG2.id AND FG2.x LIKE "%b%" AND EXISTS
(SELECT * FROM v AS FG3
WHERE FG1.id = FG2.id AND FG3.id = FG1.id AND FG3.x LIKE "%c%"))

As far as I understand it (and tested it), this seems to cover my 'AND'
problem.

--
www.daszeichen.ch
remove nixspam to reply|||David Portas wrote:
> Maybe this is what you intended:
> SELECT id
> FROM v
> WHERE x LIKE '%a%' OR x LIKE '%b%'
> GROUP BY id
> HAVING COUNT(*)=2

sorry.. I was too quick with my reply.. your solution works as well :-)
puhhh.. long way to learn for me.
The only thing I don't understand is the HAVING COUNT(*)=2 here. Why is
that?

--
www.daszeichen.ch
remove nixspam to reply|||> your solution would result in:

> ID
> --
> 1
> 2
> 3

I don't think you tested that out. If you try it I think you will find
my query gives the result you asked for. Anyway it looks like your
version will work too. It's just rather more typing :-)

--
David Portas
SQL Server MVP
--|||David Portas wrote:
>>your solution would result in:
>
>>ID
>>--
>>1
>>2
>>3
>
> I don't think you tested that out. If you try it I think you will find
> my query gives the result you asked for. Anyway it looks like your
> version will work too. It's just rather more typing :-)

mine was too complicated, yours is perfect for my needs :-) Thanks a
lot. Now I have to implement this into another query... but thanks: it
works!

cheers
bernhard

--
www.daszeichen.ch
remove nixspam to reply|||Bernhard Sturm (sturmnixspam@.datacomm.ch) writes:
> David Portas wrote:
>> Maybe this is what you intended:
>>
>> SELECT id
>> FROM v
>> WHERE x LIKE '%a%' OR x LIKE '%b%'
>> GROUP BY id
>> HAVING COUNT(*)=2
>
> sorry.. I was too quick with my reply.. your solution works as well :-)
> puhhh.. long way to learn for me.
> The only thing I don't understand is the HAVING COUNT(*)=2 here. Why is
> that?

HAVING is like WHERE, but it is applied after GROUP BY. And for this
reason you can use aggregates in HAVING.

If you run

SELECT id, cnt = COUNT(*)
FROM v
WHERE x LIKE '%a%' OR x LIKE '%b%'
GROUP BY id
-- HAVING COUNT(*)=2

You will get:

id cnt
-- --
1 2
2 1
3 1

But you want the row with cnt = 2. Normally you do would do this with a
WHERE clause, but you cannot use COUNT(*) in a WHERE clause. Hence,
HAVING to the rescue.

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

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

Sunday, February 19, 2012

Analyzing db Growth per day

Well my question is how do i analyze db growth per day. is there a tool i can use or a method. I mean i do take a look at the task view and the files but per day it doesnt move in MB wich is weird since this is a warehouse and their are nightly loads to it inserting maybe 30000 record a night on avg.

Any help would be grately aprreciated.sp_helpdb 'MyDatabaseName'|||The easiest way to do it is to check in Enterprise manager daily to see what the current size is, then keep the daily records in a spreadsheet, or database. If you are looking for a pre-packaged too, I think Quest Software has something for that. I think Microsoft is also getting into the game, as well, with a product I can not remember the name of. Some SQL Server add on.|||Or you could write a job that copies sysobjects and sysindexes into a table that you aggregate on a regualr basis. Swipe code from sp_spaceused to understand how to use that information.

Monday, February 13, 2012

Analysis Services Perfomance Issues (when open a cube definition)

we are dealing whit a perfomance issue in Analysis Server 2005 working with olap cubes. This cubes are part of a solution with a view (91 tables) and every time we try to access some meta data of the cube in the Visual Studio 2005 (i.e.: Calculations, KPIs, etc) it takes a lot of time to read the metadata of the cube. The cubes are not so big, but every time we try to get the cube metadata we have problems.In Example; it takes more than 3 minutes to get the structure of the cube in the browse section; but after this we can easily drop fields to the sections of the cube designer and watch the cube data and calculations.

We have the same problem in Excel 2007 when we try to connect to the Analysis Services database and read some information; when we get the definition of the cubes, then the data takes a normal time to be filled.

Does anybody has some ideas or suggestions ?

Regards

Bruno

Do you have any ROLAP dimensions or a lot of security roles?|||

Hi Jeffrey

no, we currently have 1 one that call an advanced function and uses a .Net Assembly in order to check the security for some objects. I don't know if this may affect the "browser" perfomance.

Regards

Bruno

|||

Do you have dimensions with ragged hierarchies? Are you on SQL Server 2005 RTM, SP1 or SP2?

You can attach the Profiler to the AS Server to identify which queries and/or commands take a long time to complete.

Tuesday, March 20, 2012

Another version of SQL Permission Question

Another version of SQL Permission Question

Another version of SQL Permission Question

Monday, March 19, 2012

Another row to col issue

Thursday, March 8, 2012

Another example of incomplete support of some legal database names

Friday, February 24, 2012

Annotated Mapping Schema

AND query in single column

Sunday, February 19, 2012

Analyzing db Growth per day

Monday, February 13, 2012

Analysis Services Perfomance Issues (when open a cube definition)

SQL server connection

Blog Archive

About Me