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.

Alternative to sp_delete_Backuphistory

I have a SQL 2000 server that has 300 databases. There is a requirement tha
t
these databases have their logs backed up every 30 minutes. This creates a
huge number of entries in the backup/restore tables in the msdb database.
I know I can delete backup history with the sp_delete_backuphistory command.
However, this command takes forever. Even deleting 30 minutes worth of
backups takes 15 or more minutes. I find I am creating entries in the
backup/restore tables faster than I could possible delete them.
Is there an alteranate way to clear out the backup/restore tables? Could is
simply truncate the tables?
Thanks for any info.
LehrSJYes, I ran into the same problem when I discovered the backup tables in
msdb were HUGE... instead of running the sp, I just truncated. The only
thing you lose by doing this is the ability to use Enterprise Manager
of Mgmt Studio to do your restore, as it relies on the information in
those tables when suggesting which backups to apply. If you can afford
to lose this, no big deal. The information will just get repopulated on
your next backups.
Also, once the tables are empty, set up a nightly job to run the
sp_delete_backuphistory, keep as many days worth of history as you
specify.
LehrSJ wrote:
> I have a SQL 2000 server that has 300 databases. There is a requirement t
hat
> these databases have their logs backed up every 30 minutes. This creates
a
> huge number of entries in the backup/restore tables in the msdb database.
> I know I can delete backup history with the sp_delete_backuphistory comman
d.
> However, this command takes forever. Even deleting 30 minutes worth of
> backups takes 15 or more minutes. I find I am creating entries in the
> backup/restore tables faster than I could possible delete them.
> Is there an alteranate way to clear out the backup/restore tables? Could
is
> simply truncate the tables?
> Thanks for any info.
> --
> LehrSJ|||Yes, you can truncate the tables. See the system table map to see the
relationships btwn the backup tables as there are dependencies between
them. All you lose by truncating the tables is the ability to use EM or
Mgmt Studio to do a restore, as it relies on the information in those
tables when suggesting which backups to apply. If you can afford to
lose it, no big deal. The information just gets repopluated on your
next backup.
Meanwhile, you might want to set up a nightly job to run
sp_delete_backuphistory so the table growth is kept in check.
LehrSJ wrote:
> I have a SQL 2000 server that has 300 databases. There is a requirement t
hat
> these databases have their logs backed up every 30 minutes. This creates
a
> huge number of entries in the backup/restore tables in the msdb database.
> I know I can delete backup history with the sp_delete_backuphistory comman
d.
> However, this command takes forever. Even deleting 30 minutes worth of
> backups takes 15 or more minutes. I find I am creating entries in the
> backup/restore tables faster than I could possible delete them.
> Is there an alteranate way to clear out the backup/restore tables? Could
is
> simply truncate the tables?
> Thanks for any info.
> --
> LehrSJ|||LehrSJ wrote:
> I have a SQL 2000 server that has 300 databases. There is a requirement t
hat
> these databases have their logs backed up every 30 minutes. This creates
a
> huge number of entries in the backup/restore tables in the msdb database.
> I know I can delete backup history with the sp_delete_backuphistory comman
d.
> However, this command takes forever. Even deleting 30 minutes worth of
> backups takes 15 or more minutes. I find I am creating entries in the
> backup/restore tables faster than I could possible delete them.
> Is there an alteranate way to clear out the backup/restore tables? Could
is
> simply truncate the tables?
> Thanks for any info.
>
The problem is that there are no useful indexes on the backup history
tables. Create these indexes, then sp_delete_backuphistory will fly:
CREATE INDEX IDX_temp on restorefile ( restore_history_id )
CREATE INDEX IDX_temp on restorefilegroup ( restore_history_id )
CREATE INDEX IDX_temp on backupset ( backup_finish_date )
CREATE INDEX IDX_temp2 ON backupset ( media_set_id )
Tracy McKibben
MCDBA
http://www.realsqlguy.com|||Thank you for this information. I didn't see why I couldn't truncate but I
was worried that there might be something that I didn't know about. I
understand what I will lose the ability for Enterprise manager to easily do
restores.
--
LehrSJ
"tootsuite@.gmail.com" wrote:

> Yes, you can truncate the tables. See the system table map to see the
> relationships btwn the backup tables as there are dependencies between
> them. All you lose by truncating the tables is the ability to use EM or
> Mgmt Studio to do a restore, as it relies on the information in those
> tables when suggesting which backups to apply. If you can afford to
> lose it, no big deal. The information just gets repopluated on your
> next backup.
> Meanwhile, you might want to set up a nightly job to run
> sp_delete_backuphistory so the table growth is kept in check.
> LehrSJ wrote:
>

Alternative to sp_delete_Backuphistory

I have a SQL 2000 server that has 300 databases. There is a requirement that
these databases have their logs backed up every 30 minutes. This creates a
huge number of entries in the backup/restore tables in the msdb database.
I know I can delete backup history with the sp_delete_backuphistory command.
However, this command takes forever. Even deleting 30 minutes worth of
backups takes 15 or more minutes. I find I am creating entries in the
backup/restore tables faster than I could possible delete them.
Is there an alteranate way to clear out the backup/restore tables? Could is
simply truncate the tables?
Thanks for any info.
LehrSJ
Yes, I ran into the same problem when I discovered the backup tables in
msdb were HUGE... instead of running the sp, I just truncated. The only
thing you lose by doing this is the ability to use Enterprise Manager
of Mgmt Studio to do your restore, as it relies on the information in
those tables when suggesting which backups to apply. If you can afford
to lose this, no big deal. The information will just get repopulated on
your next backups.
Also, once the tables are empty, set up a nightly job to run the
sp_delete_backuphistory, keep as many days worth of history as you
specify.
LehrSJ wrote:
> I have a SQL 2000 server that has 300 databases. There is a requirement that
> these databases have their logs backed up every 30 minutes. This creates a
> huge number of entries in the backup/restore tables in the msdb database.
> I know I can delete backup history with the sp_delete_backuphistory command.
> However, this command takes forever. Even deleting 30 minutes worth of
> backups takes 15 or more minutes. I find I am creating entries in the
> backup/restore tables faster than I could possible delete them.
> Is there an alteranate way to clear out the backup/restore tables? Could is
> simply truncate the tables?
> Thanks for any info.
> --
> LehrSJ
|||Yes, you can truncate the tables. See the system table map to see the
relationships btwn the backup tables as there are dependencies between
them. All you lose by truncating the tables is the ability to use EM or
Mgmt Studio to do a restore, as it relies on the information in those
tables when suggesting which backups to apply. If you can afford to
lose it, no big deal. The information just gets repopluated on your
next backup.
Meanwhile, you might want to set up a nightly job to run
sp_delete_backuphistory so the table growth is kept in check.
LehrSJ wrote:
> I have a SQL 2000 server that has 300 databases. There is a requirement that
> these databases have their logs backed up every 30 minutes. This creates a
> huge number of entries in the backup/restore tables in the msdb database.
> I know I can delete backup history with the sp_delete_backuphistory command.
> However, this command takes forever. Even deleting 30 minutes worth of
> backups takes 15 or more minutes. I find I am creating entries in the
> backup/restore tables faster than I could possible delete them.
> Is there an alteranate way to clear out the backup/restore tables? Could is
> simply truncate the tables?
> Thanks for any info.
> --
> LehrSJ
|||LehrSJ wrote:
> I have a SQL 2000 server that has 300 databases. There is a requirement that
> these databases have their logs backed up every 30 minutes. This creates a
> huge number of entries in the backup/restore tables in the msdb database.
> I know I can delete backup history with the sp_delete_backuphistory command.
> However, this command takes forever. Even deleting 30 minutes worth of
> backups takes 15 or more minutes. I find I am creating entries in the
> backup/restore tables faster than I could possible delete them.
> Is there an alteranate way to clear out the backup/restore tables? Could is
> simply truncate the tables?
> Thanks for any info.
>
The problem is that there are no useful indexes on the backup history
tables. Create these indexes, then sp_delete_backuphistory will fly:
CREATE INDEX IDX_temp on restorefile ( restore_history_id )
CREATE INDEX IDX_temp on restorefilegroup ( restore_history_id )
CREATE INDEX IDX_temp on backupset ( backup_finish_date )
CREATE INDEX IDX_temp2 ON backupset ( media_set_id )
Tracy McKibben
MCDBA
http://www.realsqlguy.com
|||Thank you for this information. I didn't see why I couldn't truncate but I
was worried that there might be something that I didn't know about. I
understand what I will lose the ability for Enterprise manager to easily do
restores.
LehrSJ
"tootsuite@.gmail.com" wrote:

> Yes, you can truncate the tables. See the system table map to see the
> relationships btwn the backup tables as there are dependencies between
> them. All you lose by truncating the tables is the ability to use EM or
> Mgmt Studio to do a restore, as it relies on the information in those
> tables when suggesting which backups to apply. If you can afford to
> lose it, no big deal. The information just gets repopluated on your
> next backup.
> Meanwhile, you might want to set up a nightly job to run
> sp_delete_backuphistory so the table growth is kept in check.
> LehrSJ wrote:
>