Currency Conversions in ssas

Anyone got at link (other than books online) that describes how to implement currency conversions in ssas?

Here is some information for you

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

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

Edward.
--
This posting is provided "AS IS" with no warranties, and confers no rights.

Currency Conversion SSAS 2005

I would like to have calculated members in a currency dimension that will apply an exchange rate to all applicable measures based on what the user selects. All measures in the fact table are stored in one currency (USD) but need to be reported in a variety of currencies.

In AS 2000 this was accomplished via a lookup cube. I have read the documentation on books on line regarding currency conversion with the Business Intelligence Wizard in SSAS 2005 and this seems overly complicated and will not work based on our needs (since our data is stored in one currency and only the most recent exchange rate is important, no date dimension is necessary).

I have a fact table with with currency code and converstion rate. Since all of our measures are stored in USD, I do not have a currency key in the other fact tables. Is what I am trying to do possible without adding the currency key to all the fact tables?

Any suggestions are greatly appreciated.

Even in AS2000 with your setup, LookupCube wasn't the best solution. The best solution would've been to create virtual cube, and then multiply the USD measure by ValidMeasure(Rate). The same approach should work in AS2005 as well, only instead of virtual cube, you would have two measure groups in your cube.|||

Thank you for the reponse.

Can you expand upon what you mean by using the ValidMeasure function?

I have a measure group with the Converstion Rate. The only dimension related to this measure group is the currency dimension. I would like to be able to have one calculated measure per currency code that multiplies all related measures stored in USD by the corresponding conversion rate.

So that leaves me with two issues:

1) How to apply the conversion rate to the measures based on what currency is selected in the currency dimension.

2) How to filter the solution to only apply to financial measures (the count measures should not adjust)

|||

Here is an example of how MDX Script could look like to achieve both of your issues. This assumes that Currency attribte is non-aggregatable, as it makes no sense to aggregate across different currencies.

({Measures.FinancialMeasure1, Measures.FinancialMeasure2, Measures.FinancialMeasure3}, Currency.Currency.Currency.MEMBERS) = Measures.CurrentMember*ValidMeasure(Measures.ConversionRate);

(more details can be found here: http://www.sqljunkies.com/WebLog/mosha/archive/2005/12/06/multiplication_perf.aspx)

|||Works perfectly! Thanks! |||

It seems that this solution has caused another issue. The measures that this logic was applied to are not rolling up correctly. When I filter a dimension on the rows other than at the all level, the totals do not reflect the current selection of data.

For example, if I want to display the units and amount by product for all products, my result set might look something like this:

Product Units Amount

A 1 10

B 1 10

C 1 10

Total 3 30

If I want to look at only Product A, my result looks like this:

Product Units Amount

A 1 10

Total 1 30

The Total for the Units measure is correct because it's value does not change based on the currency that the user has selected, however, the Total for the Amount measure is incorrect and does not take into account what dimension filters are being applied.

Do you have any suggestions?

|||You can apply the currency conversion only at Leaves() - then they will be rolled up to the higher levels, and then visual totals should work correctly. However, be aware that the performance might become worse. Yet another approach would be to use measure expressions to do currency conversion during processing. This solution is not as flexible, but I think it will fit your scenario too.|||

I cannot use ValidMeasure with the Measure Expressions thus the conversion is inaccurrate. Can you expand on the Leaves() suggestion?

|||

I was able to use Measure Expressions to solve this problem after adding a date key to the currency conversion fact table. So now the currency conversion fact table looks like this:

Currency Key

Time Key

Conversion Rate

I am able to link the currency fact table to the other fact tables via the Time dimension and perform the currency conversion calculations using Measure Expressions based on what Currency is selected in Currency Dimension. Not as dynamic as placing this logic in an MDX Script but it works and the performance is great! Thanks Mosha!

Currency Conversion SSAS 2005

I would like to have calculated members in a currency dimension that will apply an exchange rate to all applicable measures based on what the user selects. All measures in the fact table are stored in one currency (USD) but need to be reported in a variety of currencies.

In AS 2000 this was accomplished via a lookup cube. I have read the documentation on books on line regarding currency conversion with the Business Intelligence Wizard in SSAS 2005 and this seems overly complicated and will not work based on our needs (since our data is stored in one currency and only the most recent exchange rate is important, no date dimension is necessary).

I have a fact table with with currency code and converstion rate. Since all of our measures are stored in USD, I do not have a currency key in the other fact tables. Is what I am trying to do possible without adding the currency key to all the fact tables?

Any suggestions are greatly appreciated.

Even in AS2000 with your setup, LookupCube wasn't the best solution. The best solution would've been to create virtual cube, and then multiply the USD measure by ValidMeasure(Rate). The same approach should work in AS2005 as well, only instead of virtual cube, you would have two measure groups in your cube.|||

Thank you for the reponse.

Can you expand upon what you mean by using the ValidMeasure function?

I have a measure group with the Converstion Rate. The only dimension related to this measure group is the currency dimension. I would like to be able to have one calculated measure per currency code that multiplies all related measures stored in USD by the corresponding conversion rate.

So that leaves me with two issues:

1) How to apply the conversion rate to the measures based on what currency is selected in the currency dimension.

2) How to filter the solution to only apply to financial measures (the count measures should not adjust)

|||

Here is an example of how MDX Script could look like to achieve both of your issues. This assumes that Currency attribte is non-aggregatable, as it makes no sense to aggregate across different currencies.

({Measures.FinancialMeasure1, Measures.FinancialMeasure2, Measures.FinancialMeasure3}, Currency.Currency.Currency.MEMBERS) = Measures.CurrentMember*ValidMeasure(Measures.ConversionRate);

(more details can be found here: http://www.sqljunkies.com/WebLog/mosha/archive/2005/12/06/multiplication_perf.aspx)

|||Works perfectly! Thanks! |||

It seems that this solution has caused another issue. The measures that this logic was applied to are not rolling up correctly. When I filter a dimension on the rows other than at the all level, the totals do not reflect the current selection of data.

For example, if I want to display the units and amount by product for all products, my result set might look something like this:

Product Units Amount

A 1 10

B 1 10

C 1 10

Total 3 30

If I want to look at only Product A, my result looks like this:

Product Units Amount

A 1 10

Total 1 30

The Total for the Units measure is correct because it's value does not change based on the currency that the user has selected, however, the Total for the Amount measure is incorrect and does not take into account what dimension filters are being applied.

Do you have any suggestions?

|||You can apply the currency conversion only at Leaves() - then they will be rolled up to the higher levels, and then visual totals should work correctly. However, be aware that the performance might become worse. Yet another approach would be to use measure expressions to do currency conversion during processing. This solution is not as flexible, but I think it will fit your scenario too.|||

I cannot use ValidMeasure with the Measure Expressions thus the conversion is inaccurrate. Can you expand on the Leaves() suggestion?

|||

I was able to use Measure Expressions to solve this problem after adding a date key to the currency conversion fact table. So now the currency conversion fact table looks like this:

Currency Key

Time Key

Conversion Rate

I am able to link the currency fact table to the other fact tables via the Time dimension and perform the currency conversion calculations using Measure Expressions based on what Currency is selected in Currency Dimension. Not as dynamic as placing this logic in an MDX Script but it works and the performance is great! Thanks Mosha!

Currency Conversion SSAS 2005

I would like to have calculated members in a currency dimension that will apply an exchange rate to all applicable measures based on what the user selects. All measures in the fact table are stored in one currency (USD) but need to be reported in a variety of currencies.

In AS 2000 this was accomplished via a lookup cube. I have read the documentation on books on line regarding currency conversion with the Business Intelligence Wizard in SSAS 2005 and this seems overly complicated and will not work based on our needs (since our data is stored in one currency and only the most recent exchange rate is important, no date dimension is necessary).

I have a fact table with with currency code and converstion rate. Since all of our measures are stored in USD, I do not have a currency key in the other fact tables. Is what I am trying to do possible without adding the currency key to all the fact tables?

Any suggestions are greatly appreciated.

Even in AS2000 with your setup, LookupCube wasn't the best solution. The best solution would've been to create virtual cube, and then multiply the USD measure by ValidMeasure(Rate). The same approach should work in AS2005 as well, only instead of virtual cube, you would have two measure groups in your cube.|||

Thank you for the reponse.

Can you expand upon what you mean by using the ValidMeasure function?

I have a measure group with the Converstion Rate. The only dimension related to this measure group is the currency dimension. I would like to be able to have one calculated measure per currency code that multiplies all related measures stored in USD by the corresponding conversion rate.

So that leaves me with two issues:

1) How to apply the conversion rate to the measures based on what currency is selected in the currency dimension.

2) How to filter the solution to only apply to financial measures (the count measures should not adjust)

|||

Here is an example of how MDX Script could look like to achieve both of your issues. This assumes that Currency attribte is non-aggregatable, as it makes no sense to aggregate across different currencies.

({Measures.FinancialMeasure1, Measures.FinancialMeasure2, Measures.FinancialMeasure3}, Currency.Currency.Currency.MEMBERS) = Measures.CurrentMember*ValidMeasure(Measures.ConversionRate);

(more details can be found here: http://www.sqljunkies.com/WebLog/mosha/archive/2005/12/06/multiplication_perf.aspx)

|||Works perfectly! Thanks! |||

It seems that this solution has caused another issue. The measures that this logic was applied to are not rolling up correctly. When I filter a dimension on the rows other than at the all level, the totals do not reflect the current selection of data.

For example, if I want to display the units and amount by product for all products, my result set might look something like this:

Product Units Amount

A 1 10

B 1 10

C 1 10

Total 3 30

If I want to look at only Product A, my result looks like this:

Product Units Amount

A 1 10

Total 1 30

The Total for the Units measure is correct because it's value does not change based on the currency that the user has selected, however, the Total for the Amount measure is incorrect and does not take into account what dimension filters are being applied.

Do you have any suggestions?

|||You can apply the currency conversion only at Leaves() - then they will be rolled up to the higher levels, and then visual totals should work correctly. However, be aware that the performance might become worse. Yet another approach would be to use measure expressions to do currency conversion during processing. This solution is not as flexible, but I think it will fit your scenario too.|||

I cannot use ValidMeasure with the Measure Expressions thus the conversion is inaccurrate. Can you expand on the Leaves() suggestion?

|||

I was able to use Measure Expressions to solve this problem after adding a date key to the currency conversion fact table. So now the currency conversion fact table looks like this:

Currency Key

Time Key

Conversion Rate

I am able to link the currency fact table to the other fact tables via the Time dimension and perform the currency conversion calculations using Measure Expressions based on what Currency is selected in Currency Dimension. Not as dynamic as placing this logic in an MDX Script but it works and the performance is great! Thanks Mosha!

Cubes Migration problem

Hello,
I have 3 cubes with 3 shared dimensions (dates, ids, factors) in SQL Server 2000 environment. After the cubes migration to SSAS 2005
using Analysis Services Migration Wizard I got individual dimensions for
each cube : dates, dates1, dates2, ids,ids1, ids2, factors, factors1,
factors2. This makes the problem for my cubes MDX queries and based on it applications.
The question is how to preserve shared dimensions during cubes migration? And another qustion: is it possible to have in the database dimensions with the same name

(like dates and dates)?

Thanks,
GB

Migration is not going to create a best possible stucture for your cube. It operates in generic manner trying to cover all possible cube designs.

After you've migrated you need to go over your cube design and improve it. You should try to re-structure your cubes so you have a single version of database dimension ( you probably dont neet dates1, dates2, ids1, ids2 ...).

You should also take a look if possible to create a single cube with several measure groups. Looks like this might be the case. If you have multiple dimensions shared between several cubes, chances you can model the situation by creating multiple measure groups.

Take a look at sample Adventure Works project, see the stucture of Adventure Works cube. This should give you clues for having better cube design.

Edward.
--
This posting is provided "AS IS" with no warranties, and confers no rights.

Cubes Migration problem

Hello,
I have 3 cubes with 3 shared dimensions (dates, ids, factors) in SQL Server 2000 environment. After the cubes migration to SSAS 2005
using Analysis Services Migration Wizard I got individual dimensions for
each cube : dates, dates1, dates2, ids,ids1, ids2, factors, factors1,
factors2. This makes the problem for my cubes MDX queries and based on it applications.
The question is how to preserve shared dimensions during cubes migration? And another qustion: is it possible to have in the database dimensions with the same name

(like dates and dates)?

Thanks,
GB

Migration is not going to create a best possible stucture for your cube. It operates in generic manner trying to cover all possible cube designs.

After you've migrated you need to go over your cube design and improve it. You should try to re-structure your cubes so you have a single version of database dimension ( you probably dont neet dates1, dates2, ids1, ids2 ...).

You should also take a look if possible to create a single cube with several measure groups. Looks like this might be the case. If you have multiple dimensions shared between several cubes, chances you can model the situation by creating multiple measure groups.

Take a look at sample Adventure Works project, see the stucture of Adventure Works cube. This should give you clues for having better cube design.

Edward.
--
This posting is provided "AS IS" with no warranties, and confers no rights.

Cube won't process in SSIS, but will process thru Mgmt Studio?

Hello,

SSAS newbie here. I have an AS database called "AS_Sales" with numerous cubes, including one called "CP Sales". It has one measure group (also called "CP Sales") that is partitioned by fiscal quarter.

I can right-click the cube in management studio and hit "Process" and it indicates that everything completes successfully. However, I set up an Analysis Services task in SSIS to do the same thing, and I receive errors.

SSIS package consists of two AS tasks -- Refresh Dimensions & Reprocess Cube. The task fails during the dimension reprocessing, with the following error messages:

Information: 0x40016041 at AS_Sales_Cubes_Refresh: The package is attempting to configure from the XML file "E:\SSIS\Config\AS_Sales_Cubes_Refresh.dtsConfig".

SSIS package "AS_Sales_Cubes_Refresh.dtsx" starting.

Error: 0xC11F000E at Rebuild Dimension Structure, Analysis Services Execute DDL Task: Errors in the OLAP storage engine: An error occurred while processing the 'FY2001_QTR4' partition of the 'CP Sales' measure group for the 'CP Sales' cube from the AS_Sales database.

Error: 0xC11F000E at Rebuild Dimension Structure, Analysis Services Execute DDL Task: Errors in the OLAP storage engine: An error occurred while processing the 'FY2003_QTR3' partition of the 'CP Sales' measure group for the 'CP Sales' cube from the AS_Sales database.

Error: 0xC11F000E at Rebuild Dimension Structure, Analysis Services Execute DDL Task: Errors in the OLAP storage engine: An error occurred while processing the 'FY2005_QTR2' partition of the 'CP Sales' measure group for the 'CP Sales' cube from the AS_Sales database.

Error: 0xC11F000E at Rebuild Dimension Structure, Analysis Services Execute DDL Task: Errors in the OLAP storage engine: An error occurred while processing the 'FY2007_QTR3' partition of the 'CP Sales' measure group for the 'CP Sales' cube from the AS_Sales database.

Error: 0xC1060000 at Rebuild Dimension Structure, Analysis Services Execute DDL Task: OLE DB error: OLE DB or ODBC error: Operation canceled; HY008.

Error: 0xC11F000E at Rebuild Dimension Structure, Analysis Services Execute DDL Task: Errors in the OLAP storage engine: An error occurred while processing the 'FY2005_QTR4' partition of the 'CP Sales' measure group for the 'CP Sales' cube from the AS_Sales database.

Error: 0xC1060000 at Rebuild Dimension Structure, Analysis Services Execute DDL Task: OLE DB error: OLE DB or ODBC error: Operation canceled; HY008.

Error: 0xC11F000E at Rebuild Dimension Structure, Analysis Services Execute DDL Task: Errors in the OLAP storage engine: An error occurred while processing the 'FY2006_QTR1' partition of the 'CP Sales' measure group for the 'CP Sales' cube from the AS_Sales database.

Task failed: Rebuild Dimension Structure

SSIS package "AS_Sales_Cubes_Refresh.dtsx" finished: Failure.

It only seems to be failing for a handful of the partitions, which makes the problem all that more confusing. Does anyone have any ideas about what I'm doing wrong?

Thanks in advance,

Jamie

if you have defined your attribute relationships as rigid you cannot use refresh for your dimensions. Try full process of each dimension and see what happens. Place the processing(full) of the dimensions before processing the cubes/measure groups.

HTH

Thomas Ivarsson

Cube Wizard not detecting a Fact Table in SSAS

Hi,

While trying to create a Cube using the Cube Wizard, Under 'Identify Fact and Dimension Tables' screen, When I try to check either 'Fact' or 'Dimension' option box for a FactTable, SS is flagging an error.

Can someone tell me as to why this is happening ?

The wizard relies on the relationships in the DSV, so if these are missing or incorrect the wizard may have problems figuring things out.|||A little more specificially, every Fact Table must have one or more Dimension Tables which can be reached from it based on the relationships in the DSV. You probably just need to select a table for use as a dimension, possibly even the fact table itself, and then the wizard should let you continue.|||

Thanks Darren and Matt for your replies.

I modified the Fact table to have FKs with Dimension Tables and now the Wizard detects it successfully.

Cheers,

Sundar

Cube Wizard not detecting a Fact Table in SSAS

Hi,

While trying to create a Cube using the Cube Wizard, Under 'Identify Fact and Dimension Tables' screen, When I try to check either 'Fact' or 'Dimension' option box for a FactTable, SS is flagging an error.

Can someone tell me as to why this is happening ?

The wizard relies on the relationships in the DSV, so if these are missing or incorrect the wizard may have problems figuring things out.|||A little more specificially, every Fact Table must have one or more Dimension Tables which can be reached from it based on the relationships in the DSV. You probably just need to select a table for use as a dimension, possibly even the fact table itself, and then the wizard should let you continue.|||

Thanks Darren and Matt for your replies.

I modified the Fact table to have FKs with Dimension Tables and now the Wizard detects it successfully.

Cheers,

Sundar

Cube Wizard not detecting a Fact Table in SSAS

Hi,

While trying to create a Cube using the Cube Wizard, Under 'Identify Fact and Dimension Tables' screen, When I try to check either 'Fact' or 'Dimension' option box for a FactTable, SS is flagging an error.

Can someone tell me as to why this is happening ?

The wizard relies on the relationships in the DSV, so if these are missing or incorrect the wizard may have problems figuring things out.|||A little more specificially, every Fact Table must have one or more Dimension Tables which can be reached from it based on the relationships in the DSV. You probably just need to select a table for use as a dimension, possibly even the fact table itself, and then the wizard should let you continue.|||

Thanks Darren and Matt for your replies.

I modified the Fact table to have FKs with Dimension Tables and now the Wizard detects it successfully.

Cheers,

Sundar

Cube Wizard not detecting a Fact Table in SSAS

Hi,

While trying to create a Cube using the Cube Wizard, Under 'Identify Fact and Dimension Tables' screen, When I try to check either 'Fact' or 'Dimension' option box for a FactTable, SS is flagging an error.

Can someone tell me as to why this is happening ?

The wizard relies on the relationships in the DSV, so if these are missing or incorrect the wizard may have problems figuring things out.|||A little more specificially, every Fact Table must have one or more Dimension Tables which can be reached from it based on the relationships in the DSV. You probably just need to select a table for use as a dimension, possibly even the fact table itself, and then the wizard should let you continue.|||

Thanks Darren and Matt for your replies.

I modified the Fact table to have FKs with Dimension Tables and now the Wizard detects it successfully.

Cheers,

Sundar

cube limit

Hi all,

I am new to SSAS with SQL2005SP2. Currently, I have a fact table with almost 10 million records. In this table, I have at least 27 fields which store monthly data for the past 27 months along with some other fields. At first, I plan to make those 27 fields to one field and add another field like period to capture the year and month information so I can link to my time dimension table. Of course, this makes the new table with 270 million record counts. It took forever to process the cube. If I add more dimenstions into this cube, I got an error msg saying it exceed the limits. I am trying to find out the limit but did not get anywhere. Does anyone know the max dimensions is allowed in a cube or the max intersection can be created for a cube?

wenchi

did you patrition your cube?

what exactly error message you have got?

how many dimensions (attribute hierarchies) has the cube?

|||

I think the theoretical limit is 2 billion attributes in a cube. If you could answer Vladimir's questions we might be able to help identify your issue.

cube limit

Hi all,

I am new to SSAS with SQL2005SP2. Currently, I have a fact table with almost 10 million records. In this table, I have at least 27 fields which store monthly data for the past 27 months along with some other fields. At first, I plan to make those 27 fields to one field and add another field like period to capture the year and month information so I can link to my time dimension table. Of course, this makes the new table with 270 million record counts. It took forever to process the cube. If I add more dimenstions into this cube, I got an error msg saying it exceed the limits. I am trying to find out the limit but did not get anywhere. Does anyone know the max dimensions is allowed in a cube or the max intersection can be created for a cube?

wenchi

did you patrition your cube?

what exactly error message you have got?

how many dimensions (attribute hierarchies) has the cube?

|||

I think the theoretical limit is 2 billion attributes in a cube. If you could answer Vladimir's questions we might be able to help identify your issue.

Cube deployment issue SSAS 2005

Hello all,

I got an issue with deploying my cube. I am new to SSAS 2005, and I cannot find the option to define which account to use with deploying. I managed to choose the server for deployment, but strangely enough, no user can be selected, so when it deploys, it throws an error stating the standard windows login isn't valid (wich is correct, but I do not want to use standard windows login).And why do I have to use a local windows user account? Why can't the account from my server?|||The only thing I can find wich is remotely connected with this problem is the following:
http://msdn2.microsoft.com/en-us/library/ms166576.aspx

But nothing about a login or something like that.
Please help, I am totally clueless.|||From your description it is not clear to me what exactly you are doing and what is happening. Can you please decsribe it in more detail, and also provide the exact error message you get. Thanks.|||

I made a database + tables, I defined it a data source in Visual Studio/Business Intelligence/Analysis Services, made a view, and then I defined a simple cube. In AS 2000, I only needed to process the cube. But in 2005 I also need to deploy it. Then it gives an error that it doesn't reckognise the user (wich is a local windows user). Of course it needs to be a SQL Server user (I made an account in SQL Server 2005). I cannot find anywhere where to define the user for deploying the cube.

Thanks in advance.

|||

SSAS does not use SQL Server accounts for authentication. It only uses Windows accounts. This is why there is not where to specify a particular user as it always uses the current windows account. When you are setup using a domain this is not an issue as everything can be specified in terms of domain accounts.

In your situation it sounds like you have local accounts on your workstation and on the server. In this case what you have to do is to set up an identical account (same username and password) on both your workstation and the server. Then when you connect to the server you will effectively be "mapped" onto the local account on the server. So you will need to make sure the account on the server has the appropriate priviledges to deploy databases.

|||That sounds like a workaround.

I rebuilt the cube under the new account, and now I got exactly the same failure as the following:
http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=239828&SiteID=1

(and yes, I've altered the 'localhost' to the appropriate server running sql server 2005)|||

It could be seen as a work around, but it is the only way I know of to authenticate when you are not using a AD domain.

It sounds like you are having connectivity issues, possibly caused by a firewall or something. There is a great article on diagnosing connection issues here http://www.sqljunkies.com/WebLog/edwardm/archive/2006/05/26/21447.aspx

I am suspicious that there may still be an underlying connection issue here. If you have the sample Adventure Works database you could use that to test that you can connect and query it. If you don't want to install the sample, would it be possible to copy the project files to the server and try to deploy the project locally on the server? If you can get this to work it would eliminate actual deployment issues and identify if we are dealing with a network connection issue.

|||I have watched the firewall, and the port configuration seems to be okay. The connection is closed abruptly. (FYI, I use ISA2004, and port 2382 is open, 2383 I do not get opened yet, but it isn't accessed either)
The error message is:
Error 1 The project could not be deployed to the '<server>' server because of the following connectivity problems : A connection cannot be made. Ensure that the server is running. To verify or update the name of the target server, right-click on the project in Solution Explorer, select Project Properties, click on the Deployment tab, and then enter the name of the server. 0 0
(<server> is either localhost, or the server running SQL Server 2005 + AS2005)
The MDX application wich came with AS2000 connects fine to AS2005.
Unfortunately, to set it up via http is not an option in my case.
Could anyone give me a link to where I can find out how and where AS2005 stores cubes etc?
My gratitude is yours.

Regards,

Eyso|||

The fact that you are seeing a connection on port 2382 suggests to me that you might be using a named instance (eg. <server>\<instance>). If this is the case, it will not use port 2383 and unless you have set a specific port for that instance you will not necessarily know which port is being used. With a named instance the client connects to the SQL Browser service on port 2382 to ask it which port the particular instance is listening on and the tries to use that port. If this is the case it sounds like you might want to set a specific port for the instance to use so that you can open up that port in your firewall, by default a named instance will just search for a free port number each time it starts up if one is not specifically set.

> The MDX application which came with AS2000 connects fine to AS2005.

This is unusual if you are getting "A connection cannot be made" errors - as they use the same sort of connection, its just the commands that are sent over the connection that vary. Are you using the same server name in the MDX Sample as in the deployment options in BIDS? I would expect a different error from this, but are you sure your user has the rights to deploy a database?

> Could anyone give me a link to where I can find out how and where AS2005 stores cubes etc?

The "where" is easy, there is a data directory set on the server and if needed you can override this on a partition basis. As to the "how" they are stored in a proprietary format I don't believe there is any public information on this (any you really should not need to know). All your access to SSAS should go through one of the documented API's - XMLA, AMO, ADOMD, ADOMD.NET etc.

|||Well, I made an account in SQL Server 2005 with the same name (as windows login) to my network account, and I open BIDS with my normal account, then if I want to deploy it, I get the following error message:
Error 1 Either the '<usergroup\username>' user does not have permission to create a new object in '<server>', or the object does not exist. 0 0
While it does exists, and I gave it every permission possible.
(as suggested in this thread: https://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=398382&SiteID=1).|||Great.
Now I try to add securables to that account, run it as a script. Server Management says script has executed succesfully, but when I click again on the account, nothing has changed.
If I try to deploy the cube, nothing is being written in the logfile of SQL Server.|||Hm, if I try to add the server to 'servers' (BIDS -> Tools -> Add server), it doesn't seem to reckognise SQL Server 2005. Though it does reckognise SQL Server 2000, wich runs on the same server.|||

Eyso Zanstra wrote:

Well, I made an account in SQL Server 2005 with the same name (as windows login) to my network account

No - SQL Server accounts have nothing to do with Analysis Services. You can uninstall SQL Server completely and still run Analysis Services.

If you are logging into a windows domain you must setup that network account with administrative rights in order to be able to deploy a new database. You got me chasing down the wrong path when you started talking about using a local windows account earlier in the thread, and I assumed that you must have been running in a workgroup or using a novel network. if you are logged in to a windows domain you cannot map to a local account on the server - you can only assign the rights to the network account.

|||Thanks, that answers my question about how these cubes are being stored. :-)
Is it suffice when I get those rights only on that particular directory where the cube is stored?

Cube data storage location

Hi -

What level of storage locations we can define in SSAS database e.g. Can we define a separate location for these SSAS ojects.

Instance

Database,

dimensions

Measure Gruop

Measure

Partitons

We do want to store cube database in root data folder.Can some one gide me on this, how to do this. Appreciate your help.

You can set a per instance data folder (indeed each instance will have it's own install folder and the data folder is under that by default). But below that, although you can set the storeage location for cubes and measure groups, these settings pretty much just serve as the default for any new partitions that are created. It is really only at the partition level that you have granular control over where the data is stored. Even then this is really only worth changing if you want to spread your data across mulitple disks. If you are not doing this I would recommend that you just stay with the default folder structure.

I believe that the metadata for databases cubes and measure groups (which are quite small) are all stored in the data folder for the instance along with the dimension data and metadata.

On my laptop I have configured the default folder for my instance to a folder outside of "program files", but I have left everything else at the default settings.

cube cretion & report accessing it

Hi,

I’m new with SSAS but I had know how to create a cube and I could able to connect to SSAS database and view the cube I had created. But I do not understand if some Reporting tool wanted to access the data from cube, will it connect cube and do the manipulation as per the report requirement or I have to do create a cube specific to the each report.

Another clarification if I create a cube on show flick schema will that be any problem in accessing the data.

This are the very basic questions but I need to get more clarity

Thank you

Regarding cubes & reports, you should be able to create a single cube and use it across your reports. Each report would have one or more MDX queries associated with it to provide the data for the report. Depending on the tool you use, the MDX may or may not be easily accessible as the interface may provide a means of assembling the query without exposing the MDX to you.

Regarding the other, I'm not sure what "show flick shema" is.

Thanks,
Bryan

|||Thank you for this information !!!!!!!!!!!!!