Analysis Services & Default Value

Is it possible to define a default value for each a parameter also in a dataset?

I have a dataset with a parameter from the dimension Time. I would like to change the default value from the parameter. I tried to create a dataset to get the current year unique name, but if I set it in the parameter dialog it does not recognize the value.

I have a dataset in a report with the parameter Year from the dimension Time.

In the dataset I have to select a default value for the parameter. There is no other option.

In the reports parameters list I can specify a default value. This is not recognized, if it does not mach previous one.

Is it possible to set the default value of such a parameter to the current year value?

Analysis 2005 Dimension Max members

Dear Anyone,

Does the dimensions for Analysis Services 2005 have any member limitations? Say a parent can only have so many members?

Thanks,

Joseph

The numbers of members in an level in an attribute hierarchy in Analysis Services 2005 has the limit of a 32-bit signed integer (2**32 = approx. 2 billion). The AS 2000 maximum of no more than 64,000 members per parent no longer applies. Also note that since the concept of a "dimension" has changed between the two versions of Analysis Services, you can now define more than one hierarchy per dimension in AS 2005, which makes it less meaningful to discuss the maximum number of members per parent. As I indicate, it is now the number of members in an attribute hierarchy that is interesting (an attribute hierarchy by default consists of two levels - an "All" level and a level including all of the members of that specific attribute) .|||

Is there a possibility that when processing a dimension (not parent and child) a memory error an happen even though the dimension is quite large?

The reason I asked is that we encountered a memory error problem when processing a large dimension. Understandably our dimension is large but it was also my impression that sql2005 removed the 64k limit.

AMO: list of cubes using a dim

Hello!

I'm trying to get a list of all cubes which are using a specific dimension with AMO. I could get a list of all cubes, then all dimensions used by each cube and check where my dimension is used but I feel there must be a more elegent solution ;-)
There is a GetReferences method in the Dimension object but I don't know how to use it, any idea or a better way to get my list ?

Other question: is there a way to automatically do a full or update process on each dim of a cube when we process this cube without having to list them all before ?

Thx!

I suggest to write the code that you mentioned: iterate over all Cubes and their CubeDimensions, checking which CubeDimension points to your Dimension. Because one Dimension can be included multiple times in a Cube (role-playing dimensions feature), you won't be able to use the cube.Dimensions.Contain(...) method (because that method is based on the ID of the CubeDimension which is not necessarily the same as the ID of the Dimension). When checking if a CubeDimension points to your Dimension, it's faster to compare the string IDs (CubeDimension.DimensionID with Dimension.ID, case insensitive compare), than to use the CubeDimension.Dimension property (because that does a lookup).

The GetReferences method will do the same iterations over all the Cubes and their CubeDimensions, plus more (so it won't be faster than your code).

Adrian

|||Thx for your help Adrian!

AMO: list of cubes using a dim

Hello!

I'm trying to get a list of all cubes which are using a specific dimension with AMO. I could get a list of all cubes, then all dimensions used by each cube and check where my dimension is used but I feel there must be a more elegent solution ;-)
There is a GetReferences method in the Dimension object but I don't know how to use it, any idea or a better way to get my list ?

Other question: is there a way to automatically do a full or update process on each dim of a cube when we process this cube without having to list them all before ?

Thx!

I suggest to write the code that you mentioned: iterate over all Cubes and their CubeDimensions, checking which CubeDimension points to your Dimension. Because one Dimension can be included multiple times in a Cube (role-playing dimensions feature), you won't be able to use the cube.Dimensions.Contain(...) method (because that method is based on the ID of the CubeDimension which is not necessarily the same as the ID of the Dimension). When checking if a CubeDimension points to your Dimension, it's faster to compare the string IDs (CubeDimension.DimensionID with Dimension.ID, case insensitive compare), than to use the CubeDimension.Dimension property (because that does a lookup).

The GetReferences method will do the same iterations over all the Cubes and their CubeDimensions, plus more (so it won't be faster than your code).

Adrian

|||Thx for your help Adrian!

AMO: list of cubes using a dim

Hello!

I'm trying to get a list of all cubes which are using a specific dimension with AMO. I could get a list of all cubes, then all dimensions used by each cube and check where my dimension is used but I feel there must be a more elegent solution ;-)
There is a GetReferences method in the Dimension object but I don't know how to use it, any idea or a better way to get my list ?

Other question: is there a way to automatically do a full or update process on each dim of a cube when we process this cube without having to list them all before ?

Thx!

I suggest to write the code that you mentioned: iterate over all Cubes and their CubeDimensions, checking which CubeDimension points to your Dimension. Because one Dimension can be included multiple times in a Cube (role-playing dimensions feature), you won't be able to use the cube.Dimensions.Contain(...) method (because that method is based on the ID of the CubeDimension which is not necessarily the same as the ID of the Dimension). When checking if a CubeDimension points to your Dimension, it's faster to compare the string IDs (CubeDimension.DimensionID with Dimension.ID, case insensitive compare), than to use the CubeDimension.Dimension property (because that does a lookup).

The GetReferences method will do the same iterations over all the Cubes and their CubeDimensions, plus more (so it won't be faster than your code).

Adrian

|||Thx for your help Adrian!

AMO Question - Distinct members in Cube

Hi,

How to filter members in a dimension.

To meet a requirement, I made a table as a Fact and a Dimension. In the said table, I have a column which is varchar and contains unique values always. They can be said as 'A' or 'B' or 'C'. For all records in the said table, any of these three values can be present. But they are not from any lookup table but come from a third party system.

Using the AMO object, I am listing all the values are members. But the problem I am facing is, I am getting all values as members. As per my requirement, I must list only distinct of them

I am setting the DimensionAttribute object's Usage property to Key, OrderBy to OrderBy.Name and Type to Regular. What other property need to be set to list only the unique values as members?

I request a quick response to this query. I am desperately looking for the reply to solve the problem.

Thanks

Sekhar PC

Sorry Sekhar.

But the description of your problem is not very clear. Can you try to describe what you are trying to acheve.

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

|||

Hi,

Thank you for your reply.

In my cube, I have a fact table which has a column of varchar type. Assume that, the possible values are A,B,C,D.

While building the cube using the AMO and VB.Net, I am required to list them as members in the cube. Essentially, I am required to fetch the distinct values from that column and list as members.

How to that using AMO and VB.Net?

Thanks in advance

Sekhar

|||

You dont use AMO for retreiving data from Analysis Services. For that you use ADOMD.NET object model ( not the only option, but it might be easiest for you).

Here is some information on how to use ADOMD.NET

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

You can try and get a book on Analysis Services: Teo Lachev's "Applied Microsoft Analysis Services 2005.. " is good one you can use.

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

AMO Question - Distinct members in Cube

Hi,

How to filter members in a dimension.

To meet a requirement, I made a table as a Fact and a Dimension. In the said table, I have a column which is varchar and contains unique values always. They can be said as 'A' or 'B' or 'C'. For all records in the said table, any of these three values can be present. But they are not from any lookup table but come from a third party system.

Using the AMO object, I am listing all the values are members. But the problem I am facing is, I am getting all values as members. As per my requirement, I must list only distinct of them

I am setting the DimensionAttribute object's Usage property to Key, OrderBy to OrderBy.Name and Type to Regular. What other property need to be set to list only the unique values as members?

I request a quick response to this query. I am desperately looking for the reply to solve the problem.

Thanks

Sekhar PC

Sorry Sekhar.

But the description of your problem is not very clear. Can you try to describe what you are trying to acheve.

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

|||

Hi,

Thank you for your reply.

In my cube, I have a fact table which has a column of varchar type. Assume that, the possible values are A,B,C,D.

While building the cube using the AMO and VB.Net, I am required to list them as members in the cube. Essentially, I am required to fetch the distinct values from that column and list as members.

How to that using AMO and VB.Net?

Thanks in advance

Sekhar

|||

You dont use AMO for retreiving data from Analysis Services. For that you use ADOMD.NET object model ( not the only option, but it might be easiest for you).

Here is some information on how to use ADOMD.NET

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

You can try and get a book on Analysis Services: Teo Lachev's "Applied Microsoft Analysis Services 2005.. " is good one you can use.

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

AMO and ProcessAdd for dimensions

Is there any way to process a dimension using ProcessAdd using AMO without using xmla? We currently have an app for SQL2000 that processes dimensions using DSO - ProcessUpdate that a colleague insists can just be changed to AMO - ProcessAdd but I am not getting the impression that it is as simple as that.

A quick yes or no would be great!

Thanks

Hi,

You can do it, but this only add the new records to your dimension.

If your dimension data has updated records they will not be updated in SSAS dimension.

You can check this link

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

Regards,

Raul

|||

Thanks for the response. However, I have read that link previously and the examples all show that xmla must be used. I was wondering something similar to this post:

http://forums.microsoft.com/TechNet/ShowPost.aspx?PostID=2147144&SiteID=17

rprs wrote:

Hi,

You can do it, but this only add the new records to your dimension.

If your dimension data has updated records they will not be updated in SSAS dimension.

You can check this link

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

Regards,

Raul

AMO - Dimension Size?

Hello Folks,

I've started to write an AMO application to get information relevant to DBA's out of our SSAS databases.

I've successfully got the estimated sizes for Cubes, MeasureGroups and individual partitions nicely listed.

However, i'm having trouble because i can't seem to find a property of the Dimension object that will give me the size.

The reason i'm doing this is because i've read that you need to watch the size (and memory consumption) of these babies.

How do i do this in AMO? Or any other means!

Many thanks.

Kyle (DBA)

First, if you aren't already using the AMO browser, then you should start. It was designed for this purpose. Look for it in the Samples, by default located at:

C:\Program Files\Microsoft SQL Server\90\Samples\Analysis Services\Programmability\AMO\AmoBrowser

DImensions are buckets holding attributes. Dimensions themselves don't have sizes, attributes do. To figure out the # of members in a dimension, you need to navigate down to the key attribute of the dimension. The size of the *key* attribute is probably what you are looking for.

_-_-_ Dave

AMO - Dimension Composite Key

i'm developing an aplication in order to create a cube on the fly, and i'm confuse with something that i read at the Msdn's web site, the doubt that i have is about composite keys in Dimension Tables, after read this paragraph (extracted from - http://msdn2.microsoft.com/en-us/library/ms345091.aspx - "Creating Dropping and Finding Dimensions) .

" The primary key attribute of the dimension should be defined as

AttributeUsage.Key to make clear that this attribute is the key access

to the dimension "

It's like the Dimension Tables must have a unique atributte as PK right ?, but what happens if my Dim Tables have a compositeKey ?.

Is necessary to create a UNIQUE atribute as PK in the Dimension Tables ?

The Key attribute of a dimension can have a composite key - an example is the [Internet Sales Order Details] dimension in Adventure Works, with key columns of {SalesOrderNumber, SalesOrderLineNumber}. In terms of AMO, multiple DataItems could be added to an attribute's KeyColumns collection:

http://msdn2.microsoft.com/zh-cn/library/microsoft.analysisservices.dimensionattribute.keycolumns.aspx

>>

DimensionAttribute.KeyColumns Property

Gets the collection of key column definitions for DimensionAttribute.

...

>>

|||Thank you Deepak, I look the Adventure Works -Amo example and like you see its possible to have a composite key in a Dimension Table (it makes sense :) ).

I saw that they "rename" this composite key with ' Sales Order Key ', i suposse this is like create on the fly a unique PK that encapsulates the composite key, in order to be used later at the measureGroup creation, am i wrong ?.

Thanks for your fast reply!!!

cheers Lisber.

alternative for slowly changing dimension (SCD) object

Hi,

I think slowly changing dimension object is not a good choice to update my dimension. It's running slower than I expected. my dimension records has surrogate keys from a control table that SCD is looking up whenever it encounters a new record.

Any alternative I can use?

cherriesh wrote:

Hi,

I think slowly changing dimension object is not a good choice to update my dimension. It's running slower than I expected. my dimension records has surrogate keys from a control table that SCD is looking up whenever it encounters a new record.

Any alternative I can use?

Most people use the techniques described here:

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

-Jamie

|||

Hi Jamie,

I tried to use your example with the lookup to check if record is existing or not. If the record is already existing and has changed, then that means that I have to use the oledb command to update my table? based on the forums i read, this object runs slow. do i have any alternative for this?

http://blogs.conchango.com/jamiethomson/archive/2006/09/12/SSIS_3A00_-Checking-if-a-row-exists-and-if-it-does_2C00_-has-it-changed.aspx

cherriesh|||

See my blog...

there is a post to populate dimensions... different from others...

Regards!

|||

Hi Pedro,

Which one in your blog? can you post the url.

thanks a lot!

cherriesh

|||

cherriesh wrote:

Hi Jamie,

I tried to use your example with the lookup to check if record is existing or not. If the record is already existing and has changed, then that means that I have to use the oledb command to update my table? based on the forums i read, this object runs slow. do i have any alternative for this?

http://blogs.conchango.com/jamiethomson/archive/2006/09/12/SSIS_3A00_-Checking-if-a-row-exists-and-if-it-does_2C00_-has-it-changed.aspx

cherriesh

Cherriesh,

As part of the thread that Jamie posted earlier, there is a discussion on alternatives -- namely loading your updates to a staging table that you later use an Execute SQL task to perform the batch update. Please read through that entire thread.

|||

http://pedrocgd.blogspot.com/2007/05/ssis-populating-dimension_28.html

Helped?

Regards

|||your problem is resolved?!

alternative for slowly changing dimension (SCD) object

Hi,

I think slowly changing dimension object is not a good choice to update my dimension. It's running slower than I expected. my dimension records has surrogate keys from a control table that SCD is looking up whenever it encounters a new record.

Any alternative I can use?

cherriesh wrote:

Hi,

I think slowly changing dimension object is not a good choice to update my dimension. It's running slower than I expected. my dimension records has surrogate keys from a control table that SCD is looking up whenever it encounters a new record.

Any alternative I can use?

Most people use the techniques described here:

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

-Jamie

|||

Hi Jamie,

I tried to use your example with the lookup to check if record is existing or not. If the record is already existing and has changed, then that means that I have to use the oledb command to update my table? based on the forums i read, this object runs slow. do i have any alternative for this?

http://blogs.conchango.com/jamiethomson/archive/2006/09/12/SSIS_3A00_-Checking-if-a-row-exists-and-if-it-does_2C00_-has-it-changed.aspx

cherriesh|||

See my blog...

there is a post to populate dimensions... different from others...

Regards!

|||

Hi Pedro,

Which one in your blog? can you post the url.

thanks a lot!

cherriesh

|||

cherriesh wrote:

Hi Jamie,

I tried to use your example with the lookup to check if record is existing or not. If the record is already existing and has changed, then that means that I have to use the oledb command to update my table? based on the forums i read, this object runs slow. do i have any alternative for this?

http://blogs.conchango.com/jamiethomson/archive/2006/09/12/SSIS_3A00_-Checking-if-a-row-exists-and-if-it-does_2C00_-has-it-changed.aspx

cherriesh

Cherriesh,

As part of the thread that Jamie posted earlier, there is a discussion on alternatives -- namely loading your updates to a staging table that you later use an Execute SQL task to perform the batch update. Please read through that entire thread.

|||

http://pedrocgd.blogspot.com/2007/05/ssis-populating-dimension_28.html

Helped?

Regards

|||your problem is resolved?!

alternative for slowly changing dimension (SCD) object

Hi,

I think slowly changing dimension object is not a good choice to update my dimension. It's running slower than I expected. my dimension records has surrogate keys from a control table that SCD is looking up whenever it encounters a new record.

Any alternative I can use?

cherriesh wrote:

Hi,

I think slowly changing dimension object is not a good choice to update my dimension. It's running slower than I expected. my dimension records has surrogate keys from a control table that SCD is looking up whenever it encounters a new record.

Any alternative I can use?

Most people use the techniques described here:

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

-Jamie

|||

Hi Jamie,

I tried to use your example with the lookup to check if record is existing or not. If the record is already existing and has changed, then that means that I have to use the oledb command to update my table? based on the forums i read, this object runs slow. do i have any alternative for this?

http://blogs.conchango.com/jamiethomson/archive/2006/09/12/SSIS_3A00_-Checking-if-a-row-exists-and-if-it-does_2C00_-has-it-changed.aspx

cherriesh|||

See my blog...

there is a post to populate dimensions... different from others...

Regards!

|||

Hi Pedro,

Which one in your blog? can you post the url.

thanks a lot!

cherriesh

|||

cherriesh wrote:

Hi Jamie,

I tried to use your example with the lookup to check if record is existing or not. If the record is already existing and has changed, then that means that I have to use the oledb command to update my table? based on the forums i read, this object runs slow. do i have any alternative for this?

http://blogs.conchango.com/jamiethomson/archive/2006/09/12/SSIS_3A00_-Checking-if-a-row-exists-and-if-it-does_2C00_-has-it-changed.aspx

cherriesh

Cherriesh,

As part of the thread that Jamie posted earlier, there is a discussion on alternatives -- namely loading your updates to a staging table that you later use an Execute SQL task to perform the batch update. Please read through that entire thread.

|||

http://pedrocgd.blogspot.com/2007/05/ssis-populating-dimension_28.html

Helped?

Regards

|||your problem is resolved?!