Accessing lower level members

Hi All,

I have used .Children function to retrieve members of dimension at one
level below the current level of dimension. For example (using FoodMart
2000 and AS 2000)

Select NON EMPTY ( { [Measures].[Unit Sales] } ) ON COLUMNS,
NON EMPTY ( [Customers].[Country].[USA].Children ) on rows

>From sales

This query returns me Children of member Country which is USA. It
returns all members of State Provinces which have USA as its parent.

Can I access or retrieve all members of City with Country member being
USA?

I am building a web application where user applies filters. This is
necessary because if user wants to view sales data about City which
belongs to country USA.

Many thanks in advance.

Raghu

Hi Raghu. Yes, you can retrieve members of City where the Country is USA. Use the MDX DESCENDANTS() function. Change your query to the following:

Select NON EMPTY ( { [Measures].[Unit Sales] } ) ON COLUMNS,
NON EMPTY ( DESCENDANTS([Customers].[Country].[USA], [Customers].[City]) ) on rows

From sales

Here's a link to the BOL description of the DESCENDANTS() function:

http://msdn2.microsoft.com/en-us/library/ms146075.aspx

Good luck - Paul Goldy

|||

Thanks Paul.

This solved it.

Raghu

Access Vs SQL Server

Hello!

I am trying to gather information on why MS SQL Server 2000 is a better enterprise level database management system MS Access 2002. There is an article on the Microsoft Technet, but when I try and access the article I get a 404 error. Link is there, but page behind is missing.

http://www.micrsoft.com/sql/techinfo/planning/SQLAccess.asp

I know the basics, just want stats and facts to back them up.

Thanks...Originally posted by swestenhofer
Hello!

I am trying to gather information on why MS SQL Server 2000 is a better enterprise level database management system MS Access 2002. There is an article on the Microsoft Technet, but when I try and access the article I get a 404 error. Link is there, but page behind is missing.

http://www.micrsoft.com/sql/techinfo/planning/SQLAccess.asp

I know the basics, just want stats and facts to back them up.

Thanks...

1) Volume of data
2) Scalbility
3) Performance
4) OLAP
5) Security features
6) Easy Query processing
7) Data distribution and replication
8) Job scheduling
9) Programmability

There are lots of reasons like this. Access is good if your data is less than 2GB and depends what you are looking from a database.|||Access is not a database "Server". Thus, if you have a query in Access that joins a parent table with 10,000 rows to a child table with 100,000 to return a filtered set of records consisting of, say 200 rows, Access will transfer all 110,000 records from both tables over your network and then perform the SQL statement on whatever (slow) desktop the user happens to be at.

Under SQL Server, all the calculation is done on the (powerfull quad-processor, RAM out the wazzoo) server, and only the final 200 rows are returned over your network.

In a nutshell, running Access as an Enterprise solution drags a network to its knees.|||Originally posted by blindman
Access is not a database "Server". Thus, if you have a query in Access that joins a parent table with 10,000 rows to a child table with 100,000 to return a filtered set of records consisting of, say 200 rows, Access will transfer all 110,000 records from both tables over your network and then perform the SQL statement on whatever (slow) desktop the user happens to be at.

Under SQL Server, all the calculation is done on the (powerfull quad-processor, RAM out the wazzoo) server, and only the final 200 rows are returned over your network.

In a nutshell, running Access as an Enterprise solution drags a network to its knees.

Thanks all!! This should be good fuel for my client to use... thanks for the help!|||Hey...no one told him about the cost...

What's the differences between a kia and a bentley?|||Originally posted by Brett Kaiser
Hey...no one told him about the cost...

What's the differences between a kia and a bentley?

I know the cost differences involved, and the client is aware as well, and in the long run, user connection timeouts and database size limitations may outway the costs based on recovery and migration...

Thanks again!

access to data in the cube

Hi,

i'm currently building a cube which data has a level of access depending who the logged in user is in the windows authentication. My cube's fact table is actually a VIEW with this line of code:

SELECT a.serviceid AS [service code], a.agentid AS [agent code], a.timeid AS date, a.talktime AS [talk time], a.wraptime AS [wrap time]
FROM dbo.factAgentActivity AS a INNER JOIN
dbo.factaccess AS b ON a.agentid = b.agent AND b.username = SUSER_NAME()

I used SUSER_NAME to get the name of the user currently logged in and compared it against the username table. If i open this VIEW in the SQL SERVER database, i can retrieve the right data that user will see. But when I used this in the Analysis Services and Browse the cube, the metrics and cubes have blank values. The same thing if i link my cube to excel.

Is my setup correct or im missing something here?

thanks in advance!

regards,

cherriesh

I'm not sure to understand but:

"SUSER_NAME()" return the current user logged in.

It means that when you process the cube "SUSER_NAME()" is replaced with the AS account.

If it is correct, how can AS dynamically change the user on browser time?

Maybe I'm missing something.

Francesco

|||if you already know what access they should have, why wouldn't you just create a role(s) within your SSAS database project and assign the NT Users (or NT Groups) accordingly. Depending on your storage mode for your cube once you process the cube the view wouldn't work anyway, that would just be used to perform the initial load of the cube.

access problem while connecting sql 2k in win 2k3

when i am connecting the sql server in windows 2003 using the sql analyser t
he following error occurs
Server: Msg 17, Level 16, State 1
[Microsoft][ODBC SQL Server Driver][TCP/IP Sockets]SQL Server do
es not exist or access denied.
the authentication mode is mixed.
ICF is enabled.
aliasing used is TCP/ip
NetBios is also enabled
Message posted via http://www.droptable.comHi
Is port 1433 opened? You need a minimum of this when using SQL
authentication.
If you use Integrated, you need the 135, 136 and 139 open too.
Regards
--
Mike Epprecht, Microsoft SQL Server MVP
Zurich, Switzerland
IM: mike@.epprecht.net
MVP Program: http://www.microsoft.com/mvp
Blog: http://www.msmvps.com/epprecht/
"abhilash c via droptable.com" <forum@.droptable.com> wrote in message
news:5c7648275a5d424f9056a2250fce8d53@.SQ
droptable.com...
> when i am connecting the sql server in windows 2003 using the sql analyser
the following error occurs
> Server: Msg 17, Level 16, State 1
> [Microsoft][ODBC SQL Server Driver][TCP/IP Sockets]SQL Server does not

exist or access denied.
> the authentication mode is mixed.
> ICF is enabled.
> aliasing used is TCP/ip
> NetBios is also enabled
> --
> Message posted via http://www.droptable.com

access problem while connecting sql 2k in win 2k3

when i am connecting the sql server in windows 2003 using the sql analyser the following error occurs
Server: Msg 17, Level 16, State 1
[Microsoft][ODBC SQL Server Driver][TCP/IP Sockets]SQL Server does not exist or access denied.
the authentication mode is mixed.
ICF is enabled.
aliasing used is TCP/ip
NetBios is also enabled
Message posted via http://www.sqlmonster.com
Hi
Is port 1433 opened? You need a minimum of this when using SQL
authentication.
If you use Integrated, you need the 135, 136 and 139 open too.
Regards
Mike Epprecht, Microsoft SQL Server MVP
Zurich, Switzerland
IM: mike@.epprecht.net
MVP Program: http://www.microsoft.com/mvp
Blog: http://www.msmvps.com/epprecht/
"abhilash c via SQLMonster.com" <forum@.SQLMonster.com> wrote in message
news:5c7648275a5d424f9056a2250fce8d53@.SQLMonster.c om...
> when i am connecting the sql server in windows 2003 using the sql analyser
the following error occurs
> Server: Msg 17, Level 16, State 1
> [Microsoft][ODBC SQL Server Driver][TCP/IP Sockets]SQL Server does not
exist or access denied.
> the authentication mode is mixed.
> ICF is enabled.
> aliasing used is TCP/ip
> NetBios is also enabled
> --
> Message posted via http://www.sqlmonster.com

Access Front End and SQL Server 2000 Record Level Access Control

Hello,

We are using Access ADP as a front end, SQL Server 2000 as a back end.
We have a customer contact database. We would like to limit certain
users to only receive certain records based on Windows NT group
membership.

For example, Eastern Sales Group can see clients located in their
region, but they cannot see clients located in the Northern Region.

Is there an elegant way to do this? Below a two solutions which have
been proposed, but none seem to fit. Access is required as a Front End
for its ease of use.

*********
** 1 **
*********
Add an additional attribute (bit mask value) to tblCustomers, and
query appropriately based on the user's group membership.

Problem:
Access allows users direct access to the underlying table.

*********
** 2 **
*********
Create a separe table for each group (effectively splitting
tblCustomers into smaller, separate tables based on group access).
Then, apply SQL Server security on the objects to enforce the business
rules.

Problem:
Does this break Normal Form? I've never seen a solution like this.

I've googled and found similar questions, but not a good solution.

Any suggestions would be appreciated.

Thanks,
Jeff
Jeffrey Walton
noloader.at.yahoo.com"Noloader" <noloader@.yahoo.com> wrote in message
news:6b543aa7.0404231447.777fe29c@.posting.google.c om...
> Hello,
> We are using Access ADP as a front end, SQL Server 2000 as a back end.
> We have a customer contact database. We would like to limit certain
> users to only receive certain records based on Windows NT group
> membership.
> For example, Eastern Sales Group can see clients located in their
> region, but they cannot see clients located in the Northern Region.
> Is there an elegant way to do this? Below a two solutions which have
> been proposed, but none seem to fit. Access is required as a Front End
> for its ease of use.
> *********
> ** 1 **
> *********
> Add an additional attribute (bit mask value) to tblCustomers, and
> query appropriately based on the user's group membership.
> Problem:
> Access allows users direct access to the underlying table.
> *********
> ** 2 **
> *********
> Create a separe table for each group (effectively splitting
> tblCustomers into smaller, separate tables based on group access).
> Then, apply SQL Server security on the objects to enforce the business
> rules.
> Problem:
> Does this break Normal Form? I've never seen a solution like this.
> I've googled and found similar questions, but not a good solution.
> Any suggestions would be appreciated.
> Thanks,
> Jeff
> Jeffrey Walton
> noloader.at.yahoo.com

In general, there is no reason why your users should need to access tables
directly. If you manage data access through stored procedures, then the
procedures can check role membership and only return or update those rows
which are permitted to the user. You could also use views, based on role
membership, or use application roles and handle everything in the client
application.

I would avoid solution 2, because it duplicates data - whatever information
you use to partition the data could also be put in a column. But if you have
a large amount of data, and if users only access their own data, then this
might also be an option.

Simon|||RE/
>We have a customer contact database. We would like to limit certain
>users to only receive certain records based on Windows NT group
>membership.

Based on what little experience I've had doing an Access front end against SQL
Server, I'd set SQL Server security so that nobody and nothing can get directly
to the tables in question and then develop stored procedures and/or views to
move the data back-and-forth between tables and app.
--
PeteCresswell