Showing posts with label correctly. Show all posts
Showing posts with label correctly. Show all posts

Saturday, February 25, 2012

Attributes, Properties; Dimensions, Levels, and Hierarchies, oh my!

I have a most basic question: how to correctly access properties (attributes) of dimension members that are at a different level. Here's a simple query from AdventureWorks to illustrate:

Code Snippet

select

[Customer].[Customer].members

dimension properties

[customer].[customer].[postal code],

[customer].[postal code].[city]

on 1,

[Measures].[Internet Sales Amount]

on 0

from [Adventure Works]

This query works, but the city property is left out of the results (postal code is provided). How can I get City, State-Province, Country, etc, as properties in a query like this? (As the relationships are all correctly defined, and the levels for these attributes/properties are less restrictive than the query level, I'm totally missing why this doesn't work in either the form given -- but do I have the name perhaps wrong?)

Seperately, I have read (somewhere) that property access is to be discouraged in SSAS05, instead prefering direct attribute access via UDM; is there a better way to write these sorts of queries, say including City, etc in the output grid area?

Thanks as always!

To somewhat answer my own query, I have found that:

Code Snippet

with member [measures].[cc] as [customer].[city].MemberValue + ', ' + [customer].[state-province].MemberValue

select

[Customer].[Customer].members

dimension properties

[customer].[customer].[postal code],

[customer].[postal code].[city]

on 1,

{[measures].[cc], [Measures].[Internet Sales Amount]}

on 0

from [Adventure Works]

properly returns the customer "city, state" in the output grid area. But I am still interested in how to obtain the city and state-province instead as properties of the customer members on axis(1).....

Thanks again!

Monday, February 13, 2012

Attaching Database does not name database correctly

Here are the steps I have taken:
I have a situation where I need to rename a database and its physical files
via a scripting process. I am having a problem in the last step where I am
reattaching the renamed files with the new database name...the problem is
that no matter what I attempt to rename the database to it keeps the old
database name in the properties. Incidently this even happens using
Management Studio...if you detach the database, rename the data and log
file, and then reattach it then check the properties of the database under
the files tab and you will see that it kept the original database name.
Example:
Original filename 00002014
1. Detach the database with sp_detach_db - Works fine
2. Set the permissions on the data and log file with cacls.exe - Works fine
3. Create a renamed backup copy of the original data and log files using
xp_cmdshell Copy - Works fine
4. Create a renamed data and log file that is the same as the new database
name will be. - Works fine
5. Create a database for Attaching - Weirdness occurs:
CREATE DATABASE [90002014] ON
( FILENAME = N'D:\Microsoft SQL Server\90002014.mdf' ),
( FILENAME = N'D:\Microsoft SQL Server\90002014_log.ldf' )
FOR ATTACH
The problem that happens is that even though the database is created and
attached correctly.the name when I query "master.dbo.sysaltfiles" is the OLD
database name. So instead of 90002014 it was still the old 00002014 but the
database name on the database in management studio is the new name 90002014.
It is crazy! How do I change the actual name of the database when I do the
CREATE DATABASE FOR ATTACH Procedure. (or for that matter using the
SP_ATTACH_DB stored proc, both have the same problem)
RonYou must be talking about the LOGICAL names of the files not the database
itself. This is normal. To change the LOGICAL names you use the ALTER
DATABASE command with the MODIFY FILE option to change the Logical name to a
new one.
Andrew J. Kelly SQL MVP
"RSH" <way_beyond_oops@.yahoo.com> wrote in message
news:uDbx7jKPGHA.2300@.TK2MSFTNGP15.phx.gbl...
> Here are the steps I have taken:
> I have a situation where I need to rename a database and its physical
> files via a scripting process. I am having a problem in the last step
> where I am reattaching the renamed files with the new database name...the
> problem is that no matter what I attempt to rename the database to it
> keeps the old database name in the properties. Incidently this even
> happens using Management Studio...if you detach the database, rename the
> data and log file, and then reattach it then check the properties of the
> database under the files tab and you will see that it kept the original
> database name.
>
> Example:
> Original filename 00002014
>
> 1. Detach the database with sp_detach_db - Works fine
> 2. Set the permissions on the data and log file with cacls.exe - Works
> fine
> 3. Create a renamed backup copy of the original data and log files using
> xp_cmdshell Copy - Works fine
> 4. Create a renamed data and log file that is the same as the new
> database name will be. - Works fine
> 5. Create a database for Attaching - Weirdness occurs:
>
> CREATE DATABASE [90002014] ON
> ( FILENAME = N'D:\Microsoft SQL Server\90002014.mdf' ),
> ( FILENAME = N'D:\Microsoft SQL Server\90002014_log.ldf' )
> FOR ATTACH
>
> The problem that happens is that even though the database is created and
> attached correctly.the name when I query "master.dbo.sysaltfiles" is the
> OLD database name. So instead of 90002014 it was still the old 00002014
> but the database name on the database in management studio is the new name
> 90002014.
>
> It is crazy! How do I change the actual name of the database when I do
> the CREATE DATABASE FOR ATTACH Procedure. (or for that matter using the
> SP_ATTACH_DB stored proc, both have the same problem)
>
> Ron
>|||That was it...THANKS!
Ron
"Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
news:u2Ogc5KPGHA.3924@.TK2MSFTNGP14.phx.gbl...
> You must be talking about the LOGICAL names of the files not the database
> itself. This is normal. To change the LOGICAL names you use the ALTER
> DATABASE command with the MODIFY FILE option to change the Logical name to
> a new one.
> --
> Andrew J. Kelly SQL MVP
>
> "RSH" <way_beyond_oops@.yahoo.com> wrote in message
> news:uDbx7jKPGHA.2300@.TK2MSFTNGP15.phx.gbl...
>