Derived attributes in a dimension

SQL 2005 SSAS - is it possible to create a derived attribute in a Dimension or does it have to be done in the underlying table.

Specifically, I have a geographic location Dimension where the top level is "Area" (USGulf, USWestCoast, Caribbean etc). Now there is one part of the company that likes to group these together into Zones. I can see how to do this in SQL in the underlying table or in reports in SSRS, but ideally would like to do this in the cube.

Thanks

You could add a Named Calculation for Zone to the dimension table in the Data Source View. This would be a SQL expression - the underlying table structure wouldn't change.

Deployment Failed

Hi Team,

Happy New Year to you all,

When i try to deploy my project (SSAS Step by Step) with a calculated Member, the deployment fails.

I actually get the following error.

Error 1 The 'Database' with 'ID' = 'SSAS Step by Step ' doesn't exist in the collection. 0 0

When I try to change the transactional deployment property to true so as for me to overwrite the existing database i get the following error;

Error 1 Members belong to different hierarchies in the function. 0 0

Could you please advise me on how to go about this.

Any help will highly appreciated.

Regards,

Ronald

Please provide the expression of the calculated member. From the error message it seems like you are trying to build a set from the members in different hierarchies. Might be you only wanted to build a tuple instead of set - but it is hard to tell without actual expression.

Deployment error -- Incorrect ImpersonationInfo object

Hi,

I'm new to SSAS 2005 and I'm walking through the Analysis Services tutorial in the online documentation. So far so good until when I wanted to deploy the project. The error is

Error 1 Parser: The syntax for the ImpersonationInfo object is incorrect. If the ImpersonateAccount value is used for ImpersonationInfo, then the Account property cannot be empty. 0 0

I don't know why it asked for ImpersonationInfo. I'm a local administrator of the computer and I want to deploy to local server.

However, when I went through the Deployment Wizard in Analysis Services, it was deployed successfully. When I switched back to Visual Studio AS project and tried to browse the cube or dimension, it failed again saying that

Either the user, [UserID], does not have access to the Analysis Services Tutorial database, or the database does not exist.

What am I missing? :(

Thanks
JennyNevermind. I've figured it out myself. I forgot to deploy the Adventure Works DW analysis services project first. Also, when I set up the data source, for some reasons, impersonation was selected by mistake.

Thanks!|||Hi Jenny Tam.
I had the same error. here is how to correct it
1-right-click on data source in solution explorer
2-select View Designer
3-select Impersonation Information tab, then Default box
It's all.|||Thanks - I have the same problem and it is great solution.
|||thank you

Deployment error -- Incorrect ImpersonationInfo object

Hi,

I'm new to SSAS 2005 and I'm walking through the Analysis Services tutorial in the online documentation. So far so good until when I wanted to deploy the project. The error is

Error 1 Parser: The syntax for the ImpersonationInfo object is incorrect. If the ImpersonateAccount value is used for ImpersonationInfo, then the Account property cannot be empty. 0 0

I don't know why it asked for ImpersonationInfo. I'm a local administrator of the computer and I want to deploy to local server.

However, when I went through the Deployment Wizard in Analysis Services, it was deployed successfully. When I switched back to Visual Studio AS project and tried to browse the cube or dimension, it failed again saying that

Either the user, [UserID], does not have access to the Analysis Services Tutorial database, or the database does not exist.

What am I missing? :(

Thanks
JennyNevermind. I've figured it out myself. I forgot to deploy the Adventure Works DW analysis services project first. Also, when I set up the data source, for some reasons, impersonation was selected by mistake.

Thanks!|||Hi Jenny Tam.
I had the same error. here is how to correct it
1-right-click on data source in solution explorer
2-select View Designer
3-select Impersonation Information tab, then Default box
It's all.|||Thanks - I have the same problem and it is great solution.
|||thank you

Deployment error -- Incorrect ImpersonationInfo object

Hi,

I'm new to SSAS 2005 and I'm walking through the Analysis Services tutorial in the online documentation. So far so good until when I wanted to deploy the project. The error is

Error 1 Parser: The syntax for the ImpersonationInfo object is incorrect. If the ImpersonateAccount value is used for ImpersonationInfo, then the Account property cannot be empty. 0 0

I don't know why it asked for ImpersonationInfo. I'm a local administrator of the computer and I want to deploy to local server.

However, when I went through the Deployment Wizard in Analysis Services, it was deployed successfully. When I switched back to Visual Studio AS project and tried to browse the cube or dimension, it failed again saying that

Either the user, [UserID], does not have access to the Analysis Services Tutorial database, or the database does not exist.

What am I missing? :(

Thanks
JennyNevermind. I've figured it out myself. I forgot to deploy the Adventure Works DW analysis services project first. Also, when I set up the data source, for some reasons, impersonation was selected by mistake.

Thanks!|||Hi Jenny Tam.
I had the same error. here is how to correct it
1-right-click on data source in solution explorer
2-select View Designer
3-select Impersonation Information tab, then Default box
It's all.|||Thanks - I have the same problem and it is great solution.
|||thank you

Deploying SSAS 2005 project in Server

Hi,

I Need to deploy all the three SSIS , SSAS and SSRS 2005 in One server. will it be fine to keep like this.

I have a doubt like, will it effect performance because SSIS have incremental load, SSAS also having incremental load then what will happen while accessing the reports while incremental process is going on

Can you please help me out

Thanks

Dinesh.

Hello Dinesh,

We installed SSIS, SSAS, SSRS and SharePoint Portal Server 2007 on one machine.

The system performs good with 4 GB of internal memory.

Later on the best installation is to have a farm with multiple servers, but in the beginning you can use just one.

Regards

Hessel

|||

Hi Dinesh Murali,

Incremental load is helpful indeed. But in general the following might turn into bottleneck if you do not have:

1) Hardware configuration (RAM, IO Controllers) required for the server to run at expected speed.

2) Network bandwidth for the Data transfer (Incase of SSIS and SSRS).

3) Try a quick test using task manager determine the following during peak time as well as off-peak time:

a) Memory consumed by Sql Server Manament Studio with all services (SSRS, SSIS, SSAS) enabled.

b) Time taken to load the report, complete the execution of SSIS Packages, or Processing a Cube or SSAS database.

Thanks

Subhash Subramanyam

Deploying a SSAS solution from File location using C#

Hi all,
i have created a SSAS solution using BIDS . I have saved the project on local hard disk. Now i need to deploy the solution through C# and not use C#. Can i do that ? if how how?

Regards..
Girija Shankar

Could you describe your goals a bit more? When you create an SSAS solution with BIDS, a script file is created that can be used to deploy a database to SSAS. You could also use the ASCMD application to submit scripts or execute a script via AMO. (I believe the Server object's Execute method handles this.)

B.

|||

Hi Bryan,

Thanks for the reply. The scenario is as folows:

1. I create a SSAS solution. i create the datasource,DSv,cubes and dimensions and Roles.I donot deploy that to the server but save the file to a local drive location.

2. If i go to the local directory path where I saved the solution i will find files such as .database, .cube,.partitions, .dim etc

3. i need to deploy the full solution from this file location to the server. After deployement i can process the cubes one by one using AMO. The processing part is clear to me using AMO but how to deploy from file location i am not able to figure out . All this has to be done through code (C#).

Regards...

Girija Shankar

|||

So, the file in the BIN folder is a complete or near-complete script. You should be able to use standard techniques to read the file (it's just XML though I would read it as a simple text file). With the script in memory, you can then submit it as a string through the AMO Server object's Execute method.

You may also want to review the ASCMD project that comes with the SSAS samples. I believe it has the functionality built in to read a file and execute it like I'm describing. It's also written in C#.

B.

|||

Bryan,

There are four files in bin folder . i suppose i would be using .asdatabase file. read that,envolope that with the create and Object Tags and execute that on server. Is this correct?

Regards...

Girija Shankar

|||

Hi, Another question.

There are some read only tags such as CreatedTimestamp, should i remove them while reading that?

Regards...
Girija Shankar

|||Another option is to use the Deployment Wizard (launched from the Start menu under Microsoft SQL Server->Analysis Services). This wizard has the option to generate a deployment script which is just an XMLA

command that can be sent the the server to deploy and optionally process the cube. This script deployment can be done using Adomd.Net, an XMLA Query in SQL Management Studio, or using the ASCMD.exe command line utilility available at http://www.microsoft.com/downloads/details.aspx?familyid=e719ecf7-9f46-4312-af89-6ad8702e4e6e&displaylang=en.

Deploying a SSAS solution from File location using C#

Hi all,
i have created a SSAS solution using BIDS . I have saved the project on local hard disk. Now i need to deploy the solution through C# and not use C#. Can i do that ? if how how?

Regards..
Girija Shankar

Could you describe your goals a bit more? When you create an SSAS solution with BIDS, a script file is created that can be used to deploy a database to SSAS. You could also use the ASCMD application to submit scripts or execute a script via AMO. (I believe the Server object's Execute method handles this.)

B.

|||

Hi Bryan,

Thanks for the reply. The scenario is as folows:

1. I create a SSAS solution. i create the datasource,DSv,cubes and dimensions and Roles.I donot deploy that to the server but save the file to a local drive location.

2. If i go to the local directory path where I saved the solution i will find files such as .database, .cube,.partitions, .dim etc

3. i need to deploy the full solution from this file location to the server. After deployement i can process the cubes one by one using AMO. The processing part is clear to me using AMO but how to deploy from file location i am not able to figure out . All this has to be done through code (C#).

Regards...

Girija Shankar

|||

So, the file in the BIN folder is a complete or near-complete script. You should be able to use standard techniques to read the file (it's just XML though I would read it as a simple text file). With the script in memory, you can then submit it as a string through the AMO Server object's Execute method.

You may also want to review the ASCMD project that comes with the SSAS samples. I believe it has the functionality built in to read a file and execute it like I'm describing. It's also written in C#.

B.

|||

Bryan,

There are four files in bin folder . i suppose i would be using .asdatabase file. read that,envolope that with the create and Object Tags and execute that on server. Is this correct?

Regards...

Girija Shankar

|||

Hi, Another question.

There are some read only tags such as CreatedTimestamp, should i remove them while reading that?

Regards...
Girija Shankar

|||Another option is to use the Deployment Wizard (launched from the Start menu under Microsoft SQL Server->Analysis Services). This wizard has the option to generate a deployment script which is just an XMLA

command that can be sent the the server to deploy and optionally process the cube. This script deployment can be done using Adomd.Net, an XMLA Query in SQL Management Studio, or using the ASCMD.exe command line utilility available at http://www.microsoft.com/downloads/details.aspx?familyid=e719ecf7-9f46-4312-af89-6ad8702e4e6e&displaylang=en.

Deploying a full database through XMLA and C#

Hi all,

I want to create , deploy and process the XMLA scripts for a full projectin SSAS . I want to do this through code C#. I have the XMLA's. I want the code to check wether the database exists and tehn drop it if it exists and create a new database , create and deploy and process the DSV, cubes and other objects one by one taking the XMLA. I also want to capture the log as to what happened , i mean wether it was sucessful or it threw an error.

Please give me some sample code as to how to go about it...

Regards...

Girija Shankar

Hi,

You can use AMO to check if the database exist, to drop it, to re-create it, to run XMLA scripts.

Sample code for running XMLA scripts with AMO:

http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=516930&SiteID=1

You mentioned that you want to drop the database if it exists and then re-create it. You can do that in a single step, the 'Create' command has the 'AllowOverwrite' parameter:

<Create AllowOverwrite='true'>

... the database definition here ...

</Create>

Adrian Dumitrascu

|||Hi Adrian,

Thanks for the answer. But I think you didnot get my question. I want to check in the server wether the database exists or not. if it exists i will drop that and take the xmla script from a specified location as a xmla file and then process it to cretae the necessary objects. then i process the cubes present in the Database one by one. Now the point is how do i check wether it exists and i want to catch the processing results ( wether success or failure). The XMLA will be a predifined file existing on local system.

Regards....
Girija Shankar