Difference between Reconnect and Reflesh

Hello people,

In Analysis Services 2005's cube browser, there are two buttons "Refconnect" and "Refresh", which is the difference between them? When should I use each one?

Thanks in advance.

Hernán.

Hi Hernán,

A short description of these options is also available at: http://msdn2.microsoft.com/en-us/library/ms188087(SQL.90).aspx. Basically, if the connection to the server has been lost, you need to "reconnect". If the connection is alive and you just want to see the data changes caused by a recent processing operation for example, you would use "refresh".

Hope this helps,

Artur

Developer Edition vs Enterprise Edition

Does anyone know why the same cube
deployed on 2 different servers (one Enterprise Edition,
the other a Developer Edtion) with the same MDX
query would return 2 different Results?

MDX query I am using:

WITH MEMBER [Measures].[TimeChargedToDate] AS
'SUM({NULL:[Timesheet].[Date].&[2006].&[9].&[15]},[Time Charged])'

SELECT NON EMPTY
{
[Measures].[Time Charged],
[Measures].[TimeChargedToDate]
} ON COLUMNS,
NON EMPTY
{
(
[Project].[Project Number].&[Z07600] *
[Staff Member].[Staff Member Name].[Staff Member Name].ALLMEMBERS
)
} ON ROWS
FROM
(
SELECT
{[Timesheet].[Date].[Year].&[2006].&[9].&[2] : [Timesheet].[Date].[Year].&[2006].&[9].&[15]}
ON COLUMNS
FROM
(
SELECT ( [Project].[Project Number].&[Z07600] ) ON COLUMNS
FROM [Timesheet_Cube]
)
)

On the Enterprise Edition my results are:
Time Charged TimeChargedToDate
Z07600 Person One (null) 0.25
Z07600 Person Two (null) 2
Z07600 Person Three (null) 1
Z07600 Person Four 82 276
Z07600 Person Five 69.5 307.75
Z07600 Person Six (null) 187.25
Z07600 Person Seven (null) 24.5
Z07600 Person Eight (null) 77.5
Z07600 Person Nine 34 34

On the Developer Edition My results are:
Time Charged TimeChargedToDate
Z07600 Person Four 82 82
Z07600 Person Five 69.5 69.5
Z07600 Person Nine 34 34

Same query, same cube, same data (I backed up the processed cube
from the developer edition and restored it on the Enterprise Edition to
make sure that I did not reProcess the cube and have that affect something)
and somehow I get different results.

Any ideas as to why this is happening?

Hello. We can test this more rapidly if you can repeat this using the Adventure works cube and an MDX-example with that cube.

Are the service packs or hotfixes different on these servers? What tool do you use to query the cubes?

Regards

Thomas Ivarsson

|||

Nothing special has been done with the servers.
They are both 2005 servers and I am using Management
Studio as my MDX query tool.

I can try to reproduce this with adventureworks... It will
take some time.

|||

Are both servers sp1 servers. Do one or both have the same hotfixes after sp1?

Do you use the same partions on both servers(one or several?)

Regards

Thomas Ivarsson

|||

I cannot reproduce the problem with
AdventureWorks using MS sample UDM.

|||

It appears that my local machine
may not have SP1 installed. I will
give that a shot then try again.

Thanks.

|||

After installing SP1 on the Developer Edition,
this problem goes away... How interesting.

Thanks for you comments everyone!

Dev cube on 32-bit, production on 64?

Please help me confirm that the following scenario is reasonable.

I believe that I can develop an SSAS DB and cube on SQL 2005 "Dev/32 bit" and deploy dev builds locally using BIDS, and then deploy a production version, still using my local BIDS, straight to the "Production/64 bit" server (with same SP).

Am I correct that this will work and is a standard approach? Are there any obvious gotchas I should know about?

Thanks very much.

Daniel Upton

Yes this will work, and this approach is used by many users.

dev and production server - cache issue

i have two servers, both running SSAS, my production server recently became really slow performing lookups, just in one cube. I cant seem to find the root cause. I took a backup of the production and loaded it exactly as production but on a different server. I can query the cube in development fast. Looking at profiler, it seems the one cube with performance issues isnt looking up things in cache at first, it takes one try, then queries are fast. I have read some places online about cache warming, which is a great idea, but it doesnt make sense that the same cube in my dev environment is working just fine without cache warming. Anyone have any insights?

Are there any security roles applied in production that may not be applied in dev?

There are a couple of things around security roles that could result is slightly slower performance. If you are running as a server admin you sometimes get better performance as security roles are not applied at all. And the formula engine caches cannot be shared over security roles. So if you had mulitple different secuirty roles being used in production the amount of cache re-use would be reduced.

|||no, the security roles are the same. After investigating more, I found the Aggregation Manager util that comes with the samples and ran that against dev and prod, it looks like prod has 0 for aggregation counts where dev has counts..I wonder how that can be though, why one wouldn't be working and the other would when dev was just restored from prod's backup.

Is there anything in some settings or something where the aggregations are messed up or not working correctly for some reason? Maybe memory limits or disk, or something? I'm not having much luck finding out how they could be different. Even rebuilding the aggreations on prod through BIDS didnt change the count (i tested on a few partitions). Its like the prod server doesnt recognize the aggregations for some reason
|||let me rephrase that

the aggregation counts are there, like there are 25-30 per partition, but when i view the record count distirbution per aggreagation, all my records are in "No aggregation"
|||

Just to double check, are you using MOLAP storage?

How did you do your backup and restore? Did you do a full archive and restore or did you script the structures and re-process on dev?

Are you by any chance doing just a processData on your partitions? (this will not process aggregations)

Do you have any proactive caching on prod which might cause the cache to be flushed?

|||
yes using MOLAP

After more investigating, there was no full backup. We did try doing a full deployment from dev to production last night and it didnt change anything even after a full reprocess..

we dont have proactive caching set up

still stumped on this one..
|||we did another deploy and it seems to fix the issue, just a weird anamoly
|||Strange. All I can think if is that someone might have designed additional aggregations after the solution was deployed to production.

dev and production server - cache issue

i have two servers, both running SSAS, my production server recently became really slow performing lookups, just in one cube. I cant seem to find the root cause. I took a backup of the production and loaded it exactly as production but on a different server. I can query the cube in development fast. Looking at profiler, it seems the one cube with performance issues isnt looking up things in cache at first, it takes one try, then queries are fast. I have read some places online about cache warming, which is a great idea, but it doesnt make sense that the same cube in my dev environment is working just fine without cache warming. Anyone have any insights?

Are there any security roles applied in production that may not be applied in dev?

There are a couple of things around security roles that could result is slightly slower performance. If you are running as a server admin you sometimes get better performance as security roles are not applied at all. And the formula engine caches cannot be shared over security roles. So if you had mulitple different secuirty roles being used in production the amount of cache re-use would be reduced.

|||no, the security roles are the same. After investigating more, I found the Aggregation Manager util that comes with the samples and ran that against dev and prod, it looks like prod has 0 for aggregation counts where dev has counts..I wonder how that can be though, why one wouldn't be working and the other would when dev was just restored from prod's backup.

Is there anything in some settings or something where the aggregations are messed up or not working correctly for some reason? Maybe memory limits or disk, or something? I'm not having much luck finding out how they could be different. Even rebuilding the aggreations on prod through BIDS didnt change the count (i tested on a few partitions). Its like the prod server doesnt recognize the aggregations for some reason
|||let me rephrase that

the aggregation counts are there, like there are 25-30 per partition, but when i view the record count distirbution per aggreagation, all my records are in "No aggregation"
|||

Just to double check, are you using MOLAP storage?

How did you do your backup and restore? Did you do a full archive and restore or did you script the structures and re-process on dev?

Are you by any chance doing just a processData on your partitions? (this will not process aggregations)

Do you have any proactive caching on prod which might cause the cache to be flushed?

|||
yes using MOLAP

After more investigating, there was no full backup. We did try doing a full deployment from dev to production last night and it didnt change anything even after a full reprocess..

we dont have proactive caching set up

still stumped on this one..
|||we did another deploy and it seems to fix the issue, just a weird anamoly
|||Strange. All I can think if is that someone might have designed additional aggregations after the solution was deployed to production.

dev and production server - cache issue

i have two servers, both running SSAS, my production server recently became really slow performing lookups, just in one cube. I cant seem to find the root cause. I took a backup of the production and loaded it exactly as production but on a different server. I can query the cube in development fast. Looking at profiler, it seems the one cube with performance issues isnt looking up things in cache at first, it takes one try, then queries are fast. I have read some places online about cache warming, which is a great idea, but it doesnt make sense that the same cube in my dev environment is working just fine without cache warming. Anyone have any insights?

Are there any security roles applied in production that may not be applied in dev?

There are a couple of things around security roles that could result is slightly slower performance. If you are running as a server admin you sometimes get better performance as security roles are not applied at all. And the formula engine caches cannot be shared over security roles. So if you had mulitple different secuirty roles being used in production the amount of cache re-use would be reduced.

|||no, the security roles are the same. After investigating more, I found the Aggregation Manager util that comes with the samples and ran that against dev and prod, it looks like prod has 0 for aggregation counts where dev has counts..I wonder how that can be though, why one wouldn't be working and the other would when dev was just restored from prod's backup.

Is there anything in some settings or something where the aggregations are messed up or not working correctly for some reason? Maybe memory limits or disk, or something? I'm not having much luck finding out how they could be different. Even rebuilding the aggreations on prod through BIDS didnt change the count (i tested on a few partitions). Its like the prod server doesnt recognize the aggregations for some reason
|||let me rephrase that

the aggregation counts are there, like there are 25-30 per partition, but when i view the record count distirbution per aggreagation, all my records are in "No aggregation"
|||

Just to double check, are you using MOLAP storage?

How did you do your backup and restore? Did you do a full archive and restore or did you script the structures and re-process on dev?

Are you by any chance doing just a processData on your partitions? (this will not process aggregations)

Do you have any proactive caching on prod which might cause the cache to be flushed?

|||
yes using MOLAP

After more investigating, there was no full backup. We did try doing a full deployment from dev to production last night and it didnt change anything even after a full reprocess..

we dont have proactive caching set up

still stumped on this one..
|||we did another deploy and it seems to fix the issue, just a weird anamoly
|||Strange. All I can think if is that someone might have designed additional aggregations after the solution was deployed to production.

Determining Table Name from TableID in Cube Partition

Hi all,

I am trying to determine source table name of the cube partition using AMO.
I am able to get the partition source (which is of {Microsoft.AnalysisServices.DsvTableBinding} data type). There I can see the TableID property, but not the table's real database name. (TableID has dot between database schema and table name replaced by underscore).

Is there a way how to translate TableID into its original database name?

Thank you very much in advance.

Marek Stefanek
Use the TableID to find the DataTable object in the DataSourceView.Schema (which is a DataSet). Then in the ExtendedProperties collection of the DataTable, you should see some properties such as the original database table name and schema and the friendly name.|||Hi Matt,

Thank you a lot, it helped.

If I may, I would like to ask you another question: Is it possible to get the complete list of extended properties names? It is easy to retrieve the extended property value if you know the property name. But I have no idea from where I could get the property names.

Looking at properties of DataTable object during Script Task debug does not provide much information:

{System.Data.DataTable}
CaseSensitive: False
Columns: {System.Data.DataColumnCollection}
Constraints: {System.Data.ConstraintCollection}
Container: Nothing
DataSet: {System.Data.DataSet}
DefaultView: {System.Data.DataView}
DesignMode: False
DisplayExpression: ""
ExtendedProperties: {System.Data.PropertyCollection}
HasErrors: False
ChildRelations: {System.Data.DataRelationCollection.DataTableRelationCollection}
IsInitialized: True
Locale: {System.Globalization.CultureInfo}
MinimumCapacity: 50
Namespace: ""
ParentRelations: {System.Data.DataRelationCollection.DataTableRelationCollection}
Prefix: ""
PrimaryKey: {Length=0}
RemotingFormat: Xml {0}
Rows: {System.Data.DataRowCollection}
Site: Nothing
TableName: "dbo_VF_VYSLEDKY_2006"

It is probably obvious for skilled .NET developer, but for me its quite confusing.

Thanks!

Marek
|||

It's not a complete list, but here are the ones I think you're interested in:

public const string DBTableName = "DbTableName";
public const string DBSchemaName = "DbSchemaName";
public const string DBColumnName = "DbColumnName";
public const string FriendlyName = "FriendlyName";
public const string Description = "Description";