Aggregation Issue

I have a cube that I designed aggregation with 12% performance in MOLAP storage mode. However, when I ran query it read from partition not from aggregation.

How can I change so that the query read from aggregation?

Thanks in advance,
A. Imamuddin

Hi Ashari

1. Ensure that you have designed hierarchies on your dimensions even though they seem to be unnecessary. I found that aggregations are created when these exist

2. I find that creating aggregations manually by editing the XMLA for the measure group works better. You do this by scripting the measure group in the SQL Server Man Studio and add/edit your aggregations.

Let me know if this helps?

Thanks

John

|||Hi John,

Thanks for your replay. I would like to inform you, I do not applied point 1 because I have already had hierarchies. I have applied point 2, but after processing the partition, the query still read from partition. FYI, I also design aggregation using Usage Based Optimization Wizard.

Thanks,
A. Imamuddin|||

It's likely that, even after usage-based optimisation, you still haven't build any aggregations useful for your query. Rather than change your query, to make sure you're building the right aggregations take a look at:
http://cwebbbi.spaces.live.com/blog/cns!7B84B0F2C239489A!907.entry

HTH,

Chris

Aggregation Issue

I have a cube that I designed aggregation with 12% performance in MOLAP storage mode. However, when I ran query it read from partition not from aggregation.

How can I change so that the query read from aggregation?

Thanks in advance,
A. Imamuddin

Hi Ashari

1. Ensure that you have designed hierarchies on your dimensions even though they seem to be unnecessary. I found that aggregations are created when these exist

2. I find that creating aggregations manually by editing the XMLA for the measure group works better. You do this by scripting the measure group in the SQL Server Man Studio and add/edit your aggregations.

Let me know if this helps?

Thanks

John

|||Hi John,

Thanks for your replay. I would like to inform you, I do not applied point 1 because I have already had hierarchies. I have applied point 2, but after processing the partition, the query still read from partition. FYI, I also design aggregation using Usage Based Optimization Wizard.

Thanks,
A. Imamuddin|||

It's likely that, even after usage-based optimisation, you still haven't build any aggregations useful for your query. Rather than change your query, to make sure you're building the right aggregations take a look at:
http://cwebbbi.spaces.live.com/blog/cns!7B84B0F2C239489A!907.entry

HTH,

Chris

After doing in-place upgrade from 2000 to 2005, compatibility mode is still 8.0

After performing an in-place upgrade from SQL 2000 to SQL 2005, the
entire instance is still 8.0. I want it to be 9.0, but don't know how
to do that.
Next to the server name under object explorer in SQL Server Management
Studio, it says 8.0.760.
If I try doing sp_dbcmptlevel to change to 9.0, it says "Valid values
of database compatibility level are 60, 65, 70, or 80", even though
I'm using Mangement Studio on the same machine.
Is there something that I missed when doing the in-place upgrade that
prevents my instance of SQL (the default instance) from being upgraded
to 9.0? How do I upgrade the instance of SQL to 9.0?
Any help is appreciated. Thanks.
<cobra@.tomgreen.com> wrote in message
news:1176253434.316158.302770@.n76g2000hsh.googlegr oups.com...
> After performing an in-place upgrade from SQL 2000 to SQL 2005, the
> entire instance is still 8.0. I want it to be 9.0, but don't know how
> to do that.
> Next to the server name under object explorer in SQL Server Management
> Studio, it says 8.0.760.
> If I try doing sp_dbcmptlevel to change to 9.0, it says "Valid values
> of database compatibility level are 60, 65, 70, or 80", even though
> I'm using Mangement Studio on the same machine.
> Is there something that I missed when doing the in-place upgrade that
> prevents my instance of SQL (the default instance) from being upgraded
> to 9.0? How do I upgrade the instance of SQL to 9.0?
It should prompt you for which instance you want to upgrade. Sounds like
you picked the wrong one.
Or only uprgaded the tools and not the database engine?

> Any help is appreciated. Thanks.
>
Greg Moore
SQL Server DBA Consulting Remote and Onsite available!
Email: sql (at) greenms.com http://www.greenms.com/sqlserver.html
|||I know it's not an issue with the instance, because there was only one
instance to begin with. I suppose the database engine never got
upgraded.
I could run the setup.exe again from the CD, but what do I have to do
to perform a database engine upgrade on the existing instance?
Thanks again!
On Apr 10, 10:39 pm, "Greg D. Moore \(Strider\)"
<mooregr_deletet...@.greenms.com> wrote:
> It should prompt you for which instance you want to upgrade. Sounds like
> you picked the wrong one.
> Or only uprgaded the tools and not the database engine?
>
>
> --
> Greg Moore
> SQL Server DBA Consulting Remote and Onsite available!
> Email: sql (at) greenms.com http://www.greenms.com/sqlserver.html
|||> I could run the setup.exe again from the CD, but what do I have to do
> to perform a database engine upgrade on the existing instance?
Select to install a database engine, and when it ask for what instance name, you select the name of
your current 2000 instance.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
<cobra@.tomgreen.com> wrote in message news:1176319271.755939.173860@.d57g2000hsg.googlegr oups.com...
>I know it's not an issue with the instance, because there was only one
> instance to begin with. I suppose the database engine never got
> upgraded.
> I could run the setup.exe again from the CD, but what do I have to do
> to perform a database engine upgrade on the existing instance?
> Thanks again!
>
> On Apr 10, 10:39 pm, "Greg D. Moore \(Strider\)"
> <mooregr_deletet...@.greenms.com> wrote:
>
|||<cobra@.tomgreen.com> wrote in message
news:1176253434.316158.302770@.n76g2000hsh.googlegr oups.com...
> After performing an in-place upgrade from SQL 2000 to SQL 2005, the
> entire instance is still 8.0. I want it to be 9.0, but don't know how
> to do that.
> Next to the server name under object explorer in SQL Server Management
> Studio, it says 8.0.760.
> If I try doing sp_dbcmptlevel to change to 9.0, it says "Valid values
> of database compatibility level are 60, 65, 70, or 80", even though
> I'm using Mangement Studio on the same machine.
> Is there something that I missed when doing the in-place upgrade that
> prevents my instance of SQL (the default instance) from being upgraded
> to 9.0? How do I upgrade the instance of SQL to 9.0?
> Any help is appreciated. Thanks.
Hi
I had the same problem: there was only one instance (default) but it did not
upgrade.
It turned out the problem was that my installation of SQL 2000 didn't have
the necessary service packs applied in order to upgrade it successfully to
2005. After applying SP4 I reinstalled 2005 and this time it upgraded to
version 9 successfully.
My installation of 2000 was 8.00.194 i.e. the RTM version. I note yours is
SP3 so installating SP4 may be worth a whirl.
Andrew

After doing in-place upgrade from 2000 to 2005, compatibility mode is still 8.0

After performing an in-place upgrade from SQL 2000 to SQL 2005, the
entire instance is still 8.0. I want it to be 9.0, but don't know how
to do that.
Next to the server name under object explorer in SQL Server Management
Studio, it says 8.0.760.
If I try doing sp_dbcmptlevel to change to 9.0, it says "Valid values
of database compatibility level are 60, 65, 70, or 80", even though
I'm using Mangement Studio on the same machine.
Is there something that I missed when doing the in-place upgrade that
prevents my instance of SQL (the default instance) from being upgraded
to 9.0? How do I upgrade the instance of SQL to 9.0?
Any help is appreciated. Thanks.<cobra@.tomgreen.com> wrote in message
news:1176253434.316158.302770@.n76g2000hsh.googlegroups.com...
> After performing an in-place upgrade from SQL 2000 to SQL 2005, the
> entire instance is still 8.0. I want it to be 9.0, but don't know how
> to do that.
> Next to the server name under object explorer in SQL Server Management
> Studio, it says 8.0.760.
> If I try doing sp_dbcmptlevel to change to 9.0, it says "Valid values
> of database compatibility level are 60, 65, 70, or 80", even though
> I'm using Mangement Studio on the same machine.
> Is there something that I missed when doing the in-place upgrade that
> prevents my instance of SQL (the default instance) from being upgraded
> to 9.0? How do I upgrade the instance of SQL to 9.0?
It should prompt you for which instance you want to upgrade. Sounds like
you picked the wrong one.
Or only uprgaded the tools and not the database engine?

> Any help is appreciated. Thanks.
>
Greg Moore
SQL Server DBA Consulting Remote and Onsite available!
Email: sql (at) greenms.com http://www.greenms.com/sqlserver.html|||I know it's not an issue with the instance, because there was only one
instance to begin with. I suppose the database engine never got
upgraded.
I could run the setup.exe again from the CD, but what do I have to do
to perform a database engine upgrade on the existing instance?
Thanks again!
On Apr 10, 10:39 pm, "Greg D. Moore \(Strider\)"
<mooregr_deletet...@.greenms.com> wrote:[vbcol=seagreen]
> It should prompt you for which instance you want to upgrade. Sounds like
> you picked the wrong one.
> Or only uprgaded the tools and not the database engine?
>
>
> --
> Greg Moore
> SQL Server DBA Consulting Remote and Onsite available!
> Email: sql (at) greenms.com http://www.greenms.com/sqlserver.html[/vbco
l]|||> I could run the setup.exe again from the CD, but what do I have to do
> to perform a database engine upgrade on the existing instance?
Select to install a database engine, and when it ask for what instance name,
you select the name of
your current 2000 instance.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
<cobra@.tomgreen.com> wrote in message news:1176319271.755939.173860@.d57g2000hsg.googlegroups
.com...
>I know it's not an issue with the instance, because there was only one
> instance to begin with. I suppose the database engine never got
> upgraded.
> I could run the setup.exe again from the CD, but what do I have to do
> to perform a database engine upgrade on the existing instance?
> Thanks again!
>
> On Apr 10, 10:39 pm, "Greg D. Moore \(Strider\)"
> <mooregr_deletet...@.greenms.com> wrote:
>|||<cobra@.tomgreen.com> wrote in message
news:1176253434.316158.302770@.n76g2000hsh.googlegroups.com...
> After performing an in-place upgrade from SQL 2000 to SQL 2005, the
> entire instance is still 8.0. I want it to be 9.0, but don't know how
> to do that.
> Next to the server name under object explorer in SQL Server Management
> Studio, it says 8.0.760.
> If I try doing sp_dbcmptlevel to change to 9.0, it says "Valid values
> of database compatibility level are 60, 65, 70, or 80", even though
> I'm using Mangement Studio on the same machine.
> Is there something that I missed when doing the in-place upgrade that
> prevents my instance of SQL (the default instance) from being upgraded
> to 9.0? How do I upgrade the instance of SQL to 9.0?
> Any help is appreciated. Thanks.
Hi
I had the same problem: there was only one instance (default) but it did not
upgrade.
It turned out the problem was that my installation of SQL 2000 didn't have
the necessary service packs applied in order to upgrade it successfully to
2005. After applying SP4 I reinstalled 2005 and this time it upgraded to
version 9 successfully.
My installation of 2000 was 8.00.194 i.e. the RTM version. I note yours is
SP3 so installating SP4 may be worth a whirl.
Andrew

After doing in-place upgrade from 2000 to 2005, compatibility mode is still 8.0

After performing an in-place upgrade from SQL 2000 to SQL 2005, the
entire instance is still 8.0. I want it to be 9.0, but don't know how
to do that.
Next to the server name under object explorer in SQL Server Management
Studio, it says 8.0.760.
If I try doing sp_dbcmptlevel to change to 9.0, it says "Valid values
of database compatibility level are 60, 65, 70, or 80", even though
I'm using Mangement Studio on the same machine.
Is there something that I missed when doing the in-place upgrade that
prevents my instance of SQL (the default instance) from being upgraded
to 9.0? How do I upgrade the instance of SQL to 9.0?
Any help is appreciated. Thanks.<cobra@.tomgreen.com> wrote in message
news:1176253434.316158.302770@.n76g2000hsh.googlegroups.com...
> After performing an in-place upgrade from SQL 2000 to SQL 2005, the
> entire instance is still 8.0. I want it to be 9.0, but don't know how
> to do that.
> Next to the server name under object explorer in SQL Server Management
> Studio, it says 8.0.760.
> If I try doing sp_dbcmptlevel to change to 9.0, it says "Valid values
> of database compatibility level are 60, 65, 70, or 80", even though
> I'm using Mangement Studio on the same machine.
> Is there something that I missed when doing the in-place upgrade that
> prevents my instance of SQL (the default instance) from being upgraded
> to 9.0? How do I upgrade the instance of SQL to 9.0?
It should prompt you for which instance you want to upgrade. Sounds like
you picked the wrong one.
Or only uprgaded the tools and not the database engine?
> Any help is appreciated. Thanks.
>
Greg Moore
SQL Server DBA Consulting Remote and Onsite available!
Email: sql (at) greenms.com http://www.greenms.com/sqlserver.html|||I know it's not an issue with the instance, because there was only one
instance to begin with. I suppose the database engine never got
upgraded.
I could run the setup.exe again from the CD, but what do I have to do
to perform a database engine upgrade on the existing instance?
Thanks again!
On Apr 10, 10:39 pm, "Greg D. Moore \(Strider\)"
<mooregr_deletet...@.greenms.com> wrote:
> It should prompt you for which instance you want to upgrade. Sounds like
> you picked the wrong one.
> Or only uprgaded the tools and not the database engine?
>
> > Any help is appreciated. Thanks.
> --
> Greg Moore
> SQL Server DBA Consulting Remote and Onsite available!
> Email: sql (at) greenms.com http://www.greenms.com/sqlserver.html|||> I could run the setup.exe again from the CD, but what do I have to do
> to perform a database engine upgrade on the existing instance?
Select to install a database engine, and when it ask for what instance name, you select the name of
your current 2000 instance.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
<cobra@.tomgreen.com> wrote in message news:1176319271.755939.173860@.d57g2000hsg.googlegroups.com...
>I know it's not an issue with the instance, because there was only one
> instance to begin with. I suppose the database engine never got
> upgraded.
> I could run the setup.exe again from the CD, but what do I have to do
> to perform a database engine upgrade on the existing instance?
> Thanks again!
>
> On Apr 10, 10:39 pm, "Greg D. Moore \(Strider\)"
> <mooregr_deletet...@.greenms.com> wrote:
>> It should prompt you for which instance you want to upgrade. Sounds like
>> you picked the wrong one.
>> Or only uprgaded the tools and not the database engine?
>>
>> > Any help is appreciated. Thanks.
>> --
>> Greg Moore
>> SQL Server DBA Consulting Remote and Onsite available!
>> Email: sql (at) greenms.com http://www.greenms.com/sqlserver.html
>|||<cobra@.tomgreen.com> wrote in message
news:1176253434.316158.302770@.n76g2000hsh.googlegroups.com...
> After performing an in-place upgrade from SQL 2000 to SQL 2005, the
> entire instance is still 8.0. I want it to be 9.0, but don't know how
> to do that.
> Next to the server name under object explorer in SQL Server Management
> Studio, it says 8.0.760.
> If I try doing sp_dbcmptlevel to change to 9.0, it says "Valid values
> of database compatibility level are 60, 65, 70, or 80", even though
> I'm using Mangement Studio on the same machine.
> Is there something that I missed when doing the in-place upgrade that
> prevents my instance of SQL (the default instance) from being upgraded
> to 9.0? How do I upgrade the instance of SQL to 9.0?
> Any help is appreciated. Thanks.
Hi
I had the same problem: there was only one instance (default) but it did not
upgrade.
It turned out the problem was that my installation of SQL 2000 didn't have
the necessary service packs applied in order to upgrade it successfully to
2005. After applying SP4 I reinstalled 2005 and this time it upgraded to
version 9 successfully.
My installation of 2000 was 8.00.194 i.e. the RTM version. I note yours is
SP3 so installating SP4 may be worth a whirl.
Andrew

After attaching database it is in Read only mode

Hi,
I detached a database from Server1 and took MDF file to Server2.
After that I re-attached the database on Server1.
When I tried to attached database using EM, sp_attach_db and also
sp_Attach_single_file, i was getting error msg 1813.
So I detached database again from Server1 and used the LDF on Server2.
It recognizes and LDF file and attaches the database. However, the
database is in read-only mode. I can go to properties and uncheck
"Read-Only" but when I try to save the changes it gives me Error 5105
- Deactivation Error...
I don't have access to new MDF file after I detached it again.
Any help will be greatly appreciated.
Divyesh
Check the file properties for the mdf and ldf at the
operating system level and make sure the files themselves
are not set to read only for the file properties.
-Sue
On 1 Dec 2004 13:53:26 -0800, divyeshkhatri@.hotmail.com
(Divyesh) wrote:

>Hi,
>I detached a database from Server1 and took MDF file to Server2.
>After that I re-attached the database on Server1.
>When I tried to attached database using EM, sp_attach_db and also
>sp_Attach_single_file, i was getting error msg 1813.
>So I detached database again from Server1 and used the LDF on Server2.
> It recognizes and LDF file and attaches the database. However, the
>database is in read-only mode. I can go to properties and uncheck
>"Read-Only" but when I try to save the changes it gives me Error 5105
>- Deactivation Error...
>I don't have access to new MDF file after I detached it again.
>Any help will be greatly appreciated.
>Divyesh

After attaching database it is in Read only mode

Hi,
I detached a database from Server1 and took MDF file to Server2.
After that I re-attached the database on Server1.
When I tried to attached database using EM, sp_attach_db and also
sp_Attach_single_file, i was getting error msg 1813.
So I detached database again from Server1 and used the LDF on Server2.
It recognizes and LDF file and attaches the database. However, the
database is in read-only mode. I can go to properties and uncheck
"Read-Only" but when I try to save the changes it gives me Error 5105
- Deactivation Error...
I don't have access to new MDF file after I detached it again.
Any help will be greatly appreciated.
DivyeshCheck the file properties for the mdf and ldf at the
operating system level and make sure the files themselves
are not set to read only for the file properties.
-Sue
On 1 Dec 2004 13:53:26 -0800, divyeshkhatri@.hotmail.com
(Divyesh) wrote:

>Hi,
>I detached a database from Server1 and took MDF file to Server2.
>After that I re-attached the database on Server1.
>When I tried to attached database using EM, sp_attach_db and also
>sp_Attach_single_file, i was getting error msg 1813.
>So I detached database again from Server1 and used the LDF on Server2.
> It recognizes and LDF file and attaches the database. However, the
>database is in read-only mode. I can go to properties and uncheck
>"Read-Only" but when I try to save the changes it gives me Error 5105
>- Deactivation Error...
>I don't have access to new MDF file after I detached it again.
>Any help will be greatly appreciated.
>Divyesh

After attaching database it is in Read only mode

Hi,
I detached a database from Server1 and took MDF file to Server2.
After that I re-attached the database on Server1.
When I tried to attached database using EM, sp_attach_db and also
sp_Attach_single_file, i was getting error msg 1813.
So I detached database again from Server1 and used the LDF on Server2.
It recognizes and LDF file and attaches the database. However, the
database is in read-only mode. I can go to properties and uncheck
"Read-Only" but when I try to save the changes it gives me Error 5105
- Deactivation Error...
I don't have access to new MDF file after I detached it again.
Any help will be greatly appreciated.
DivyeshCheck the file properties for the mdf and ldf at the
operating system level and make sure the files themselves
are not set to read only for the file properties.
-Sue
On 1 Dec 2004 13:53:26 -0800, divyeshkhatri@.hotmail.com
(Divyesh) wrote:
>Hi,
>I detached a database from Server1 and took MDF file to Server2.
>After that I re-attached the database on Server1.
>When I tried to attached database using EM, sp_attach_db and also
>sp_Attach_single_file, i was getting error msg 1813.
>So I detached database again from Server1 and used the LDF on Server2.
> It recognizes and LDF file and attaches the database. However, the
>database is in read-only mode. I can go to properties and uncheck
>"Read-Only" but when I try to save the changes it gives me Error 5105
>- Deactivation Error...
>I don't have access to new MDF file after I detached it again.
>Any help will be greatly appreciated.
>Divyesh