How to show running totals

I have a cube with many dimensions including one called WorkDate YMD. I also have a measure called Hours.

At the moment I can see hours against any level of the WorkDate dimension, but I have a requirement now for running totals.

For example, the data at the moment is this

Jan 4 hours, Feb 5 hours March 6 Hours

As well as the actual Hours the users now want to see the running totals.

So the data will be

Jan 4 hours, Feb 9 hours March 15 hours

I know this should be easy as a calculated member in the cube using MDX and therefore visible in excel pivot tables but I cannot see how to accomplish this correctly ans easily.

TIA

Hi. If you're WorkDate dimension is of type "Time" you can use the YTD() function as follows:

YTD(WorkDate.WorkDate.CurrentMember, Measures.Hours)

If WorkDate is NOT a type Time dimension you can get the same functionality from:

SUM(PeriodsToDate([WorkDate].[WorkDate].<Level name>, [WorkDate].[WorkDate].[<level name>].CurrentMember), Measures.Hours)

Hope this helps.

PGoldy

|||

Thanks for the info, but I don't think I explained the issue properly, there is another dimension of project so the work date rather than being Year to Date cumulative totals it is cumalitive from the start of the project so for example Nov 5 hours, Dec 2 Hours, Jan 4 Hours Total 11 hours should be viewed as Nov 5 hours, Dec 7 hours, Jan 11 hours

btw, yes WorkDate is a time dimension of Year / Month / Day and my users want to see the cumulative totals on a monthly basis.

TIA

|||

Hi Thanks for further information. Sounds like what you want is a running total for each month which starts at the project begin date. Here's a query which November as the project start, and provides a running total through April (spanning the year boundary).

WITH MEMBER Measures.[Running Total] AS
'
SUM(WorkDate.WorkDate.[November]:WorkDate.WorkDate.CurrentMember, Measures.Hours)
'
SELECT
{WorkDate.WorkDate.November
,WorkDate.WorkDate.December
,WorkDate.WorkDate.January
,WorkDate.WorkDate.February
,WorkDate.WorkDate.March
,WorkDate.WorkDate.April} ON COLUMNS,
{Measures.[Running Total]} ON ROWS
FROM [Your Cube]

Hope this helps.

PGoldy

|||

Hi Paul,

I think I need to push my boss harder to let me go onto an MDX course.

The cube I have is against MS Project server data, so the users want to see cumulative hours for many selected of grouped projects or tasks, so I cannot hard code November to April unfortunately.

I guess if possible I'm after a simple mdx query that I can drop into the calculated section in SS2000 Analysis Services so that it appears as a measure that they can select with my normal measure of Hours back in Excel, and so that they can do a line graph and show how the projects hours are growing.

Thanks for your help

Neil.

|||

Hi Neil. Thanks for the explanation. I provided very query specific solution to your uestion - as if you are using Reporting Services. Your explanation shows that what I gave you isn't very useful - especially in Excel where you don't control the specific MDX. Sorry about that. In ieu of an MDX course you may want to pick up Spoffords book on MDX Solutions (http://www.amazon.com/s/ref=nb_ss_gw/102-1292512-3894526?url=search-alias%3Daps&field-keywords=MDX+spofford&Go.x=12&Go.y=7). This is the SL 2005 edition, bt the MDX basics didn't change from SQL 2000, and if you search the net you can probably find e SQL2000 edition.

Good Luck.

PGoldy

|||

And here's an old OLAP NewsGroup post from George Spofford, explaining how to set up a "time analysis" dimension in As 2000 - which is a precursor to the "Time Intelligence Calculations" in AS 2005. To show running totals from the beginning, you could add a [Time Series].[ATD] calculation, using PeriodsToDate ( [Time].[All], [Time].CurrentMember ) ...

http://groups.google.com/group/microsoft.public.sqlserver.olap/msg/4c59dc7518614323?hl=en&

>>

microsoft.public.sqlserver.olap > Can MDX tell if a member is calculated member? From: George Spofford - view profile Date: Fri, Aug 23 2002 8:19 am Email: George Spofford <geo...@.dsslab.com> Groups: microsoft.public.sqlserver.olap Not yet rated Rating: show options Reply | Reply to Author | Forward | Print | Individual Message | Show original | Report Abuse | Find messages by this author The direct answer is: Sort of. However, your simplest solution is to implement a "time analysis utility dimension", which moves your Current, MTD, QTD, YTD calculations into a separate dimension from time. First, the interesting part. The expression Intersect (Dimension.Members, { [Dimension].[@.X@.] }).Count will return 0 if @.X@. refers to a calc member, 1 otherwise. Second, the possibly better solution: A time analysis utility dimension has no all level and 1 real member in a dimension table named something like "Current" with a key value like 0 or 1. You can create and process the dimension as usual. Bring the dimension into the cube, join it to the fact table on any column of the fact table, and set the member key in the cube the constant 0 or 1 (matching Current's key). Add calculated members to the cube on this dimension: CREATE MEMBER [Time Series].[YTD] AS 'Aggregate ( PeriodsToDate ( [Time].[Year], [Time].CurrentMember ), ([Time Series].[Current]) )' CREATE MEMBER [Time Series].[QTD] AS 'Aggregate ( PeriodsToDate ( [Time].[Quarter], [Time].CurrentMember ), ([Time Series].[Current]) )' A query for YTD sales at August 2002 is a query for the tuple ([Measures].[Sales], [Time Series].[YTD], [Time].[Aug 2002])

>>

|||

Thanks guys,

After all this help, I've been able to get what I'm after, the mdx for running totals is

sum(periodstodate([work date].[(all)]),[measures].[hours])

where [work date] is the date dimension and [hours] are the measure to be cumulated.

Thanks

Neil.

How to show attribute ValueColumn in Excel 2007?

I'm using Excel 2007 to access my SSAS 2005 cube. For an attribute, I have defined the ValueColumn as the alternative description for the attribute. How can I show the ValueColumn in Excel 2007? Better yet, I want to see if there is any way I can toggle between the NameColumn and ValueColumn.

Thanks,

Mitch

The only way I can think of to expose the MemberValue to excel would be to create a calculated measure, but this would mean you would have to have the value in the data section of the pivot table and it sounds like you want to get it into the row/column labels.

Another approach would be to set this "alternate description" up as an attribute in it's own right that is related to the main attribute, you could then display it using the member properties feature in Excel.

Finally, I have not tried this myself, but you could look into maybe using the translations feature to store the alternate description. You might then be able to write a small macro in Excel that would alter the connection to use a different language that would bring up your alternate description. I would suggest doing a small test before going too far down this path.

|||

Darren,

These are not bad suggestions. The closest solution is to use the member property. It is just that my users are used to toggle between one description and another with their current data warehouse tool, and I'm trying to replace the current tool with SSAS and Excel 2007.

Mitch

sql

How to share dimensions for multiple cubes in AS 2005?

Hello,

I have a couple of cubes that I created using a wizard in AS 2005. Every time I create a new cube, a AS wizard adds dimensions with 1,2 etc at the end of their names. Is it possible to use/share existing dimensions which are used by other cubes instead of having bunch of dimensions which are the same and the only difference is their name?

Thanks!

When you are going through cube building wizard, once you get to the Review Shared Dimensions page, select all the dimensions that you want to reuse. The wizard will reuse these dimensions and it won't create the dimensions with the 1,2 etc.

Hope this helps.

Van Dieu

|||Altering what you have already generated may be complicated. I don't use the wizard to generate cubes, so I'm not sure how much work is involved to change it. It might be as simple as opening the cube editor, deleting the dimensions that are duplicates, and adding the ones that you want to use, but I doubt it. You'll probably have to alter the data source view and the dimension usage in the cube editor as well.|||

Actually, it is that easy. You can just open up the cube editor and delete all the duplicated dimensions. Then on the dimension usage tab add them back in (the dimensions that you want to share).

Van Dieu

|||Good to know

How to set up reporting services to have up-to-date cube data whenever report opened

Hi, at work I have set up reporting services with analysis services
providing cube data. I have created a cube and deployed it to reporting
services. What I want to know is how do i guarantee that when an executive
opens up the report, they will get the latest data from the SQL database? I
have installed/created everything with defaults, including the reports and
their deployment via visual studio to reporting services.
Here I am guessing that when a report is opened that reporting services
connects to analysis services (which interacts with sql server whatever way)
to get the latest update of data within the report? I am just a little
concerned because I had a previous report set up and no matter how i tried
to get it to update data, it just couldnt. I had to redeploy the solution
from VS to reporting services.
Any help most appreciated!
cheers
murrayThe default deployment will always get you the latest data... However if you
make a change to the report onthe reporting server, that change will NOT be
overridden by redeploying...
Perhaps you set the cache time on the report in Report Manager... That means
that data will be re-used without re-querying for 60 minutes... Redeploying
the report will NOT change this and many other settings... You would have to
either delete and redeploy or change the setting in Report Manager..
However, for the standard default deploy of a new report, no caching is used..
--
Wayne Snyder MCDBA, SQL Server MVP
Mariner, Charlotte, NC
I support the Professional Association for SQL Server ( PASS) and it''s
community of SQL Professionals.
"j.murray" wrote:
> Hi, at work I have set up reporting services with analysis services
> providing cube data. I have created a cube and deployed it to reporting
> services. What I want to know is how do i guarantee that when an executive
> opens up the report, they will get the latest data from the SQL database? I
> have installed/created everything with defaults, including the reports and
> their deployment via visual studio to reporting services.
> Here I am guessing that when a report is opened that reporting services
> connects to analysis services (which interacts with sql server whatever way)
> to get the latest update of data within the report? I am just a little
> concerned because I had a previous report set up and no matter how i tried
> to get it to update data, it just couldnt. I had to redeploy the solution
> from VS to reporting services.
> Any help most appreciated!
> cheers
> murray
>
>sql

how to set up a cube for pivot table service in Excel?

background: sql2k and analysis service
I know how to create a cube in analysis service, but how to view the
cube in the Pivot Table services in Excel?
when I go to Data|PivotTable and PivotChart report...|External Data
Source|
Get Data | OLAP cube, then browse to the server, but I don't see any of
the cubes being created.
I'm using Standard SQL2k. Is this a sql version issue or should i
create cube differently?
thank youHave you set permissions on the cube to allow access from the user running
Excel?
Under Database Roles in Analysis manager.
Standard version includes analysis services, with EE having increased
features in some areas, so I do not think that is your problem.
Mike John
"=== Steve L ===" <steve.lin@.powells.com> wrote in message
news:1107205552.456050.61110@.f14g2000cwb.googlegroups.com...
> background: sql2k and analysis service
> I know how to create a cube in analysis service, but how to view the
> cube in the Pivot Table services in Excel?
> when I go to Data|PivotTable and PivotChart report...|External Data
> Source|
> Get Data | OLAP cube, then browse to the server, but I don't see any of
> the cubes being created.
> I'm using Standard SQL2k. Is this a sql version issue or should i
> create cube differently?
> thank you
>|||i'm very lost...:(
i created a cube on the analysis service then add my nt accout to the
All Users database role. (enforced on client).
i then go thru the steps in Excell, Data|PivotTable and PivotChart
report...|External Data Source|
Get Data | OLAP cube, but then where shoudl browse to from there?
i check the book online about the local cube. it was not helping. i
still dont' know how to create a local cube.|||Steve, it is difficult to follow your description, but Local cube has
nothing to do with connecting to an analysis services cube.
After you select olap cube you should be able to select thser server and
database that contains the cube.
Mike John
"=== Steve L ===" <steve.lin@.powells.com> wrote in message
news:1107212127.396318.211160@.f14g2000cwb.googlegroups.com...
> i'm very lost...:(
> i created a cube on the analysis service then add my nt accout to the
> All Users database role. (enforced on client).
> i then go thru the steps in Excell, Data|PivotTable and PivotChart
> report...|External Data Source|
> Get Data | OLAP cube, but then where shoudl browse to from there?
> i check the book online about the local cube. it was not helping. i
> still dont' know how to create a local cube.
>|||...After you select olap cube you should be able to select thser server
and
database that contains the cube...
which i did but i couldnt' find any cubes on the sql server running
analysis services thru Excel pivot table services. i can see cubes in
the analsysis services itself, but even when i search the sql server
with analysis services on it, i couldn't find any file with extension
.cub
also, i looked up the help files for excel:
In the PivotTable report, the Offline OLAP command on the PivotTable
menu (PivotTable toolbar (toolbar: A bar with buttons and options that
you use to carry out commands. To display a toolbar, click Customize on
the Tools menu, and then click the Toolbars tab.)) is unavailable if
the provider does not support offline cube files.
in my excel, the Offline OLAP menu is always greyed out. why?|||...After you select olap cube you should be able to select thser server
and
database that contains the cube...
which i did but i couldnt' find any cubes on the sql server running
analysis services thru Excel pivot table services. i can see cubes in
the analsysis services itself, but even when i search the sql server
with analysis services on it, i couldn't find any file with extension
.cub
also, i looked up the help files for excel:
In the PivotTable report, the Offline OLAP command on the PivotTable
menu (PivotTable toolbar (toolbar: A bar with buttons and options that
you use to carry out commands. To display a toolbar, click Customize on
the Tools menu, and then click the Toolbars tab.)) is unavailable if
the provider does not support offline cube files.
in my excel, the Offline OLAP menu is always greyed out. why?|||...After you select olap cube you should be able to select thser server
and
database that contains the cube...
which i did but i couldnt' find any cubes on the sql server running
analysis services thru Excel pivot table services. i can see cubes in
the analsysis services itself, but even when i search the sql server
with analysis services on it, i couldn't find any file with extension
.cub
also, i looked up the help files for excel:
In the PivotTable report, the Offline OLAP command on the PivotTable
menu (PivotTable toolbar (toolbar: A bar with buttons and options that
you use to carry out commands. To display a toolbar, click Customize on
the Tools menu, and then click the Toolbars tab.)) is unavailable if
the provider does not support offline cube files.
in my excel, the Offline OLAP menu is always greyed out. why?|||...After you select olap cube you should be able to select thser server
and
database that contains the cube...
which i did but i couldnt' find any cubes on the sql server running
analysis services thru Excel pivot table services. i can see cubes in
the analsysis services itself, but even when i search the sql server
with analysis services on it, i couldn't find any file with extension
.cub
also, i looked up the help files for excel:
In the PivotTable report, the Offline OLAP command on the PivotTable
menu (PivotTable toolbar (toolbar: A bar with buttons and options that
you use to carry out commands. To display a toolbar, click Customize on
the Tools menu, and then click the Toolbars tab.)) is unavailable if
the provider does not support offline cube files.
in my excel, the Offline OLAP menu is always greyed out. why?|||...After you select olap cube you should be able to select thser server
and
database that contains the cube...
which i did but i couldnt' find any cubes on the sql server running
analysis services thru Excel pivot table services. i can see cubes in
the analsysis services itself, but even when i search the sql server
with analysis services on it, i couldn't find any file with extension
.cub
also, i looked up the help files for excel:
In the PivotTable report, the Offline OLAP command on the PivotTable
menu (PivotTable toolbar (toolbar: A bar with buttons and options that
you use to carry out commands. To display a toolbar, click Customize on
the Tools menu, and then click the Toolbars tab.)) is unavailable if
the provider does not support offline cube files.
in my excel, the Offline OLAP menu is always greyed out. why?

How to set an action?

Hi,
I want to link to a website (like www.google.com) when user click any cell in cube browser.

Then I set an action, the setting detail as following:
Target Type: Cells
Target Object: All Cells
Action Content Type: URL
Action expression: www.google.com

But it's not work fine. When I click any cell in cube browse there is an error message.

Is any problem in my setting detail? especially Action expression?

thanks,You should put double quotes around literal srings in the action expression, so:

"www.google.com"|||Thanks. I add double quotes to it. There is no error message.

But I click the cell, there is no response.
I thought the action would be open a browser and link to google.
Why the action is no any response?|||Try the full URL: "http://www.google.com", because certain safety options can block browsing if the URL doesn't start with "http://" or "https://"

how to set a dimension attribute as a simple "note" field?

I have a simple cube,
dimensions are time and customers (id, name, address, contry, etc), fact are
quantity
On Y axes I have to show the customer name AND the address.
if I drag and drop only the id and name, the query is fast, and it works
fine, If after the name I also add the address, the query becames very slow
I belive this is because of aggregation on the address attribute, that I
really don't need, the address is specific to every customer and it does not
make any sense to do calculation on this.
I only need to show che address near the name, without any aggregation
How can I do it?
Is there any property I have to set?
Thank you
Alessandro Belli
Hello Alessandro,
I understand that you'd like to specify an dimension attribute as a
property not to be aggregated. If I'm off-base, please let me know.
You may want to consider define attribute relationship bewteen attribute
name and address, thus you could display address as member property of name
and do not necessary to drag name attribute to the browser which cause more
calculations and overhead even if they are 1:1 relationship. When you drag
a dimension attribute into the browser it is used as normal attribute
anyway though at running time, it will gain performance improvement when
engine may find it has 1:1 relationship with another attribute.
By default, dimension attribute are related to key dimension attribute by a
attribute relationship. In VS 2005, you could open the dimension in VS
project, and check the attribute relationships defined on the key attribute
( I assume it is "id" in the dimension).
You may want to create a attribute relationship directly on name attribute
and define the relationship bweteen name and address. You will be able to
see address as the member property of the name attribute in browser. You
could right click the a name cell->Show properties in report->Show All
properties in report.
The other option that you could display name/address at the same time ( I
assume it is 1:1 relationship) is that you create a new column on dimension
source table, and merge (name, address) column to a new column such as
name-address. You could use this new column as a attribute directly in the
dimension attribute.
If you have further questions or concerns on the issue, please feel free to
let's know. Than you.
Best Regards,
Peter Yang
MCSE2000/2003, MCSA, MCDBA
Microsoft Online Community Support
==================================================
Get notification to my posts through email? Please refer to
http://msdn.microsoft.com/subscripti...ult.aspx#notif
ications
<http://msdn.microsoft.com/subscripti...s/default.aspx>.
Note: The MSDN Managed Newsgroup support offering is for non-urgent issues
where an initial response from the community or a Microsoft Support
Engineer within 1 business day is acceptable. Please note that each follow
up response may take approximately 2 business days as the support
professional working with you may need further investigation to reach the
most efficient resolution. The offering is not appropriate for situations
that require urgent, real-time or phone-based interactions or complex
project analysis and dump analysis issues. Issues of this nature are best
handled working with a dedicated Microsoft Support Engineer by contacting
Microsoft Customer Support Services (CSS) at
<http://msdn.microsoft.com/subscripti...t/default.aspx>.
==================================================
This posting is provided "AS IS" with no warranties, and confers no rights.
|||Peter Yang [MSFT] wrote:

> Hello Alessandro,
> I understand that you'd like to specify an dimension attribute as a
> property not to be aggregated. If I'm off-base, please let me know.
> You may want to consider define attribute relationship bewteen attribute
> name and address, thus you could display address as member property of name
> and do not necessary to drag name attribute to the browser which cause more
> calculations and overhead even if they are 1:1 relationship. When you drag
> a dimension attribute into the browser it is used as normal attribute
> anyway though at running time, it will gain performance improvement when
> engine may find it has 1:1 relationship with another attribute.
> By default, dimension attribute are related to key dimension attribute by a
> attribute relationship. In VS 2005, you could open the dimension in VS
> project, and check the attribute relationships defined on the key attribute
> ( I assume it is "id" in the dimension).
> You may want to create a attribute relationship directly on name attribute
> and define the relationship bweteen name and address. You will be able to
> see address as the member property of the name attribute in browser. You
> could right click the a name cell->Show properties in report->Show All
> properties in report.
> The other option that you could display name/address at the same time ( I
> assume it is 1:1 relationship) is that you create a new column on dimension
> source table, and merge (name, address) column to a new column such as
> name-address. You could use this new column as a attribute directly in the
> dimension attribute.
> If you have further questions or concerns on the issue, please feel free to
> let's know. Than you.
> Best Regards,
> Peter Yang
> MCSE2000/2003, MCSA, MCDBA
> Microsoft Online Community Support
> ==================================================
> Get notification to my posts through email? Please refer to
> http://msdn.microsoft.com/subscripti...ult.aspx#notif
> ications
> <http://msdn.microsoft.com/subscripti...s/default.aspx>.
> Note: The MSDN Managed Newsgroup support offering is for non-urgent issues
> where an initial response from the community or a Microsoft Support
> Engineer within 1 business day is acceptable. Please note that each follow
> up response may take approximately 2 business days as the support
> professional working with you may need further investigation to reach the
> most efficient resolution. The offering is not appropriate for situations
> that require urgent, real-time or phone-based interactions or complex
> project analysis and dump analysis issues. Issues of this nature are best
> handled working with a dedicated Microsoft Support Engineer by contacting
> Microsoft Customer Support Services (CSS) at
> <http://msdn.microsoft.com/subscripti...t/default.aspx>.
> ==================================================
> This posting is provided "AS IS" with no warranties, and confers no rights.
You can add address as property for name attribute and can create
calculated measure on this property.
Regards
Amish Shah
http://shahamishm.tripod.com
|||Peter Yang [MSFT] wrote:

> Hello Alessandro,
> I understand that you'd like to specify an dimension attribute as a
> property not to be aggregated. If I'm off-base, please let me know.
> You may want to consider define attribute relationship bewteen attribute
> name and address, thus you could display address as member property of name
> and do not necessary to drag name attribute to the browser which cause more
> calculations and overhead even if they are 1:1 relationship. When you drag
> a dimension attribute into the browser it is used as normal attribute
> anyway though at running time, it will gain performance improvement when
> engine may find it has 1:1 relationship with another attribute.
> By default, dimension attribute are related to key dimension attribute by a
> attribute relationship. In VS 2005, you could open the dimension in VS
> project, and check the attribute relationships defined on the key attribute
> ( I assume it is "id" in the dimension).
> You may want to create a attribute relationship directly on name attribute
> and define the relationship bweteen name and address. You will be able to
> see address as the member property of the name attribute in browser. You
> could right click the a name cell->Show properties in report->Show All
> properties in report.
> The other option that you could display name/address at the same time ( I
> assume it is 1:1 relationship) is that you create a new column on dimension
> source table, and merge (name, address) column to a new column such as
> name-address. You could use this new column as a attribute directly in the
> dimension attribute.
> If you have further questions or concerns on the issue, please feel free to
> let's know. Than you.
> Best Regards,
> Peter Yang
> MCSE2000/2003, MCSA, MCDBA
> Microsoft Online Community Support
> ==================================================
> Get notification to my posts through email? Please refer to
> http://msdn.microsoft.com/subscripti...ult.aspx#notif
> ications
> <http://msdn.microsoft.com/subscripti...s/default.aspx>.
> Note: The MSDN Managed Newsgroup support offering is for non-urgent issues
> where an initial response from the community or a Microsoft Support
> Engineer within 1 business day is acceptable. Please note that each follow
> up response may take approximately 2 business days as the support
> professional working with you may need further investigation to reach the
> most efficient resolution. The offering is not appropriate for situations
> that require urgent, real-time or phone-based interactions or complex
> project analysis and dump analysis issues. Issues of this nature are best
> handled working with a dedicated Microsoft Support Engineer by contacting
> Microsoft Customer Support Services (CSS) at
> <http://msdn.microsoft.com/subscripti...t/default.aspx>.
> ==================================================
> This posting is provided "AS IS" with no warranties, and confers no rights.
You can add address as property for name attribute and can create
calculated measure on this property.
Regards
Amish Shah
http://shahamishm.tripod.com
|||Thank you for you suggestion, but is not clear to me how to make a
relationship between name and address.
Is enought to set address.keycolumn= customers.name and
addess.namecolumn=address?
Or what I have to do to make the realtionship?
The key of the dimension is a field named "code". Do I also have to set
name.keycolumn=customers.code?
After this when I browse the cube and I right click the name cell->Show
properties in report->Show All
> properties in report the option is grayed.
Thank's
Alessandro
"Peter Yang [MSFT]" <petery@.online.microsoft.com> ha scritto nel messaggio
news:gdsQ4cR4GHA.2336@.TK2MSFTNGXA01.phx.gbl...
> Hello Alessandro,
> I understand that you'd like to specify an dimension attribute as a
> property not to be aggregated. If I'm off-base, please let me know.
> You may want to consider define attribute relationship bewteen attribute
> name and address, thus you could display address as member property of
> name
> and do not necessary to drag name attribute to the browser which cause
> more
> calculations and overhead even if they are 1:1 relationship. When you drag
> a dimension attribute into the browser it is used as normal attribute
> anyway though at running time, it will gain performance improvement when
> engine may find it has 1:1 relationship with another attribute.
> By default, dimension attribute are related to key dimension attribute by
> a
> attribute relationship. In VS 2005, you could open the dimension in VS
> project, and check the attribute relationships defined on the key
> attribute
> ( I assume it is "id" in the dimension).
> You may want to create a attribute relationship directly on name attribute
> and define the relationship bweteen name and address. You will be able to
> see address as the member property of the name attribute in browser. You
> could right click the a name cell->Show properties in report->Show All
> properties in report.
> The other option that you could display name/address at the same time ( I
> assume it is 1:1 relationship) is that you create a new column on
> dimension
> source table, and merge (name, address) column to a new column such as
> name-address. You could use this new column as a attribute directly in the
> dimension attribute.
> If you have further questions or concerns on the issue, please feel free
> to
> let's know. Than you.
> Best Regards,
> Peter Yang
> MCSE2000/2003, MCSA, MCDBA
> Microsoft Online Community Support
> ==================================================
> Get notification to my posts through email? Please refer to
> http://msdn.microsoft.com/subscripti...ult.aspx#notif
> ications
> <http://msdn.microsoft.com/subscripti...s/default.aspx>.
> Note: The MSDN Managed Newsgroup support offering is for non-urgent issues
> where an initial response from the community or a Microsoft Support
> Engineer within 1 business day is acceptable. Please note that each follow
> up response may take approximately 2 business days as the support
> professional working with you may need further investigation to reach the
> most efficient resolution. The offering is not appropriate for situations
> that require urgent, real-time or phone-based interactions or complex
> project analysis and dump analysis issues. Issues of this nature are best
> handled working with a dedicated Microsoft Support Engineer by contacting
> Microsoft Customer Support Services (CSS) at
> <http://msdn.microsoft.com/subscripti...t/default.aspx>.
> ==================================================
> This posting is provided "AS IS" with no warranties, and confers no
> rights.
>
|||Hello Alessandro,
You could try the following steps to add attribute relationship between
name and address attribute.
1. Open customer dimension by double click the dimension on the right panel
2. Drag Address column from the source table in Data Source View to the
Name Attribute on the left panel, and you shall see a new Attribute
relationship under attribute Name.
3. Process the dimension and cube and you shall see member property Address
under Name Attribute
Please see if this could meet your requirement. If you have any concerns or
questions, please let me know. Thank you.
Best Regards,
Peter Yang
MCSE2000/2003, MCSA, MCDBA
Microsoft Online Partner Support
================================================== ===
This posting is provided "AS IS" with no warranties, and confers no rights.
================================================== ====

how to set a dimension attribute as a simple "note" field?

I have a simple cube,
dimensions are time and customers (id, name, address, contry, etc), fact are
quantity
On Y axes I have to show the customer name AND the address.
if I drag and drop only the id and name, the query is fast, and it works
fine, If after the name I also add the address, the query becames very slow
I belive this is because of aggregation on the address attribute, that I
really don't need, the address is specific to every customer and it does not
make any sense to do calculation on this.
I only need to show che address near the name, without any aggregation
How can I do it?
Is there any property I have to set?
Thank you
Alessandro BelliHello Alessandro,
I understand that you'd like to specify an dimension attribute as a
property not to be aggregated. If I'm off-base, please let me know.
You may want to consider define attribute relationship bewteen attribute
name and address, thus you could display address as member property of name
and do not necessary to drag name attribute to the browser which cause more
calculations and overhead even if they are 1:1 relationship. When you drag
a dimension attribute into the browser it is used as normal attribute
anyway though at running time, it will gain performance improvement when
engine may find it has 1:1 relationship with another attribute.
By default, dimension attribute are related to key dimension attribute by a
attribute relationship. In VS 2005, you could open the dimension in VS
project, and check the attribute relationships defined on the key attribute
( I assume it is "id" in the dimension).
You may want to create a attribute relationship directly on name attribute
and define the relationship bweteen name and address. You will be able to
see address as the member property of the name attribute in browser. You
could right click the a name cell->Show properties in report->Show All
properties in report.
The other option that you could display name/address at the same time ( I
assume it is 1:1 relationship) is that you create a new column on dimension
source table, and merge (name, address) column to a new column such as
name-address. You could use this new column as a attribute directly in the
dimension attribute.
If you have further questions or concerns on the issue, please feel free to
let's know. Than you.
Best Regards,
Peter Yang
MCSE2000/2003, MCSA, MCDBA
Microsoft Online Community Support
========================================
==========
Get notification to my posts through email? Please refer to
http://msdn.microsoft.com/subscript...ault.aspx#notif
ications
<http://msdn.microsoft.com/subscript...ps/default.aspx>.
Note: The MSDN Managed Newsgroup support offering is for non-urgent issues
where an initial response from the community or a Microsoft Support
Engineer within 1 business day is acceptable. Please note that each follow
up response may take approximately 2 business days as the support
professional working with you may need further investigation to reach the
most efficient resolution. The offering is not appropriate for situations
that require urgent, real-time or phone-based interactions or complex
project analysis and dump analysis issues. Issues of this nature are best
handled working with a dedicated Microsoft Support Engineer by contacting
Microsoft Customer Support Services (CSS) at
<http://msdn.microsoft.com/subscript...rt/default.aspx>.
========================================
==========
This posting is provided "AS IS" with no warranties, and confers no rights.|||Peter Yang [MSFT] wrote:
[vbcol=seagreen]
> Hello Alessandro,
> I understand that you'd like to specify an dimension attribute as a
> property not to be aggregated. If I'm off-base, please let me know.
> You may want to consider define attribute relationship bewteen attribute
> name and address, thus you could display address as member property of nam
e
> and do not necessary to drag name attribute to the browser which cause mor
e
> calculations and overhead even if they are 1:1 relationship. When you drag
> a dimension attribute into the browser it is used as normal attribute
> anyway though at running time, it will gain performance improvement when
> engine may find it has 1:1 relationship with another attribute.
> By default, dimension attribute are related to key dimension attribute by
a
> attribute relationship. In VS 2005, you could open the dimension in VS
> project, and check the attribute relationships defined on the key attribut
e
> ( I assume it is "id" in the dimension).
> You may want to create a attribute relationship directly on name attribute
> and define the relationship bweteen name and address. You will be able to
> see address as the member property of the name attribute in browser. You
> could right click the a name cell->Show properties in report->Show All
> properties in report.
> The other option that you could display name/address at the same time ( I
> assume it is 1:1 relationship) is that you create a new column on dimensio
n
> source table, and merge (name, address) column to a new column such as
> name-address. You could use this new column as a attribute directly in the
> dimension attribute.
> If you have further questions or concerns on the issue, please feel free t
o
> let's know. Than you.
> Best Regards,
> Peter Yang
> MCSE2000/2003, MCSA, MCDBA
> Microsoft Online Community Support
> ========================================
==========
> Get notification to my posts through email? Please refer to
> l]
> ications
> <[url]http://msdn.microsoft.com/subscriptions/managednewsgroups/default.aspx" target="_blank">http://msdn.microsoft.com/subscript...ps/default.aspx>.
> Note: The MSDN Managed Newsgroup support offering is for non-urgent issues
> where an initial response from the community or a Microsoft Support
> Engineer within 1 business day is acceptable. Please note that each follow
> up response may take approximately 2 business days as the support
> professional working with you may need further investigation to reach the
> most efficient resolution. The offering is not appropriate for situations
> that require urgent, real-time or phone-based interactions or complex
> project analysis and dump analysis issues. Issues of this nature are best
> handled working with a dedicated Microsoft Support Engineer by contacting
> Microsoft Customer Support Services (CSS) at
> <http://msdn.microsoft.com/subscript...rt/default.aspx>.
> ========================================
==========
> This posting is provided "AS IS" with no warranties, and confers no rights.[/vbcol
]
You can add address as property for name attribute and can create
calculated measure on this property.
Regards
Amish Shah
http://shahamishm.tripod.com|||Peter Yang [MSFT] wrote:
[vbcol=seagreen]
> Hello Alessandro,
> I understand that you'd like to specify an dimension attribute as a
> property not to be aggregated. If I'm off-base, please let me know.
> You may want to consider define attribute relationship bewteen attribute
> name and address, thus you could display address as member property of nam
e
> and do not necessary to drag name attribute to the browser which cause mor
e
> calculations and overhead even if they are 1:1 relationship. When you drag
> a dimension attribute into the browser it is used as normal attribute
> anyway though at running time, it will gain performance improvement when
> engine may find it has 1:1 relationship with another attribute.
> By default, dimension attribute are related to key dimension attribute by
a
> attribute relationship. In VS 2005, you could open the dimension in VS
> project, and check the attribute relationships defined on the key attribut
e
> ( I assume it is "id" in the dimension).
> You may want to create a attribute relationship directly on name attribute
> and define the relationship bweteen name and address. You will be able to
> see address as the member property of the name attribute in browser. You
> could right click the a name cell->Show properties in report->Show All
> properties in report.
> The other option that you could display name/address at the same time ( I
> assume it is 1:1 relationship) is that you create a new column on dimensio
n
> source table, and merge (name, address) column to a new column such as
> name-address. You could use this new column as a attribute directly in the
> dimension attribute.
> If you have further questions or concerns on the issue, please feel free t
o
> let's know. Than you.
> Best Regards,
> Peter Yang
> MCSE2000/2003, MCSA, MCDBA
> Microsoft Online Community Support
> ========================================
==========
> Get notification to my posts through email? Please refer to
> l]
> ications
> <[url]http://msdn.microsoft.com/subscriptions/managednewsgroups/default.aspx" target="_blank">http://msdn.microsoft.com/subscript...ps/default.aspx>.
> Note: The MSDN Managed Newsgroup support offering is for non-urgent issues
> where an initial response from the community or a Microsoft Support
> Engineer within 1 business day is acceptable. Please note that each follow
> up response may take approximately 2 business days as the support
> professional working with you may need further investigation to reach the
> most efficient resolution. The offering is not appropriate for situations
> that require urgent, real-time or phone-based interactions or complex
> project analysis and dump analysis issues. Issues of this nature are best
> handled working with a dedicated Microsoft Support Engineer by contacting
> Microsoft Customer Support Services (CSS) at
> <http://msdn.microsoft.com/subscript...rt/default.aspx>.
> ========================================
==========
> This posting is provided "AS IS" with no warranties, and confers no rights.[/vbcol
]
You can add address as property for name attribute and can create
calculated measure on this property.
Regards
Amish Shah
http://shahamishm.tripod.com|||Thank you for you suggestion, but is not clear to me how to make a
relationship between name and address.
Is enought to set address.keycolumn= customers.name and
addess.namecolumn=address?
Or what I have to do to make the realtionship?
The key of the dimension is a field named "code". Do I also have to set
name.keycolumn=customers.code?
After this when I browse the cube and I right click the name cell->Show
properties in report->Show All
> properties in report the option is grayed.
Thank's
Alessandro
"Peter Yang [MSFT]" <petery@.online.microsoft.com> ha scritto nel messagg
io
news:gdsQ4cR4GHA.2336@.TK2MSFTNGXA01.phx.gbl...
> Hello Alessandro,
> I understand that you'd like to specify an dimension attribute as a
> property not to be aggregated. If I'm off-base, please let me know.
> You may want to consider define attribute relationship bewteen attribute
> name and address, thus you could display address as member property of
> name
> and do not necessary to drag name attribute to the browser which cause
> more
> calculations and overhead even if they are 1:1 relationship. When you drag
> a dimension attribute into the browser it is used as normal attribute
> anyway though at running time, it will gain performance improvement when
> engine may find it has 1:1 relationship with another attribute.
> By default, dimension attribute are related to key dimension attribute by
> a
> attribute relationship. In VS 2005, you could open the dimension in VS
> project, and check the attribute relationships defined on the key
> attribute
> ( I assume it is "id" in the dimension).
> You may want to create a attribute relationship directly on name attribute
> and define the relationship bweteen name and address. You will be able to
> see address as the member property of the name attribute in browser. You
> could right click the a name cell->Show properties in report->Show All
> properties in report.
> The other option that you could display name/address at the same time ( I
> assume it is 1:1 relationship) is that you create a new column on
> dimension
> source table, and merge (name, address) column to a new column such as
> name-address. You could use this new column as a attribute directly in the
> dimension attribute.
> If you have further questions or concerns on the issue, please feel free
> to
> let's know. Than you.
> Best Regards,
> Peter Yang
> MCSE2000/2003, MCSA, MCDBA
> Microsoft Online Community Support
> ========================================
==========
> Get notification to my posts through email? Please refer to
> l]
> ications
> <[url]http://msdn.microsoft.com/subscriptions/managednewsgroups/default.aspx" target="_blank">http://msdn.microsoft.com/subscript...ps/default.aspx>.
> Note: The MSDN Managed Newsgroup support offering is for non-urgent issues
> where an initial response from the community or a Microsoft Support
> Engineer within 1 business day is acceptable. Please note that each follow
> up response may take approximately 2 business days as the support
> professional working with you may need further investigation to reach the
> most efficient resolution. The offering is not appropriate for situations
> that require urgent, real-time or phone-based interactions or complex
> project analysis and dump analysis issues. Issues of this nature are best
> handled working with a dedicated Microsoft Support Engineer by contacting
> Microsoft Customer Support Services (CSS) at
> <http://msdn.microsoft.com/subscript...rt/default.aspx>.
> ========================================
==========
> This posting is provided "AS IS" with no warranties, and confers no
> rights.
>|||Hello Alessandro,
You could try the following steps to add attribute relationship between
name and address attribute.
1. Open customer dimension by double click the dimension on the right panel
2. Drag Address column from the source table in Data Source View to the
Name Attribute on the left panel, and you shall see a new Attribute
relationship under attribute Name.
3. Process the dimension and cube and you shall see member property Address
under Name Attribute
Please see if this could meet your requirement. If you have any concerns or
questions, please let me know. Thank you.
Best Regards,
Peter Yang
MCSE2000/2003, MCSA, MCDBA
Microsoft Online Partner Support
========================================
=============
This posting is provided "AS IS" with no warranties, and confers no rights.
========================================
==============