Description for Calculated Members

You can add descriptions to measures that are built under the Cube Structure tab. But I cannot see how to add a description to a Calculated Member. Is it possible to add a description to a Calculated Member?
Looks like this has already been answered.

Anyone interested, here is the link.

http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=325816&SiteID=1
|||You might be interested in looking at BIDSHelper www.codeplex.com/bidshelper it's free addin for BI Development Studio that adds a number of useful features including the ability to add descriptions to calculated members (through the member properties dialog) http://www.codeplex.com/bidshelper/Wiki/View.aspx?title=Calculation%20Helpers&referringTitle=Home

Derive columns from cube member?

I have a cube that I read in a data flow section using OLE DB. To get this to work I had to do an ad hoc query through a sql server database ( this is problem that is described in post 219068).

So I now have the data and I want to derive new columns based on that data using an if statement:

if ( ISNULL(" [Facility].[REGION].[NATCODE].[MEMBER_CAPTION] " ) comptype = "NAT" else comptype = "REG"

comptype is the new derived column I am creating.

But when I try to save this I get an error: The expression might contain an invalid token, an incomplete token, or an invalid element. It might not be well-formed, or might be missing part of a requred element such as parenthesis

I selected the cube element from the columns list in derived element, when it initially put it in it looked like:

[[Facility].[REGION].[NATCODE].[MEMBER_CAPTION] ]

and gave error:

The token "[" was not recognized. The expression cannot be parsed because it contains invalid elements at the location specified.

Below is the statement I use to get data back from AS

select * from openrowset('MSOLAP', 'DATASOURCE=local; Initial Catalog=Patient Demographics 2005;',
'with member [Measures].[TimeDisplayName] as [Calendar].CurrentMember.Name
SELECT NON EMPTY { [Measures].[TimeDisplayName],[Measures].[Case Count],
[Measures].[A100 Bathing],[Measures].[A100 Bed Chair Wheelchair],
[Measures].[A100 Bladder],[Measures].[A100 Bowel],
[Measures].[A100 Dressing Lower],[Measures].[A100 Dressing Upper],
[Measures].[A100 Eating],[Measures].[A100 Grooming],
[Measures].[A100 Stairs],[Measures].[A100 Toilet],
[Measures].[A100 Toileting],[Measures].[A100 Tub Shower],
[Measures].[A100 Walk Wheelchair],[Measures].[A200 Comprehension],
[Measures].[A200 Expression],[Measures].[A200 Interaction],
[Measures].[A200 Memory],[Measures].[A200 Problem Solving],
[Measures].[D100 Bathing],[Measures].[D100 Bed Chair Wheelchair],
[Measures].[D100 Bladder],[Measures].[D100 Bowel],
[Measures].[D100 Dressing Lower],[Measures].[D100 Dressing Upper],
[Measures].[D100 Eating],[Measures].[D100 Grooming],
[Measures].[D100 Stairs],[Measures].[D100 Toilet],
[Measures].[D100 Toileting],[Measures].[D100 Tub Shower],
[Measures].[D100 Walk Wheelchair],[Measures].[D200 Comprehension],
[Measures].[D200 Expression],[Measures].[D200 Interaction],
[Measures].[D200 Memory],[Measures].[D200 Problem Solving] } ON COLUMNS,
({[Facility].[REGION].[NATCODE], [Facility].[REGION].[REGCODE]} *
[RIC].[CMGGRPCD].ALLMEMBERS ) ON ROWS FROM [CMG Demographics]
WHERE [Calendar].[Quarter 2 (2007)]')

So can some one help me on how I can do this derived colum.

Try this...

isnull(<fieldname>) == true ? "NAT" : "REG"

To get <fieldname> expand the 'columns' folder in the derived column transformation editor.

|||Use a derived column to create new columns.

New Column Name: comptype
Expression: ISNULL([InputColumn]) ? "NAT" : "REG"|||

Anthony Martin wrote:

Try this...

isnull(<fieldname>) == true ? "NAT" : "REG"

Anthony, just simplify that statement. ISNULL() returns a boolean value, so there's no need to doubly check for a true status or not. Just drop the "== true" part.|||

Thanks, good point. Just a habit I guess.

|||

I have figured out what I had to do, but I do not know why I had to do this way, but first while I saw after I posted that used used bad assignment of derived column comptype = <value>. that was one of my many different attempts around the question I posted about.

1st) I could never get the if else statment to work in derived field, I had to use conditional if (? logic for my ifs

2nd) I could not referenct the the MDX columns in my conditional if. It did not like all of the '[' and ']', so I had to convert all of the columns from MDX column name to a more standard sql column name, basically renamed column to not have '[', ']' in them

Does any one know why this does not work neatly, MDX is supposed to have been integrated with SSIS, but I had to do multiple tricks to get the data, right from start where I had to use ad hoc query though a sql database to execute the MDX.

This also results in fact that during selection of MDX it did not maintain any of the data types and converted everything to chararacter field of size 4000, which gave me trunctaion warnings in the selection of MDX into SSIS

Derive columns from cube member?

I have a cube that I read in a data flow section using OLE DB. To get this to work I had to do an ad hoc query through a sql server database ( this is problem that is described in post 219068).

So I now have the data and I want to derive new columns based on that data using an if statement:

if ( ISNULL(" [Facility].[REGION].[NATCODE].[MEMBER_CAPTION] " ) comptype = "NAT" else comptype = "REG"

comptype is the new derived column I am creating.

But when I try to save this I get an error: The expression might contain an invalid token, an incomplete token, or an invalid element. It might not be well-formed, or might be missing part of a requred element such as parenthesis

I selected the cube element from the columns list in derived element, when it initially put it in it looked like:

[[Facility].[REGION].[NATCODE].[MEMBER_CAPTION] ]

and gave error:

The token "[" was not recognized. The expression cannot be parsed because it contains invalid elements at the location specified.

Below is the statement I use to get data back from AS

select * from openrowset('MSOLAP', 'DATASOURCE=local; Initial Catalog=Patient Demographics 2005;',
'with member [Measures].[TimeDisplayName] as [Calendar].CurrentMember.Name
SELECT NON EMPTY { [Measures].[TimeDisplayName],[Measures].[Case Count],
[Measures].[A100 Bathing],[Measures].[A100 Bed Chair Wheelchair],
[Measures].[A100 Bladder],[Measures].[A100 Bowel],
[Measures].[A100 Dressing Lower],[Measures].[A100 Dressing Upper],
[Measures].[A100 Eating],[Measures].[A100 Grooming],
[Measures].[A100 Stairs],[Measures].[A100 Toilet],
[Measures].[A100 Toileting],[Measures].[A100 Tub Shower],
[Measures].[A100 Walk Wheelchair],[Measures].[A200 Comprehension],
[Measures].[A200 Expression],[Measures].[A200 Interaction],
[Measures].[A200 Memory],[Measures].[A200 Problem Solving],
[Measures].[D100 Bathing],[Measures].[D100 Bed Chair Wheelchair],
[Measures].[D100 Bladder],[Measures].[D100 Bowel],
[Measures].[D100 Dressing Lower],[Measures].[D100 Dressing Upper],
[Measures].[D100 Eating],[Measures].[D100 Grooming],
[Measures].[D100 Stairs],[Measures].[D100 Toilet],
[Measures].[D100 Toileting],[Measures].[D100 Tub Shower],
[Measures].[D100 Walk Wheelchair],[Measures].[D200 Comprehension],
[Measures].[D200 Expression],[Measures].[D200 Interaction],
[Measures].[D200 Memory],[Measures].[D200 Problem Solving] } ON COLUMNS,
({[Facility].[REGION].[NATCODE], [Facility].[REGION].[REGCODE]} *
[RIC].[CMGGRPCD].ALLMEMBERS ) ON ROWS FROM [CMG Demographics]
WHERE [Calendar].[Quarter 2 (2007)]')

So can some one help me on how I can do this derived colum.

Try this...

isnull(<fieldname>) == true ? "NAT" : "REG"

To get <fieldname> expand the 'columns' folder in the derived column transformation editor.

|||Use a derived column to create new columns.

New Column Name: comptype
Expression: ISNULL([InputColumn]) ? "NAT" : "REG"|||

Anthony Martin wrote:

Try this...

isnull(<fieldname>) == true ? "NAT" : "REG"

Anthony, just simplify that statement. ISNULL() returns a boolean value, so there's no need to doubly check for a true status or not. Just drop the "== true" part.|||

Thanks, good point. Just a habit I guess.

|||

I have figured out what I had to do, but I do not know why I had to do this way, but first while I saw after I posted that used used bad assignment of derived column comptype = <value>. that was one of my many different attempts around the question I posted about.

1st) I could never get the if else statment to work in derived field, I had to use conditional if (? logic for my ifs

2nd) I could not referenct the the MDX columns in my conditional if. It did not like all of the '[' and ']', so I had to convert all of the columns from MDX column name to a more standard sql column name, basically renamed column to not have '[', ']' in them

Does any one know why this does not work neatly, MDX is supposed to have been integrated with SSIS, but I had to do multiple tricks to get the data, right from start where I had to use ad hoc query though a sql database to execute the MDX.

This also results in fact that during selection of MDX it did not maintain any of the data types and converted everything to chararacter field of size 4000, which gave me trunctaion warnings in the selection of MDX into SSIS

Deployment worked when it shouldn't have done

I've just had a situation where, when trying to process a cube, it failed because one of my Script Commands referenced a non-existent dimension.

Now I have no problem with that - its easily fixed. My problem is that the cube is obviously faulty so why was I allowed to deploy it? The deployment should surely have failed shouldn't it?

Is this a deployment bug?

Here is the script command:

Scope(Leaves([Currency]), EXCEPT( [Currency Conversion].[Currency Conversion].MEMBERS, {[Currency Conversion].[Currency Conversion].&[<None>]} ))

-Jamie

P.S. Does anyone from the SSAS team in Redmond frequent this forum?

The server has a very flexible, forgiving, and complex system for attempting to resolve name ambiguities. So if you simply provide the text [Currencty Conversion] it may be a dimension name or a hierarchy name or a level name or even a member name. (Yes, if the server can uniquely resolve this to a member name it will although a dimension prefix is a good idea at a minimum.) As a result, until the server has read the data during processing it does not fully bind the names and thus does not know for sure that this is a dimension name that cannot be found.

Yes, people such as myself from the SSAS team in Redmond do frequent this forum.

|||

Good to know - on both counts. Thank you Matt.

-Jamie

sql

Deployment to one Database 'Clearing' Another...

Hi,

We have a setup whereby our production server builds to a build cube overnight, and if and when this completes successfully, and passes sanity checks, it automatically backs up and restores the cube over the top of a 'live cube'.

This seemed like a fair enough plan - it allows developers to deploy to the production server without breaking the production-live cube, however, we have noticed that deploying to the build cube database sometimes results in the clearing out of the production cube database (much to the users' dismay!).

Anyone else seen anything similar, or have any ideas as to what the problem could be?

Thanks,

Will.

First time I hear about something like that.

Try and see if you can use Synchronization functionality to deploy your changes from test to production. This is more straightforward then backup-copy-restore.

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

|||

Hi Edward,

Thanks for your reply.

We currently build the cube on the production server (and 'deploy' the live cube to a databae on the same server) - we're of the view that synchronization is not possible with two databases on the same server. Is this correct?

Regards,

Will.

|||

You are right. You cannot synch from one database to another on the same server instance.

What I suggest in your case is to install another instance of Analysis Server on the same server machine. This case you achive better separation between test and production. If something wrong goes with the test instance, you can easily restart it or, it would be easier to monitor usage of the production server instance without developers interfere. Then, also you'd be able to use synch. Overall in your case second instance might be cleaner solution.

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

deployment failure

I am getting following deployment fail error when I try to deploy the
cube on the server:
"Error 1 The project could not be deployed to the 'myserver_2005'
server because of the following connectivity problems : A connection
cannot be made to redirector. Ensure that 'SQL Browser' service 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 "
How do I start the analysis server so that I can deploy the cube? I
have already manually started the analysis server and browser is
running. How do I connect to the analysis server using SSMS? I am
getting an error when I use SSMS to connect to analysis server 'a
connection can not be made to the redirector. Make sure that 'sql
browser service' is running' I made sure. I am guessing that this
error message is misleading and points to some other error. Thanks.What's with all these new threads?
See my reply to your original post.
ML
--
Matija Lah, SQL Server MVP
http://milambda.blogspot.com/

deploying cube w/o database dependencies

Hi,

I have been asked to find a way to deploy a SQL 2005 cube with no dependencies on the relational database. Does any one have any suggestions of how this might be achieved?

Thanks

ahur

Not sure what you mean by "deploy".

Do you have a cube already you have built? Are you planning to create a new cube in Analysis Services and start writing data directly into it instead of processing cube based on the relational database?

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

|||

You can process the cube on one machine and then use either backup/restore or the Database Synchronization Wizard to move the processed database to another machine. If you are wanting to deploy to customers or to offline machines, then you may want to consider creating local cubes and copying these over.

Is this what you were looking for?

|||

Hi,

I have the same case, I need to deploy the cubes structure and data to another machine ( from development to production) , is there any way to achieve that?

Thanks

|||From what you describe, my posting earlier in this thread should provide some solutions. Please clarify your scenario if none of these solutions meet your needs.

deploying cube w/o database dependencies

Hi,

I have been asked to find a way to deploy a SQL 2005 cube with no dependencies on the relational database. Does any one have any suggestions of how this might be achieved?

Thanks

ahur

Not sure what you mean by "deploy".

Do you have a cube already you have built? Are you planning to create a new cube in Analysis Services and start writing data directly into it instead of processing cube based on the relational database?

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

|||

You can process the cube on one machine and then use either backup/restore or the Database Synchronization Wizard to move the processed database to another machine. If you are wanting to deploy to customers or to offline machines, then you may want to consider creating local cubes and copying these over.

Is this what you were looking for?

Deploying cube changes to multiple sites

Hi,

How does one deploy cube changes (say adding a new field into a dimension) into multiple customer databases without overwriting their Kpis?

We have a SSAS application which will snap into our existing application and allow customers to define Environmental, Health & Safety Kpis. Each customer will have different Kpis, so how do we deploy incremental updates, without overwriting those Kpis?

I know we can download a customers SSAS database into a new VS project, and copy the Kpis into an XMLA deployment script, but that is a lot of work for each customer.

What I am hoping to find is something like a SQL Script that makes the schema changes, but does not touch the data (Kpis etc.). Of course, my SQL knowledge has only got me into trouble in SSAS, so who knows.

Thanks,

--Phil

Probably the best approach would be to write a script which uses DISCOVER_XML_METADATA to retrieve the cube's metadata, and then changes the XML my injecting your KPIs and then sending this XMLA script inside <Alter> command.

deploying changes to cub

Hi,

I have a cube in production server and i want to update that with my latest copy. Is the only way to update it is generate an .xmla again through the deployment wizard? what if i just added a new cube on top of my existing ones. do i need to replace all of them again?

cherriesh

From the underlying XMLA perspective this is definitely possible. The Deployment Wizard uses an Alter command with the AllowCreate option set to true, so aslong as you have not altered the structure of any of the existing cubes and have not chosen the option to do a process operation as part of the deploy, then you should be able to deploy using the Wizard without loosing any data. You would then only need to process the new cube.

It would also be an option to build a CREATE script for just the new cube, but I don't know of any tools that would build this for you. If you have a dev server you could deploy the database to it and connect to it using SSMS and then right click on the new cube and script out the CREATE command.

Note that a cube is dependant on other objects in the database like the dimensions and if any of these have been changed or added then the create script on its own might not work.

Deploying AS to Production

Hi,

Is this the right deployment procedure: I copied the whole project to production server and re-processes the cube. Does the OLAP database resides in \bin or is it the one in C:\Program Files\Microsoft SQL Server\MSSQL.2\OLAP\Data? What's the extension name?

Thanks alot!

cherriesh

Copy the whole project is not really required. The database in the \bin folder is the one that was last built from the BI Development Studio (BIDS), but you do not deploy this file by copying it. In your program files folder where you have installed BIDS you should have an Analysis Services folder with an Analysis Services deployment wizard. This wizard allows you to do a number of things that are useful when deploying to production such as changing or retaining data source connection strings and retaining roles and partitions which may be different between dev and prod.

Also from BIDS, you can setup different profiles and change the target server for the deploy operation from BIDS, but if you needed to change data source connections you would need to remember to do that yourself.

|||

Hi,

After deployment, does the AS database reside in the drive where Analysis Services resides? I'm afraid in the server it is installed in C:\ with only 10GB instead of in the D:\ which has much greater space capacity..

cherriesh

|||

Hi,

If you open up the management studio and right click on the server instance and look at the properties. The Property DataDir will tell you where it's storing the databases. You can change it if it is wrong from C: to D: and you might want to change the backup and log directories property too (BackupDir, LogDir).

Hope that helps

Matt

|||

Hi,

Done with LogDir, BackUpDir and DataDir. I also saw the AllowedBrowsingFolder that is set to :

C:\Program Files\Microsoft SQL Server\MSSQL.3\OLAP\Log|C:\Program Files\Microsoft SQL Server\MSSQL.3\OLAP\Backup

Do I have to change this also?

I've done the Deployment wizard and I have generated the .xmla script. How to proceed from there?

cherriesh

|||

Hi,

Probably worth doing the AllowedBrowsingFolder to, it populates the drop down when you are backup and restore, various things like that.

On the assumption your script looks something like:

<Alter AllowCreate="true" ObjectExpansion="ObjectProperties" xmlns="http://schemas.microsoft.com/analysisservices/2003/engine">

<Object />

<ObjectDefinition>

<Server xmlns:xsd="http://www.w3.org/2001/XMLSchema" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlnsdl2="http://schemas.microsoft.com/analysisservices/2003/engine/2" xmlnsdl2_2="http://schemas.microsoft.com/analysisservices/2003/engine/2/2">

<ID>SERVER</ID>

<Name>SERVER</Name>

<ServerProperties>

<ServerProperty>

<Name>BackupDir</Name>

<Value>D:\Microsoft SQL Server\MSSQL.4\OLAP\Backup</Value>

</ServerProperty>

</ServerProperties>

</Server>

</ObjectDefinition>

</Alter>

Just open an xmla query, run it on the server and restart the service. It should in theory create the directories, pretty sure it did when i have done this before. If there is anything already on the AS Server, best to back that up first then restore it in the new location.

Any problems, just post again.

Matt

|||

hi,

sorry im really new to this. how do i run this .xmla in the server? what command?

thanks a lot!

cherriesh

|||

Hi,

Not to worry. If you open up management studio, click on File, then New, Analysis Services XMLA Query. Type in the server you are changing into "Server Name"

Or there is an icon on the query tool bar, has XMLA in green on it, if you hover over it it will say "Analysis Services XMLA Query".

An Alternative to not running XMLA:

If you are in management studio, connect to the server and right click the server in the object explorer. You can manually change the settings in the properties there.

If I didn't make any sense:

http://www.microsoft.com/technet/prodtechnol/sql/2005/ssasproperties.mspx

(Figures 1 and 2 are the wrong way around though)

remember to restart it the service

Hope that helps

Matt

|||

Thanks a lot! this really helps. is it the AS service that i need to restart? because, when i ran the .xmla, i could already see the AS database in the Management Studio. Does it really important to restart?

thanks!

cherriesh

|||

The changes you make won't actually take effect until you do, just the AS service.

No problem

Matt

Deploying AS to Production

Hi,

Is this the right deployment procedure: I copied the whole project to production server and re-processes the cube. Does the OLAP database resides in \bin or is it the one in C:\Program Files\Microsoft SQL Server\MSSQL.2\OLAP\Data? What's the extension name?

Thanks alot!

cherriesh

Copy the whole project is not really required. The database in the \bin folder is the one that was last built from the BI Development Studio (BIDS), but you do not deploy this file by copying it. In your program files folder where you have installed BIDS you should have an Analysis Services folder with an Analysis Services deployment wizard. This wizard allows you to do a number of things that are useful when deploying to production such as changing or retaining data source connection strings and retaining roles and partitions which may be different between dev and prod.

Also from BIDS, you can setup different profiles and change the target server for the deploy operation from BIDS, but if you needed to change data source connections you would need to remember to do that yourself.

|||

Hi,

After deployment, does the AS database reside in the drive where Analysis Services resides? I'm afraid in the server it is installed in C:\ with only 10GB instead of in the D:\ which has much greater space capacity..

cherriesh

|||

Hi,

If you open up the management studio and right click on the server instance and look at the properties. The Property DataDir will tell you where it's storing the databases. You can change it if it is wrong from C: to D: and you might want to change the backup and log directories property too (BackupDir, LogDir).

Hope that helps

Matt

|||

Hi,

Done with LogDir, BackUpDir and DataDir. I also saw the AllowedBrowsingFolder that is set to :

C:\Program Files\Microsoft SQL Server\MSSQL.3\OLAP\Log|C:\Program Files\Microsoft SQL Server\MSSQL.3\OLAP\Backup

Do I have to change this also?

I've done the Deployment wizard and I have generated the .xmla script. How to proceed from there?

cherriesh

|||

Hi,

Probably worth doing the AllowedBrowsingFolder to, it populates the drop down when you are backup and restore, various things like that.

On the assumption your script looks something like:

<Alter AllowCreate="true" ObjectExpansion="ObjectProperties" xmlns="http://schemas.microsoft.com/analysisservices/2003/engine">

<Object />

<ObjectDefinition>

<Server xmlns:xsd="http://www.w3.org/2001/XMLSchema" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlnsdl2="http://schemas.microsoft.com/analysisservices/2003/engine/2" xmlnsdl2_2="http://schemas.microsoft.com/analysisservices/2003/engine/2/2">

<ID>SERVER</ID>

<Name>SERVER</Name>

<ServerProperties>

<ServerProperty>

<Name>BackupDir</Name>

<Value>D:\Microsoft SQL Server\MSSQL.4\OLAP\Backup</Value>

</ServerProperty>

</ServerProperties>

</Server>

</ObjectDefinition>

</Alter>

Just open an xmla query, run it on the server and restart the service. It should in theory create the directories, pretty sure it did when i have done this before. If there is anything already on the AS Server, best to back that up first then restore it in the new location.

Any problems, just post again.

Matt

|||

hi,

sorry im really new to this. how do i run this .xmla in the server? what command?

thanks a lot!

cherriesh

|||

Hi,

Not to worry. If you open up management studio, click on File, then New, Analysis Services XMLA Query. Type in the server you are changing into "Server Name"

Or there is an icon on the query tool bar, has XMLA in green on it, if you hover over it it will say "Analysis Services XMLA Query".

An Alternative to not running XMLA:

If you are in management studio, connect to the server and right click the server in the object explorer. You can manually change the settings in the properties there.

If I didn't make any sense:

http://www.microsoft.com/technet/prodtechnol/sql/2005/ssasproperties.mspx

(Figures 1 and 2 are the wrong way around though)

remember to restart it the service

Hope that helps

Matt

|||

Thanks a lot! this really helps. is it the AS service that i need to restart? because, when i ran the .xmla, i could already see the AS database in the Management Studio. Does it really important to restart?

thanks!

cherriesh

|||

The changes you make won't actually take effect until you do, just the AS service.

No problem

Matt

deploy/ process cube issue

Hi,

I'm having difficulty to deploy and process cubes to the differenct server now.

It may be caused by our internal server problem but before checking that, please advice the following issue:

(To deploy and process cubes to local machine, and differenct server works perfectly previously. This problem just started few days ago.)

If I delopy and process the cube to the localhost then it works fine.

I also ran the sql profiler and trace the process.

But in my local machine, in BIDS if I change the target server and process cubes to the different server then

it just start deploying but keep running and never ending.

Also, I've tried to remote the target server and process cube in server machine while tracing that process.

The thing is every single process works fine but it's not ending. In trace file, I can see " finished processing cube"

But, cube process keeps running and I couldn't see " command end" eventclass in trace file.

Right now, we reload data into another fact table in test server , not production server. ( cube is in production)

but is ther any possibility to affect the cube processing?

Other then that, I have no idea why this happens.

I would appreciate if anybody can give me some advise.

Thanks.

FYI,

I've talked to another developer who are working on ETL now, and he told me that he've truncated some data in exisiting table and reloaded data. And also,he and did some changes in table structures and reload the data into another new fact table.

Does it matter to deploy and process the cube ?

(I'm guessing that table structure won't affect the processing issue since it will give an error before deploying, if it causes the table relationship or attibutes changes.)

/**

Now, I've taken small portion of the cube structure and create new project and deploy to the different server and it works perfectly. But is this the only way to solve this problem? Any suggestion?

Please let me know.

Thanks.

|||

We've occassionally had this same problem. It appears that the files that contain the cube and dimension data sometimes become corrupt. When you process the cube, all the data is written to a second set of files. There is then a drop/rename type of activitythat swaps out the new files in place of the old files. It's the swapping of files that you are waiting for.

Recommendation is to drop existing cube altogether, redeploy structure, and then reprocess. Very likely that everything will correctly at that point.

In fact, you're already seeing that your new project/cube are working correctly on the same server.

Hope this helps,

Pam W.

|||

Thanks for your valuable information.

could you tell me what exact file do I need to drop? All cube files (*.cube and *.partition) ?

What else do I need to take a look?

I've also tried to use ' deployment wizard' and overwrite xmla script and it's showing successful depolyment.

But processing still doesn't work.

I've tried to drop cube file and to rebuild the cube but it's just giving an error and can't process cubes.

I would approciate if you can give me more details in regards to fixing this problem.

Please let me know.

Thanks.

|||

I've just tried to create new database into the target server and redeploy cubes with new db then it works now.

It seems file is corrupted but still doesn't understand well. (I followed your recommendation but it doesn't work)

Anyway, thanks for your recommendation.

If anybody has a similar situation and can give some comments then I would greatly appreciate it.

Thanks.

deploy/ process cube issue

Hi,

I'm having difficulty to deploy and process cubes to the differenct server now.

It may be caused by our internal server problem but before checking that, please advice the following issue:

(To deploy and process cubes to local machine, and differenct server works perfectly previously. This problem just started few days ago.)

If I delopy and process the cube to the localhost then it works fine.

I also ran the sql profiler and trace the process.

But in my local machine, in BIDS if I change the target server and process cubes to the different server then

it just start deploying but keep running and never ending.

Also, I've tried to remote the target server and process cube in server machine while tracing that process.

The thing is every single process works fine but it's not ending. In trace file, I can see " finished processing cube"

But, cube process keeps running and I couldn't see " command end" eventclass in trace file.

Right now, we reload data into another fact table in test server , not production server. ( cube is in production)

but is ther any possibility to affect the cube processing?

Other then that, I have no idea why this happens.

I would appreciate if anybody can give me some advise.

Thanks.

FYI,

I've talked to another developer who are working on ETL now, and he told me that he've truncated some data in exisiting table and reloaded data. And also,he and did some changes in table structures and reload the data into another new fact table.

Does it matter to deploy and process the cube ?

(I'm guessing that table structure won't affect the processing issue since it will give an error before deploying, if it causes the table relationship or attibutes changes.)

/**

Now, I've taken small portion of the cube structure and create new project and deploy to the different server and it works perfectly. But is this the only way to solve this problem? Any suggestion?

Please let me know.

Thanks.

|||

We've occassionally had this same problem. It appears that the files that contain the cube and dimension data sometimes become corrupt. When you process the cube, all the data is written to a second set of files. There is then a drop/rename type of activitythat swaps out the new files in place of the old files. It's the swapping of files that you are waiting for.

Recommendation is to drop existing cube altogether, redeploy structure, and then reprocess. Very likely that everything will correctly at that point.

In fact, you're already seeing that your new project/cube are working correctly on the same server.

Hope this helps,

Pam W.

|||

Thanks for your valuable information.

could you tell me what exact file do I need to drop? All cube files (*.cube and *.partition) ?

What else do I need to take a look?

I've also tried to use ' deployment wizard' and overwrite xmla script and it's showing successful depolyment.

But processing still doesn't work.

I've tried to drop cube file and to rebuild the cube but it's just giving an error and can't process cubes.

I would approciate if you can give me more details in regards to fixing this problem.

Please let me know.

Thanks.

|||

I've just tried to create new database into the target server and redeploy cubes with new db then it works now.

It seems file is corrupted but still doesn't understand well. (I followed your recommendation but it doesn't work)

Anyway, thanks for your recommendation.

If anybody has a similar situation and can give some comments then I would greatly appreciate it.

Thanks.

deploy Cube to production server?

Hello,
how can I deploy the changes of cubes / dimensions from development to the production server?
cheers
Tobias

Hi,

You can first archive (export) CAB with cubes from the development server and then restore it on the production server.

Remember to process all the cubes before exporting them.

You can use the msmdarch.exe program (it is located in the Analysis Services / BIN folder) or do the export/import directly from the Analysis Manager.

Hope, this helps

Best regards

Ewa

Deploy / process cube

Hi

When I deploy and process my cube in analysis services the server just runs and runs.

Several hours later it is still running - I am pretty sure that it hangs.

The cube is based on tables which max. have 2000 rows so it is not a large cube.

I have verified that it deploys to the correct server instance.

Can anyone give some advice?

Regards,

Dennis

Do you know what part of the processing it hands on (specific dimension, measure group, partition, etc.)? If the SSAS solution is based on a SQL Server database, can you run Profiler and monitor the queries that are being generated by SSAS and submitted to SQL Server?

Dave Fackler

denying read rights to a dimension

Hi,

When you give read rights to a cube then the dimension gets read rights by default. Is there a way by which you can grant read rights to certain dimensions of that cube and deny the read rights to certain dimensions of that cube?

thanx.

In AS 2005, just create a new Role, and select what the users belonging to that role can see in the Dimension Data tab. If you select the "Deselect All Members" option, then the users of the role won't be able to see any data of that dimension.

In AS 2000, you have to add the dimension to the restricted dimension list when editing the proper role.

Hope this help,

Santi

|||

Thanx. But then in that case I can still see the dimension. However if I dont want to show any of the attributes of a dimension, is it possible to hide the dimension completely..