Role Playing dimensions Currency Conversion issue

Hi,

Having issues with Currency Conversion in SSAS when using role playing dimensions of Time.

When the Dim Usage in the cube is set for the role playing dimensions,
i.e. defining the relation between the Exchange rate fact and the role playing time dims.
the base $ measures (no calculations) return a null value.

The generated conversion script in Calculations has leaves(Time), not the leaves for each role playing dim.
With leaves for each along with leaves(time), the base measures return null.

Regards

does ssas 2005 currency conversion work when using the linkmember function in calculations.

Role Playing Dimensions

What is the proper way to include the date dimension table into your DSV if you plan on using multiple role-playing dimensions? It seems if you can use this dimension as a role playing dimension, and assign the "join" using the Dimension Usage tab, then it doesn't need to be joined to the fact table in the DSV... is that correct? Or should you add the Date Dim table to the DSV and join it to every date-specific fact foreign keys? I see three possibilities for the DSV:

1) Include the Date Dim table but not join to any other table in the DSV

2) Include the Date Dim table and join to at least one fact foreign key

3) Include the Date Dim table and join to all fact foreign keys for dates in the DSV

Which method would be the most appropriate (or what are the consequences of each method)?

Also, if you use a role-playing dimension, is there anyway of manipulating the levels to display the type of date. For example, of the user picks the Order Date dimension, the year level says "Order Year" but if they use the Shipped Date dimension, the year level would say "Shipped Year"... etc.?

Thanks

Kory

Hi KoryS. Check the Adventure Works sample BI project that clearly shows that no 3 is the correct way of doing it. It is a very simple concept, instead of using three tables of views for time you join one table to the different time keys in the fact table. There have been some concerns regarding using role playing dimensions like here(http://mgarner.wordpress.com/2006/06/27/role-playing-dimensions-not-materialized/) but now it is OK regarding performance.

Regards

/Thomas Ivarsson

|||

Thanks- I'll follow the example from the Adventureworks db.

Do you know if I can rename some of the levels in my role-playing dimensions (see second half of my original thread)?

-Kory

|||

I think you will have to use the same names but you name each dimension like DueDate, OrderDate, ShipDate and so on. You can see this is in the Adventure Works AS2005 project.

Regards

/Thomas

sql

Role playing dimension and member naming question.

I have a fact table with invoice information that has multiple date columns.

I had originaly only needed to join my time dimension to this fact table on it's create date, but I have now added a role-playing dimension to join to the invoice date.

When I had 1 date dimension all of it's members where called 'week','year', 'day', etc.
Now that I have the role-playing dimension I have two dimensions with member names like 'Date.week', 'Date.year', 'Date.day', 'Invoice Date.week', 'Invoice Date.year', 'Invoice Date.day'.

So many queries I had written to reference the original date dimension no longer work because of the extra 'Date.' prefix. Is there a way to hide this prefix for my original date dimension?

Thanks in advance.

I had the same problem earlier on. I had to bite the bullet and change my MDX when I had multiple 'date' dimensions. Can you globally change the MDX or are you using a third party tool?

Unless someone knows better.

|||

I am using ProClarity as a front end to the cube. I have found a sort of work around, rather than creating a role-playing dimension. I created a whole new date dimension on the same date table and it doesn't mess up the naming. The only downside I can see to this right now is that if I have one date dimension on rows and one date dimension on columns your result could look like:

may june july august
may
june
july
august

so it's not really clear which dimension is where, but I could get around this by changing the names of the members in the new date dimension.

-Preston

|||

Hi,

Although this solution will ultimately work, I guess we are duplicating process and space by repeating a dimension.

I've not tested it thorougly yet, but, the cube dimension has a property named HierarchyUniqueNameStyle, that allows for two values: IncludeDimensionName and ExcludeDimensionName. Using the later in the "default" dimension, let's say "Date" vs. "Delivery Date", the MDX will run fine as it was originally, without need to edit all of them.

Jordi Rambla

SQL Server MVP

Certia (http://www.certia.net)

SolidQualityLearning (http://www.solidqualitylearning.com)

Role permissions disappear

I created a new database role to give a number of users select privilege only on some tables and every day I have to go back in and add the tables back to the role. Is there something I'm missing here?First, you don't add tables to roles...you grant permissions to users on objects.

Second, it sounds like you're either recreating the tables every night (unlikely), or youre doing a restore...

which is it...

There are no miracles...|||I think you hit the nail on the head. Most if not all of these tables are dropped and recreated every night. Doh!!! Sorry, I'm stumbling through this. Our DBA up and quit out of the blue and this got dumped in my lap.|||No sweat...set this up as a stored procedure, then schedule it as the last step of your nightly batch job..

just change PUBLIC to whatever role yo have...

DECLARE myGrants99 CURSOR FOR
SELECT 'GRANT SELECT ON [' + TABLE_NAME + '] TO PUBLIC '
FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_TYPE = 'BASE TABLE'

OPEN myGrants99

DECLARE @.SQL varchar(8000)

FETCH NEXT FROM myGrants99 INTO @.SQL

WHILE @.@.FETCH_STATUS = 0
BEGIN
EXEC(@.SQL)
FETCH NEXT FROM myGrants99 INTO @.SQL
END

CLOSE myGrants99
DEALLOCATE myGrants99|||Thanks for this. It'll help tremendously. I'll give this to the guys that created the DTS packages to do the loads.|||OK, Now I have a silly question...

Why are they dropping the tables?

You'd be better off if they TRUNCATE the tables...

alos I'm assuming this is not an OTLP (going out on a limb, eh) database, ratyher a reporting/ warehouse...right?

Is there any RI?

Do you work in the Northeast US?

I work cheap...

:D|||Don't know why they're dropping and not truncating. I will suggest that to the developers. And this is a reporting warehouse for Crystal. Sorry, but working for a major telecommunications company doesn't allow me to contract outside assistance. OJT is our main source of training. Sounds really stupid doesn't it?|||Naaaahhhh

Jump in to the pool...deep end...feet first...keep your head above water...
Also, Go out and buy (and read) some good books...

Check out:

http://www.sqlteam.com/store.asp

EDIT: Oh, and keeping coming back to here or sql team...|||I'll definitely keep coming back. The response has been great. This is the second question I've posted here and both have been answered quickly.

Thanks again.

Role Permissions

Hi,
I am trying the following on SQL Server 2005.
I want to find out the permissions for different roles on database and
database objects.
So, selected all the records from the "sys.database_permissions" catalog
view and joined it with the "sys.all_objects" catalog view.
But I found some objects that have the permissions assigned to them but
don't have an entry in sys.all_objects.
This behavior was for the "master" database.
The object ids for the objects is as follows :
-233346666
-282896470
-986524149
-369557355
The permission assigned is Select for all the above objects for public role.
I want to know what these objects are.
TIA
Prasad
Not all securables are objects. What class_desc have those objects in
sys.database_permissions?
This posting is provided "AS IS" with no warranties, and confers no rights.
HTH,
~ Remus Rusanu
SQL Service Broker
http://msdn2.microsoft.com/en-us/library/ms166043(en-US,SQL.90).aspx
"Prasad" <ekke_nikhil@.yahoo.co.uk> wrote in message
news:%23Wg9iEyGGHA.1088@.tk2msftngp13.phx.gbl...
> Hi,
> I am trying the following on SQL Server 2005.
> I want to find out the permissions for different roles on database and
> database objects.
> So, selected all the records from the "sys.database_permissions" catalog
> view and joined it with the "sys.all_objects" catalog view.
> But I found some objects that have the permissions assigned to them but
> don't have an entry in sys.all_objects.
> This behavior was for the "master" database.
> The object ids for the objects is as follows :
> -233346666
> -282896470
> -986524149
> -369557355
> The permission assigned is Select for all the above objects for public
> role.
> I want to know what these objects are.
> TIA
> Prasad
>
|||Those objects have the class_desc as "OBJECT_OR_COLUMN"
"Remus Rusanu [MSFT]" <Remus.Rusanu.NoSpam@.microsoft.com.nowhere.moon> wrote
in message news:eibDbr0GGHA.1032@.TK2MSFTNGP11.phx.gbl...
> Not all securables are objects. What class_desc have those objects in
> sys.database_permissions?
> --
> This posting is provided "AS IS" with no warranties, and confers no
> rights.
> HTH,
> ~ Remus Rusanu
> SQL Service Broker
> http://msdn2.microsoft.com/en-us/library/ms166043(en-US,SQL.90).aspx
>
> "Prasad" <ekke_nikhil@.yahoo.co.uk> wrote in message
> news:%23Wg9iEyGGHA.1088@.tk2msftngp13.phx.gbl...
>
|||You can safely ignore these four objects, they shouldn't be there. You
should use http://lab.msdn.microsoft.com/productfeedback/ to report this
problem.
But in general your query will not work for securables that are not objects,
e.g. services. You can test this by running this:
GRANT SEND ON
SERVICE::[http://schemas.microsoft.com/SQL/Not...icationService]
TO [Public];
This posting is provided "AS IS" with no warranties, and confers no rights.
HTH,
~ Remus Rusanu
SQL Service Broker
http://msdn2.microsoft.com/en-us/library/ms166043(en-US,SQL.90).aspx
"Prasad" <ekke_nikhil@.yahoo.co.uk> wrote in message
news:eUPU8f%23GGHA.532@.TK2MSFTNGP15.phx.gbl...
> Those objects have the class_desc as "OBJECT_OR_COLUMN"
> "Remus Rusanu [MSFT]" <Remus.Rusanu.NoSpam@.microsoft.com.nowhere.moon>
> wrote in message news:eibDbr0GGHA.1032@.TK2MSFTNGP11.phx.gbl...
>

Role Permissions

Hi,
I am trying the following on SQL Server 2005.
I want to find out the permissions for different roles on database and
database objects.
So, selected all the records from the "sys.database_permissions" catalog
view and joined it with the "sys.all_objects" catalog view.
But I found some objects that have the permissions assigned to them but
don't have an entry in sys.all_objects.
This behavior was for the "master" database.
The object ids for the objects is as follows :
-233346666
-282896470
-986524149
-369557355
The permission assigned is Select for all the above objects for public role.
I want to know what these objects are.
TIA
PraNot all securables are objects. What class_desc have those objects in
sys.database_permissions?
This posting is provided "AS IS" with no warranties, and confers no rights.
HTH,
~ Remus Rusanu
SQL Service Broker
http://msdn2.microsoft.com/en-us/library/ms166043(en-US,SQL.90).aspx
"Pra" <ekke_nikhil@.yahoo.co.uk> wrote in message
news:%23Wg9iEyGGHA.1088@.tk2msftngp13.phx.gbl...
> Hi,
> I am trying the following on SQL Server 2005.
> I want to find out the permissions for different roles on database and
> database objects.
> So, selected all the records from the "sys.database_permissions" catalog
> view and joined it with the "sys.all_objects" catalog view.
> But I found some objects that have the permissions assigned to them but
> don't have an entry in sys.all_objects.
> This behavior was for the "master" database.
> The object ids for the objects is as follows :
> -233346666
> -282896470
> -986524149
> -369557355
> The permission assigned is Select for all the above objects for public
> role.
> I want to know what these objects are.
> TIA
> Pra
>|||Those objects have the class_desc as "OBJECT_OR_COLUMN"
"Remus Rusanu [MSFT]" <Remus.Rusanu.NoSpam@.microsoft.com.nowhere.moon> wrote
in message news:eibDbr0GGHA.1032@.TK2MSFTNGP11.phx.gbl...
> Not all securables are objects. What class_desc have those objects in
> sys.database_permissions?
> --
> This posting is provided "AS IS" with no warranties, and confers no
> rights.
> HTH,
> ~ Remus Rusanu
> SQL Service Broker
> http://msdn2.microsoft.com/en-us/library/ms166043(en-US,SQL.90).aspx
>
> "Pra" <ekke_nikhil@.yahoo.co.uk> wrote in message
> news:%23Wg9iEyGGHA.1088@.tk2msftngp13.phx.gbl...
>|||You can safely ignore these four objects, they shouldn't be there. You
should use http://lab.msdn.microsoft.com/productfeedback/ to report this
problem.
But in general your query will not work for securables that are not objects,
e.g. services. You can test this by running this:
GRANT SEND ON
SERVICE::[[url]http://schemas.microsoft.com/SQL/Notifications/EventNotificationService[
/url]]
TO [Public];
This posting is provided "AS IS" with no warranties, and confers no rights.
HTH,
~ Remus Rusanu
SQL Service Broker
http://msdn2.microsoft.com/en-us/library/ms166043(en-US,SQL.90).aspx
"Pra" <ekke_nikhil@.yahoo.co.uk> wrote in message
news:eUPU8f%23GGHA.532@.TK2MSFTNGP15.phx.gbl...
> Those objects have the class_desc as "OBJECT_OR_COLUMN"
> "Remus Rusanu [MSFT]" <Remus.Rusanu.NoSpam@.microsoft.com.nowhere.moon>
> wrote in message news:eibDbr0GGHA.1032@.TK2MSFTNGP11.phx.gbl...
>

Role Permissions

Hi,
I am trying the following on SQL Server 2005.
I want to find out the permissions for different roles on database and
database objects.
So, selected all the records from the "sys.database_permissions" catalog
view and joined it with the "sys.all_objects" catalog view.
But I found some objects that have the permissions assigned to them but
don't have an entry in sys.all_objects.
This behavior was for the "master" database.
The object ids for the objects is as follows :
-233346666
-282896470
-986524149
-369557355
The permission assigned is Select for all the above objects for public role.
I want to know what these objects are.
TIA
Prasad
Not all securables are objects. What class_desc have those objects in
sys.database_permissions?
This posting is provided "AS IS" with no warranties, and confers no rights.
HTH,
~ Remus Rusanu
SQL Service Broker
http://msdn2.microsoft.com/en-us/library/ms166043(en-US,SQL.90).aspx
"Prasad" <ekke_nikhil@.yahoo.co.uk> wrote in message
news:%23Wg9iEyGGHA.1088@.tk2msftngp13.phx.gbl...
> Hi,
> I am trying the following on SQL Server 2005.
> I want to find out the permissions for different roles on database and
> database objects.
> So, selected all the records from the "sys.database_permissions" catalog
> view and joined it with the "sys.all_objects" catalog view.
> But I found some objects that have the permissions assigned to them but
> don't have an entry in sys.all_objects.
> This behavior was for the "master" database.
> The object ids for the objects is as follows :
> -233346666
> -282896470
> -986524149
> -369557355
> The permission assigned is Select for all the above objects for public
> role.
> I want to know what these objects are.
> TIA
> Prasad
>
|||Those objects have the class_desc as "OBJECT_OR_COLUMN"
"Remus Rusanu [MSFT]" <Remus.Rusanu.NoSpam@.microsoft.com.nowhere.moon> wrote
in message news:eibDbr0GGHA.1032@.TK2MSFTNGP11.phx.gbl...
> Not all securables are objects. What class_desc have those objects in
> sys.database_permissions?
> --
> This posting is provided "AS IS" with no warranties, and confers no
> rights.
> HTH,
> ~ Remus Rusanu
> SQL Service Broker
> http://msdn2.microsoft.com/en-us/library/ms166043(en-US,SQL.90).aspx
>
> "Prasad" <ekke_nikhil@.yahoo.co.uk> wrote in message
> news:%23Wg9iEyGGHA.1088@.tk2msftngp13.phx.gbl...
>
|||You can safely ignore these four objects, they shouldn't be there. You
should use http://lab.msdn.microsoft.com/productfeedback/ to report this
problem.
But in general your query will not work for securables that are not objects,
e.g. services. You can test this by running this:
GRANT SEND ON
SERVICE::[http://schemas.microsoft.com/SQL/Not...icationService]
TO [Public];
This posting is provided "AS IS" with no warranties, and confers no rights.
HTH,
~ Remus Rusanu
SQL Service Broker
http://msdn2.microsoft.com/en-us/library/ms166043(en-US,SQL.90).aspx
"Prasad" <ekke_nikhil@.yahoo.co.uk> wrote in message
news:eUPU8f%23GGHA.532@.TK2MSFTNGP15.phx.gbl...
> Those objects have the class_desc as "OBJECT_OR_COLUMN"
> "Remus Rusanu [MSFT]" <Remus.Rusanu.NoSpam@.microsoft.com.nowhere.moon>
> wrote in message news:eibDbr0GGHA.1032@.TK2MSFTNGP11.phx.gbl...
>
sql