Memory allocation for complex cluster setup

Greetings. Having some difficulty consolidating on the appropriate memory
setup for the following configuration:
2 node cluster, Windows 2003 Enterprise Edition SP1 with SQL 2000 Enterprise
Edition SP4. There are 6 virtual SQL servers installed. Generally we run
one (primary) virtual server all by itself on the first node, and the other 5
(secondary) all bunched up on the second node. The primary virtual server
has recently begun having problems with performance. We are hung up on a
debate over the proper memory configuration. Both nodes have 10.0 GB of RAM.
We already have /PAE set on both nodes. We intentionally have not set /3GB
switch in the past, because in the event of a node failure, all 6 SQL
instances will end up on one node, possibly using 12.0GB of RAM, and causing
it's own problems. The virtual SQL servers all actually max out at about 1.7
GB (at least I have never seen them any higher), I assume that is some SQL or
OS quirk that keeps it close to 2.0GB max.
Now we are at the point where the primary virtual servers performance, even
when it is sitting all by itself, is becoming an issue. The Microsoft
documentation says for 8GB to use /3GB and /PAE. For 16GB to use /PAE
without /3GB, because if you accidentally cut off OS RAM to 1GB, you will
cause problems for the /PAE when addressing 16GB. Also, I have seen some
Microsoft documents that say "Generally you will not need to use AWE and /3GB
at the same time." My understanding is that /3GB actually is sort of "false
advertising", in that it was intended for 4GB systems. It really just tells
the OS to only reserve 1GB for itself, and give the rest to applications
(generally 3GB).
So here are the actual questions:
If I use /PAE WITHOUT /3GB on a system with 10.0GB, and then turn on SQL AWE
and set the max SQL RAM to 8.0GB, is that any different than if I use /PAE
AND /3GB on the same system, turn on AWE and set the max SQL RAM to 8.0GB?
What if I do both scenarios with a max SQL RAM of 9.0GB? Since I only have
10.0GB, will SQL be refused the extra 1.0GB in the scenario without the /3GB
switch because 2.0GB are reserved for the OS?
And finally, in my real world case, SQL is only ever using that 1.7 GB right
now... Should I start by just adding the /3GB switch without turning on AWE
in SQL to see if can even find use for the extra 1.0GB available before going
over the top and assigning an extra 6.0 GB or 7.0 GB? Will SQL actually make
use of all that RAM?
I know that is mostly theoretical. Hoping an expert can share some
understanding with me on what is otherwise guesswork on my part.
- Dan
dbanick@.axentis.comThe /3GB really has nothing to do with AWE other than the fact you should
not turn it on when you are using 12GB or more. THis is because the OS
needs more memory to handle the PTE's and such with the more memory above
4GB it has to manage. 16GB is a hard cut off but 12 is my practical limit.
Your issue is not so much the /3GB but the AWE. If you turn on AWE you will
have effectively choked out the other instances if they fail over to that
machine. AWE memory in SQL2000 is not dynamic. That is one of the main
reasons why you need to set an upper limit with the MAX Memory setting. YOu
have to be extremely careful when using AWE in a cluster due to that fact.
In SQL2005 AWE can be dynamic and you don't have to worry as much about it.
--
Andrew J. Kelly SQL MVP
"Dan Banick" <Dan Banick@.discussions.microsoft.com> wrote in message
news:31AE3BA6-C00D-4435-A876-A2A4B3042ADB@.microsoft.com...
> Greetings. Having some difficulty consolidating on the appropriate memory
> setup for the following configuration:
> 2 node cluster, Windows 2003 Enterprise Edition SP1 with SQL 2000
> Enterprise
> Edition SP4. There are 6 virtual SQL servers installed. Generally we run
> one (primary) virtual server all by itself on the first node, and the
> other 5
> (secondary) all bunched up on the second node. The primary virtual server
> has recently begun having problems with performance. We are hung up on a
> debate over the proper memory configuration. Both nodes have 10.0 GB of
> RAM.
> We already have /PAE set on both nodes. We intentionally have not set
> /3GB
> switch in the past, because in the event of a node failure, all 6 SQL
> instances will end up on one node, possibly using 12.0GB of RAM, and
> causing
> it's own problems. The virtual SQL servers all actually max out at about
> 1.7
> GB (at least I have never seen them any higher), I assume that is some SQL
> or
> OS quirk that keeps it close to 2.0GB max.
> Now we are at the point where the primary virtual servers performance,
> even
> when it is sitting all by itself, is becoming an issue. The Microsoft
> documentation says for 8GB to use /3GB and /PAE. For 16GB to use /PAE
> without /3GB, because if you accidentally cut off OS RAM to 1GB, you will
> cause problems for the /PAE when addressing 16GB. Also, I have seen some
> Microsoft documents that say "Generally you will not need to use AWE and
> /3GB
> at the same time." My understanding is that /3GB actually is sort of
> "false
> advertising", in that it was intended for 4GB systems. It really just
> tells
> the OS to only reserve 1GB for itself, and give the rest to applications
> (generally 3GB).
> So here are the actual questions:
> If I use /PAE WITHOUT /3GB on a system with 10.0GB, and then turn on SQL
> AWE
> and set the max SQL RAM to 8.0GB, is that any different than if I use /PAE
> AND /3GB on the same system, turn on AWE and set the max SQL RAM to 8.0GB?
> What if I do both scenarios with a max SQL RAM of 9.0GB? Since I only
> have
> 10.0GB, will SQL be refused the extra 1.0GB in the scenario without the
> /3GB
> switch because 2.0GB are reserved for the OS?
> And finally, in my real world case, SQL is only ever using that 1.7 GB
> right
> now... Should I start by just adding the /3GB switch without turning on
> AWE
> in SQL to see if can even find use for the extra 1.0GB available before
> going
> over the top and assigning an extra 6.0 GB or 7.0 GB? Will SQL actually
> make
> use of all that RAM?
> I know that is mostly theoretical. Hoping an expert can share some
> understanding with me on what is otherwise guesswork on my part.
> - Dan
> dbanick@.axentis.com

Memory allocation for complex cluster setup

Greetings. Having some difficulty consolidating on the appropriate memory
setup for the following configuration:
2 node cluster, Windows 2003 Enterprise Edition SP1 with SQL 2000 Enterprise
Edition SP4. There are 6 virtual SQL servers installed. Generally we run
one (primary) virtual server all by itself on the first node, and the other
5
(secondary) all bunched up on the second node. The primary virtual server
has recently begun having problems with performance. We are hung up on a
debate over the proper memory configuration. Both nodes have 10.0 GB of RAM
.
We already have /PAE set on both nodes. We intentionally have not set /3GB
switch in the past, because in the event of a node failure, all 6 SQL
instances will end up on one node, possibly using 12.0GB of RAM, and causing
it's own problems. The virtual SQL servers all actually max out at about 1.
7
GB (at least I have never seen them any higher), I assume that is some SQL o
r
OS quirk that keeps it close to 2.0GB max.
Now we are at the point where the primary virtual servers performance, even
when it is sitting all by itself, is becoming an issue. The Microsoft
documentation says for 8GB to use /3GB and /PAE. For 16GB to use /PAE
without /3GB, because if you accidentally cut off OS RAM to 1GB, you will
cause problems for the /PAE when addressing 16GB. Also, I have seen some
Microsoft documents that say "Generally you will not need to use AWE and /3G
B
at the same time." My understanding is that /3GB actually is sort of "false
advertising", in that it was intended for 4GB systems. It really just tells
the OS to only reserve 1GB for itself, and give the rest to applications
(generally 3GB).
So here are the actual questions:
If I use /PAE WITHOUT /3GB on a system with 10.0GB, and then turn on SQL AWE
and set the max SQL RAM to 8.0GB, is that any different than if I use /PAE
AND /3GB on the same system, turn on AWE and set the max SQL RAM to 8.0GB?
What if I do both scenarios with a max SQL RAM of 9.0GB? Since I only have
10.0GB, will SQL be refused the extra 1.0GB in the scenario without the /3GB
switch because 2.0GB are reserved for the OS?
And finally, in my real world case, SQL is only ever using that 1.7 GB right
now... Should I start by just adding the /3GB switch without turning on AWE
in SQL to see if can even find use for the extra 1.0GB available before goin
g
over the top and assigning an extra 6.0 GB or 7.0 GB? Will SQL actually mak
e
use of all that RAM?
I know that is mostly theoretical. Hoping an expert can share some
understanding with me on what is otherwise guesswork on my part.
- Dan
dbanick@.axentis.comThe /3GB really has nothing to do with AWE other than the fact you should
not turn it on when you are using 12GB or more. THis is because the OS
needs more memory to handle the PTE's and such with the more memory above
4GB it has to manage. 16GB is a hard cut off but 12 is my practical limit.
Your issue is not so much the /3GB but the AWE. If you turn on AWE you will
have effectively choked out the other instances if they fail over to that
machine. AWE memory in SQL2000 is not dynamic. That is one of the main
reasons why you need to set an upper limit with the MAX Memory setting. YOu
have to be extremely careful when using AWE in a cluster due to that fact.
In SQL2005 AWE can be dynamic and you don't have to worry as much about it.
Andrew J. Kelly SQL MVP
"Dan Banick" <Dan Banick@.discussions.microsoft.com> wrote in message
news:31AE3BA6-C00D-4435-A876-A2A4B3042ADB@.microsoft.com...
> Greetings. Having some difficulty consolidating on the appropriate memory
> setup for the following configuration:
> 2 node cluster, Windows 2003 Enterprise Edition SP1 with SQL 2000
> Enterprise
> Edition SP4. There are 6 virtual SQL servers installed. Generally we run
> one (primary) virtual server all by itself on the first node, and the
> other 5
> (secondary) all bunched up on the second node. The primary virtual server
> has recently begun having problems with performance. We are hung up on a
> debate over the proper memory configuration. Both nodes have 10.0 GB of
> RAM.
> We already have /PAE set on both nodes. We intentionally have not set
> /3GB
> switch in the past, because in the event of a node failure, all 6 SQL
> instances will end up on one node, possibly using 12.0GB of RAM, and
> causing
> it's own problems. The virtual SQL servers all actually max out at about
> 1.7
> GB (at least I have never seen them any higher), I assume that is some SQL
> or
> OS quirk that keeps it close to 2.0GB max.
> Now we are at the point where the primary virtual servers performance,
> even
> when it is sitting all by itself, is becoming an issue. The Microsoft
> documentation says for 8GB to use /3GB and /PAE. For 16GB to use /PAE
> without /3GB, because if you accidentally cut off OS RAM to 1GB, you will
> cause problems for the /PAE when addressing 16GB. Also, I have seen some
> Microsoft documents that say "Generally you will not need to use AWE and
> /3GB
> at the same time." My understanding is that /3GB actually is sort of
> "false
> advertising", in that it was intended for 4GB systems. It really just
> tells
> the OS to only reserve 1GB for itself, and give the rest to applications
> (generally 3GB).
> So here are the actual questions:
> If I use /PAE WITHOUT /3GB on a system with 10.0GB, and then turn on SQL
> AWE
> and set the max SQL RAM to 8.0GB, is that any different than if I use /PAE
> AND /3GB on the same system, turn on AWE and set the max SQL RAM to 8.0GB?
> What if I do both scenarios with a max SQL RAM of 9.0GB? Since I only
> have
> 10.0GB, will SQL be refused the extra 1.0GB in the scenario without the
> /3GB
> switch because 2.0GB are reserved for the OS?
> And finally, in my real world case, SQL is only ever using that 1.7 GB
> right
> now... Should I start by just adding the /3GB switch without turning on
> AWE
> in SQL to see if can even find use for the extra 1.0GB available before
> going
> over the top and assigning an extra 6.0 GB or 7.0 GB? Will SQL actually
> make
> use of all that RAM?
> I know that is mostly theoretical. Hoping an expert can share some
> understanding with me on what is otherwise guesswork on my part.
> - Dan
> dbanick@.axentis.comsql

Memory - Windows 2k3 Ent Ed, SQL 2k Ent Ed Version 2040

This is my environment:
Memory - Windows 2k3 Ent Ed, SQL 2k Ent Ed Version 2040
Have a total of 8 GB of memory.
Should I do the following ?
1. /3GB
2. /PAE
3. Enable AWE in SQL Server
Thanks,
RangaHello,
To make use of the extended memory, please modify the Boot.ini and enable
the AWE option. Make sure that apart from these you need to
make the SQL Server memory parameter static.
Take a look into below URL:-
http://support.microsoft.com/kb/274750
Thanks
Hari
"Ranga" <Ranga@.discussions.microsoft.com> wrote in message
news:617459F8-8E93-4687-AF1A-4B7175D7E9AA@.microsoft.com...
> This is my environment:
> Memory - Windows 2k3 Ent Ed, SQL 2k Ent Ed Version 2040
> Have a total of 8 GB of memory.
> Should I do the following ?
> 1. /3GB
> 2. /PAE
> 3. Enable AWE in SQL Server
> Thanks,
> Ranga

Memory - Windows 2k3 Ent Ed, SQL 2k Ent Ed Version 2040

This is my environment:
Memory - Windows 2k3 Ent Ed, SQL 2k Ent Ed Version 2040
Have a total of 8 GB of memory.
Should I do the following ?
1. /3GB
2. /PAE
3. Enable AWE in SQL Server
Thanks,
RangaHello,
To make use of the extended memory, please modify the Boot.ini and enable
the AWE option. Make sure that apart from these you need to
make the SQL Server memory parameter static.
Take a look into below URL:-
http://support.microsoft.com/kb/274750
Thanks
Hari
"Ranga" <Ranga@.discussions.microsoft.com> wrote in message
news:617459F8-8E93-4687-AF1A-4B7175D7E9AA@.microsoft.com...
> This is my environment:
> Memory - Windows 2k3 Ent Ed, SQL 2k Ent Ed Version 2040
> Have a total of 8 GB of memory.
> Should I do the following ?
> 1. /3GB
> 2. /PAE
> 3. Enable AWE in SQL Server
> Thanks,
> Ranga

Memory - Windows 2k3 Ent Ed, SQL 2k Ent Ed Version 2040

This is my environment:
Memory - Windows 2k3 Ent Ed, SQL 2k Ent Ed Version 2040
Have a total of 8 GB of memory.
Should I do the following ?
1. /3GB
2. /PAE
3. Enable AWE in SQL Server
Thanks,
Ranga
Hello,
To make use of the extended memory, please modify the Boot.ini and enable
the AWE option. Make sure that apart from these you need to
make the SQL Server memory parameter static.
Take a look into below URL:-
http://support.microsoft.com/kb/274750
Thanks
Hari
"Ranga" <Ranga@.discussions.microsoft.com> wrote in message
news:617459F8-8E93-4687-AF1A-4B7175D7E9AA@.microsoft.com...
> This is my environment:
> Memory - Windows 2k3 Ent Ed, SQL 2k Ent Ed Version 2040
> Have a total of 8 GB of memory.
> Should I do the following ?
> 1. /3GB
> 2. /PAE
> 3. Enable AWE in SQL Server
> Thanks,
> Ranga
sql

MELL 2.5 Reports - Cannot Connect to a MELL database

Although the MELL content seems to be working properly, I'm receiving the
following error when attempting to load MELL reports at
http://<servername>/mell/eng/reports/
Could not connect to a MELL database
Microsoft® eLearning Library - Reports Viewer cannot query a MELL database.
----
This is due to the following:
The DSN pointing to the SQL server is missing from the web server.
The DSN on the web server contains incorrect logon credentials.
The SQL server being referenced does not contain a MELL database.
The SQL services on the SQL server being referenced are not started.
The SQL server being referenced is not available due to network outage or
other condition.
----
Please contact your network or training administrator.
My environment consists of these two servers:
[MellServer]
Windows 2003 Server, SP1
IIS 6.0
[MellDatabaseServer]
Windows 2000 Server
SQL Server 2000
My reasons for believing the listed causes are incorrect are:
The DSN pointing to the SQL server is missing from the web server.
-- I have a DSN on the web server that's pointing to the correct SQL server
The DSN on the web server contains incorrect logon credentials.
-- The DSN tests successfully
The SQL server being referenced does not contain a MELL database.
-- A database named MELL does exist and has populated tables
The SQL services on the SQL server being referenced are not started.
-- SQL services are started... DSN tests okay
The SQL server being referenced is not available due to network outage or
other condition.
-- Again, DSN tests okay. Plus, I can see a connection coming from the web
server in my netstat -a output.
Any help is greatly appreciated.
--
--SK--Evidently my DSN file was not saving my password. If this is happening with
your MELL installation, check C:\Program Files\Common Files\ODBC\Data
Sources\mell25.dsn
Directly below the line beginning with "UID=", there should be a line that
reads:
PWD=yourpassword
If this line isn't present, add it.
Hope this helps someone else.
SK
--
--SK--
"ShaneK" wrote:
> Although the MELL content seems to be working properly, I'm receiving the
> following error when attempting to load MELL reports at
> http://<servername>/mell/eng/reports/
> Could not connect to a MELL database
> Microsoft® eLearning Library - Reports Viewer cannot query a MELL database.
> ----
> This is due to the following:
> The DSN pointing to the SQL server is missing from the web server.
> The DSN on the web server contains incorrect logon credentials.
> The SQL server being referenced does not contain a MELL database.
> The SQL services on the SQL server being referenced are not started.
> The SQL server being referenced is not available due to network outage or
> other condition.
> ----
> Please contact your network or training administrator.
> My environment consists of these two servers:
> [MellServer]
> Windows 2003 Server, SP1
> IIS 6.0
> [MellDatabaseServer]
> Windows 2000 Server
> SQL Server 2000
> My reasons for believing the listed causes are incorrect are:
> The DSN pointing to the SQL server is missing from the web server.
> -- I have a DSN on the web server that's pointing to the correct SQL server
> The DSN on the web server contains incorrect logon credentials.
> -- The DSN tests successfully
> The SQL server being referenced does not contain a MELL database.
> -- A database named MELL does exist and has populated tables
> The SQL services on the SQL server being referenced are not started.
> -- SQL services are started... DSN tests okay
> The SQL server being referenced is not available due to network outage or
> other condition.
> -- Again, DSN tests okay. Plus, I can see a connection coming from the web
> server in my netstat -a output.
> Any help is greatly appreciated.
> --
> --SK--|||Thank you so much for posting this! You've saved me a lot of heartache...
erpara
"ShaneK" wrote:
> Evidently my DSN file was not saving my password. If this is happening with
> your MELL installation, check C:\Program Files\Common Files\ODBC\Data
> Sources\mell25.dsn
> Directly below the line beginning with "UID=", there should be a line that
> reads:
> PWD=yourpassword
> If this line isn't present, add it.
> Hope this helps someone else.
> SK
> --
> --SK--
>
> "ShaneK" wrote:
> > Although the MELL content seems to be working properly, I'm receiving the
> > following error when attempting to load MELL reports at
> > http://<servername>/mell/eng/reports/
> >
> > Could not connect to a MELL database
> > Microsoft® eLearning Library - Reports Viewer cannot query a MELL database.
> > ----
> > This is due to the following:
> > The DSN pointing to the SQL server is missing from the web server.
> > The DSN on the web server contains incorrect logon credentials.
> > The SQL server being referenced does not contain a MELL database.
> > The SQL services on the SQL server being referenced are not started.
> > The SQL server being referenced is not available due to network outage or
> > other condition.
> > ----
> > Please contact your network or training administrator.
> >
> > My environment consists of these two servers:
> > [MellServer]
> > Windows 2003 Server, SP1
> > IIS 6.0
> >
> > [MellDatabaseServer]
> > Windows 2000 Server
> > SQL Server 2000
> >
> > My reasons for believing the listed causes are incorrect are:
> >
> > The DSN pointing to the SQL server is missing from the web server.
> > -- I have a DSN on the web server that's pointing to the correct SQL server
> >
> > The DSN on the web server contains incorrect logon credentials.
> > -- The DSN tests successfully
> >
> > The SQL server being referenced does not contain a MELL database.
> > -- A database named MELL does exist and has populated tables
> >
> > The SQL services on the SQL server being referenced are not started.
> > -- SQL services are started... DSN tests okay
> >
> > The SQL server being referenced is not available due to network outage or
> > other condition.
> > -- Again, DSN tests okay. Plus, I can see a connection coming from the web
> > server in my netstat -a output.
> >
> > Any help is greatly appreciated.
> > --
> > --SK--|||Glad to know this helped you!! Now, I'm only seeing IP addresses in the
reports where I'd prefer to see the names of my Active Directory users. I've
got a support case in progress with Microsoft and expect an answer this week.
If you'd like to know how that one turns out let me know and I'll post a
followup here.
--
--SK--
"Erpara" wrote:
> Thank you so much for posting this! You've saved me a lot of heartache...
> erpara
> "ShaneK" wrote:
> > Evidently my DSN file was not saving my password. If this is happening with
> > your MELL installation, check C:\Program Files\Common Files\ODBC\Data
> > Sources\mell25.dsn
> > Directly below the line beginning with "UID=", there should be a line that
> > reads:
> > PWD=yourpassword
> > If this line isn't present, add it.
> >
> > Hope this helps someone else.
> >
> > SK
> >
> > --
> > --SK--
> >
> >
> > "ShaneK" wrote:
> >
> > > Although the MELL content seems to be working properly, I'm receiving the
> > > following error when attempting to load MELL reports at
> > > http://<servername>/mell/eng/reports/
> > >
> > > Could not connect to a MELL database
> > > Microsoft® eLearning Library - Reports Viewer cannot query a MELL database.
> > > ----
> > > This is due to the following:
> > > The DSN pointing to the SQL server is missing from the web server.
> > > The DSN on the web server contains incorrect logon credentials.
> > > The SQL server being referenced does not contain a MELL database.
> > > The SQL services on the SQL server being referenced are not started.
> > > The SQL server being referenced is not available due to network outage or
> > > other condition.
> > > ----
> > > Please contact your network or training administrator.
> > >
> > > My environment consists of these two servers:
> > > [MellServer]
> > > Windows 2003 Server, SP1
> > > IIS 6.0
> > >
> > > [MellDatabaseServer]
> > > Windows 2000 Server
> > > SQL Server 2000
> > >
> > > My reasons for believing the listed causes are incorrect are:
> > >
> > > The DSN pointing to the SQL server is missing from the web server.
> > > -- I have a DSN on the web server that's pointing to the correct SQL server
> > >
> > > The DSN on the web server contains incorrect logon credentials.
> > > -- The DSN tests successfully
> > >
> > > The SQL server being referenced does not contain a MELL database.
> > > -- A database named MELL does exist and has populated tables
> > >
> > > The SQL services on the SQL server being referenced are not started.
> > > -- SQL services are started... DSN tests okay
> > >
> > > The SQL server being referenced is not available due to network outage or
> > > other condition.
> > > -- Again, DSN tests okay. Plus, I can see a connection coming from the web
> > > server in my netstat -a output.
> > >
> > > Any help is greatly appreciated.
> > > --
> > > --SK--|||Did you ever get a response to the IP address showing in the report ' I went
through some similar SQL problems and was able to resolve them, but havent
found a thing on the reports showing IP address' (granted finding anything on
MELL has been tough)
"ShaneK" wrote:
> Glad to know this helped you!! Now, I'm only seeing IP addresses in the
> reports where I'd prefer to see the names of my Active Directory users. I've
> got a support case in progress with Microsoft and expect an answer this week.
> If you'd like to know how that one turns out let me know and I'll post a
> followup here.
> --
> --SK--
>
> "Erpara" wrote:
> > Thank you so much for posting this! You've saved me a lot of heartache...
> >
> > erpara
> >
> > "ShaneK" wrote:
> >
> > > Evidently my DSN file was not saving my password. If this is happening with
> > > your MELL installation, check C:\Program Files\Common Files\ODBC\Data
> > > Sources\mell25.dsn
> > > Directly below the line beginning with "UID=", there should be a line that
> > > reads:
> > > PWD=yourpassword
> > > If this line isn't present, add it.
> > >
> > > Hope this helps someone else.
> > >
> > > SK
> > >
> > > --
> > > --SK--
> > >
> > >
> > > "ShaneK" wrote:
> > >
> > > > Although the MELL content seems to be working properly, I'm receiving the
> > > > following error when attempting to load MELL reports at
> > > > http://<servername>/mell/eng/reports/
> > > >
> > > > Could not connect to a MELL database
> > > > Microsoft® eLearning Library - Reports Viewer cannot query a MELL database.
> > > > ----
> > > > This is due to the following:
> > > > The DSN pointing to the SQL server is missing from the web server.
> > > > The DSN on the web server contains incorrect logon credentials.
> > > > The SQL server being referenced does not contain a MELL database.
> > > > The SQL services on the SQL server being referenced are not started.
> > > > The SQL server being referenced is not available due to network outage or
> > > > other condition.
> > > > ----
> > > > Please contact your network or training administrator.
> > > >
> > > > My environment consists of these two servers:
> > > > [MellServer]
> > > > Windows 2003 Server, SP1
> > > > IIS 6.0
> > > >
> > > > [MellDatabaseServer]
> > > > Windows 2000 Server
> > > > SQL Server 2000
> > > >
> > > > My reasons for believing the listed causes are incorrect are:
> > > >
> > > > The DSN pointing to the SQL server is missing from the web server.
> > > > -- I have a DSN on the web server that's pointing to the correct SQL server
> > > >
> > > > The DSN on the web server contains incorrect logon credentials.
> > > > -- The DSN tests successfully
> > > >
> > > > The SQL server being referenced does not contain a MELL database.
> > > > -- A database named MELL does exist and has populated tables
> > > >
> > > > The SQL services on the SQL server being referenced are not started.
> > > > -- SQL services are started... DSN tests okay
> > > >
> > > > The SQL server being referenced is not available due to network outage or
> > > > other condition.
> > > > -- Again, DSN tests okay. Plus, I can see a connection coming from the web
> > > > server in my netstat -a output.
> > > >
> > > > Any help is greatly appreciated.
> > > > --
> > > > --SK--

Mega / GIga

Hi Friends,
How can i transform the following query to return size in
Gigabytes ?
str(sum(convert(dec(15),v.size))* 8192 / 1048576,10,2)+'MB'
thx
Johnny
Divide by 1024?
Aaron Bertrand
SQL Server MVP
http://www.aspfaq.com/
"Johnny" <johnny_silvestre@.yahoo.de> wrote in message
news:942601c4339b$9a47f110$a401280a@.phx.gbl...
> Hi Friends,
> How can i transform the following query to return size in
> Gigabytes ?
> str(sum(convert(dec(15),v.size))* 8192 / 1048576,10,2)+'MB'
> thx
> Johnny
|||divide by 1024

Mega / GIga

Hi Friends,
How can i transform the following query to return size in
Gigabytes ?
str(sum(convert(dec(15),v.size))* 8192 / 1048576,10,2)+'MB'
thx
JohnnyDivide by 1024?
--
Aaron Bertrand
SQL Server MVP
http://www.aspfaq.com/
"Johnny" <johnny_silvestre@.yahoo.de> wrote in message
news:942601c4339b$9a47f110$a401280a@.phx.gbl...
> Hi Friends,
> How can i transform the following query to return size in
> Gigabytes ?
> str(sum(convert(dec(15),v.size))* 8192 / 1048576,10,2)+'MB'
> thx
> Johnny|||divide by 1024

Mega / GIga

Hi Friends,
How can i transform the following query to return size in
Gigabytes ?
str(sum(convert(dec(15),v.size))* 8192 / 1048576,10,2)+'MB'
thx
JohnnyDivide by 1024?
Aaron Bertrand
SQL Server MVP
http://www.aspfaq.com/
"Johnny" <johnny_silvestre@.yahoo.de> wrote in message
news:942601c4339b$9a47f110$a401280a@.phx.gbl...
> Hi Friends,
> How can i transform the following query to return size in
> Gigabytes ?
> str(sum(convert(dec(15),v.size))* 8192 / 1048576,10,2)+'MB'
> thx
> Johnny|||divide by 1024

Median Calc

I'm trying to use the following to calculate the median by year for the fiel
d
"labor_rate". This should calculate the median for any year where the count
is ODD. I haven't started on the "ELSE" part to calculate when the counts
are EVEN.
Anyway, the following doesn't work. I get an "incorrect syntax" error
message near line 3 and it doesn't like my order by clause either.
Any help would be greatly appreciated. Thanks.
SELECT [YYYY], median from
(SELECT [YYYY]
(SELECT TOP 1 [LABOR_RATE] FROM
(SELECT TOP 50 PERCENT [LABOR_RATE] , [YYYY]
FROM [dbo].[tbl_Data] WHERE [YYYY] = z.[YYYY]
order by labor_rate) sub
WHERE [YYYY] = z.[YYYY]
group by [YYYY], [LABOR_RATE]
having COUNT(*) % 2 <> 1
ORDER BY [LABOR_RATE] DESC))
as median
FROM [dbo].[tbl_Data] z
group by [YYYY], median
order by [YYYY]
CraigThere is a whole chapter on Medians in SQL FOR SMARTIES, with several
different methods.
If you have SQL-2005, you can use the row numbering to create an
ascending column and a descendng column with an OVER() clause.
The median is the row wher these two columns:
1) Are equal to each other (odd number of rows)
2) The average of the columns where they differ by one (even number of
rows) .
I have not tried this yet; I am haivng a XXXXX of time getting 2005 on
my machines for some reason.
--CELKO--
Please post DDL in a human-readable format and not a machine-generated
one. This way people do not have to guess what the keys, constraints,
DRI, datatypes, etc. in your schema are. Sample data is also a good
idea, along with clear specifications.
*** Sent via Developersdex http://www.examnotes.net ***|||Craig,
You're missing a comma after [YYYY] in line 2. That's why the
error is in line 3, where the ( is, because that's where the error
becomes apparent.
I didn't look further.
Steve Kass
Drew University
Craig wrote:

>I'm trying to use the following to calculate the median by year for the fie
ld
>"labor_rate". This should calculate the median for any year where the coun
t
>is ODD. I haven't started on the "ELSE" part to calculate when the counts
>are EVEN.
>Anyway, the following doesn't work. I get an "incorrect syntax" error
>message near line 3 and it doesn't like my order by clause either.
>Any help would be greatly appreciated. Thanks.
>
>SELECT [YYYY], median from
>(SELECT [YYYY]
>(SELECT TOP 1 [LABOR_RATE] FROM
> (SELECT TOP 50 PERCENT [LABOR_RATE] , [YYYY]
> FROM [dbo].[tbl_Data] WHERE [YYYY] = z.[YYYY]
> order by labor_rate) sub
>WHERE [YYYY] = z.[YYYY]
>group by [YYYY], [LABOR_RATE]
>having COUNT(*) % 2 <> 1
>ORDER BY [LABOR_RATE] DESC))
>as median
>FROM [dbo].[tbl_Data] z
>group by [YYYY], median
>order by [YYYY]
>
>|||Thanks. That cleared up the error. But one error is left: There is
incorrect syntax in the "From" line...can't figure it out.
Any help would be greatly appreciated. Thanks.
--
Craig
"Steve Kass" wrote:

> Craig,
> You're missing a comma after [YYYY] in line 2. That's why the
> error is in line 3, where the ( is, because that's where the error
> becomes apparent.
> I didn't look further.
> Steve Kass
> Drew University
> Craig wrote:
>
>|||...it's the last "From" line, FYI..
--
Craig
"Craig" wrote:
> Thanks. That cleared up the error. But one error is left: There is
> incorrect syntax in the "From" line...can't figure it out.
> Any help would be greatly appreciated. Thanks.
> --
> Craig
>
> "Steve Kass" wrote:
>|||Craig,
If you format the code more clearly, you'll see:
SELECT [YYYY], median from (
SELECT
[YYYY],
(
SELECT TOP 1
[LABOR_RATE]
FROM (
SELECT TOP 50 PERCENT
[LABOR_RATE],
[YYYY]
FROM [dbo].[tbl_Data]
WHERE [YYYY] = z.[YYYY]
order by labor_rate
) sub
WHERE [YYYY] = z.[YYYY]
group by [YYYY], [LABOR_RATE]
having COUNT(*) % 2 <> 1
ORDER BY [LABOR_RATE] DESC
)
) as median
FROM [dbo].[tbl_Data] z
group by [YYYY], median
order by [YYYY]
Note what "as median" applies to. It's the name of a table,
not the name of a column, as you probably intend. This
query currently says "select stuff from T from U", basically.
Get rid of the ) before "as median", and add it before "z",
and get into the habit of reading your code, not just moving around
symbols until it stops generating syntax errors.
SK
Craig wrote:

>...it's the last "From" line, FYI..
>

Measure Slicing Problem

Hi,

Please let me know whether the following requirement is possible in SQL Server Analysis Services 2005.

I have a fact table from which I have 4 measures M1, M2, M3 and M4. I have a dimension say D1,

Now I want that while browsing cube data the dimension D1 should slice M1 but it should not slice M2, M3 and M4.

What I feel is if we add the dimension to the fact table in Dimension Usage then it will slice all the measures coming from the fact table. If we remove the linkage of the dimension with the fact table in the Dimension Usage then the dimension will not slice any of the measures coming from that fact table.

The only solution that comes to my mind is by creating a duplicate of the fact table. But I dont want to do this as it would increase redundancy and make the DSV more complex.

Please let me know if I can do it in some other way.

Thanks in advance.

Regards,

Abhishek.

If M2-M4 are not related to D1 it sounds like they should be in a different fact table. However you should be able to achieve a similar outcome, buy overriding the context of D1 using a scope statement like the following.

SCOPE ({Measures.M2, Measures.M3, Measures.M4});

this = (D1.[All]);

END SCOPE;

Which pretty much says that for M2:M4, return the value of D1 at the top level, regardless of the current context of D1

|||

Hi,

Can you please tell me where should I write this scope statement.

Shoul I write it where I create the calculated member.

Thanks

Abhishek

|||You can only use scopes in the calculations section of the cube. You can either switch to script view and enter them or click the button on the tool bar that lets you add a new script command.|||

"The only solution that comes to my mind is by creating a duplicate of the fact table" - how about adding a Named Query on the fact table, which eliminates the field for D1 in the fact table? Then a new measure group could be created with this Named Query as the fact table, and measures M2, M3, M4. If the original fact table is like:

FT: <D1, D2, D3, D4, M1, M2, M3, M4>

then the Named Query could be like:

select D2, D3, D4, sum(M2) as M2, sum(M3) as M3, sum(M4) as M4

from FT

group by D2, D3, D4

Measure Slicing Problem

Hi,

Please let me know whether the following requirement is possible in SQL Server Analysis Services 2005.

I have a fact table from which I have 4 measures M1, M2, M3 and M4. I have a dimension say D1,

Now I want that while browsing cube data the dimension D1 should slice M1 but it should not slice M2, M3 and M4.

What I feel is if we add the dimension to the fact table in Dimension Usage then it will slice all the measures coming from the fact table. If we remove the linkage of the dimension with the fact table in the Dimension Usage then the dimension will not slice any of the measures coming from that fact table.

The only solution that comes to my mind is by creating a duplicate of the fact table. But I dont want to do this as it would increase redundancy and make the DSV more complex.

Please let me know if I can do it in some other way.

Thanks in advance.

Regards,

Abhishek.

If M2-M4 are not related to D1 it sounds like they should be in a different fact table. However you should be able to achieve a similar outcome, buy overriding the context of D1 using a scope statement like the following.

SCOPE ({Measures.M2, Measures.M3, Measures.M4});

this = (D1.[All]);

END SCOPE;

Which pretty much says that for M2:M4, return the value of D1 at the top level, regardless of the current context of D1

|||

Hi,

Can you please tell me where should I write this scope statement.

Shoul I write it where I create the calculated member.

Thanks

Abhishek

|||You can only use scopes in the calculations section of the cube. You can either switch to script view and enter them or click the button on the tool bar that lets you add a new script command.|||

"The only solution that comes to my mind is by creating a duplicate of the fact table" - how about adding a Named Query on the fact table, which eliminates the field for D1 in the fact table? Then a new measure group could be created with this Named Query as the fact table, and measures M2, M3, M4. If the original fact table is like:

FT: <D1, D2, D3, D4, M1, M2, M3, M4>

then the Named Query could be like:

select D2, D3, D4, sum(M2) as M2, sum(M3) as M3, sum(M4) as M4

from FT

group by D2, D3, D4

Measure Slicing Problem

Hi,

Please let me know whether the following requirement is possible in SQL Server Analysis Services 2005.

I have a fact table from which I have 4 measures M1, M2, M3 and M4. I have a dimension say D1,

Now I want that while browsing cube data the dimension D1 should slice M1 but it should not slice M2, M3 and M4.

What I feel is if we add the dimension to the fact table in Dimension Usage then it will slice all the measures coming from the fact table. If we remove the linkage of the dimension with the fact table in the Dimension Usage then the dimension will not slice any of the measures coming from that fact table.

The only solution that comes to my mind is by creating a duplicate of the fact table. But I dont want to do this as it would increase redundancy and make the DSV more complex.

Please let me know if I can do it in some other way.

Thanks in advance.

Regards,

Abhishek.

If M2-M4 are not related to D1 it sounds like they should be in a different fact table. However you should be able to achieve a similar outcome, buy overriding the context of D1 using a scope statement like the following.

SCOPE ({Measures.M2, Measures.M3, Measures.M4});

this = (D1.[All]);

END SCOPE;

Which pretty much says that for M2:M4, return the value of D1 at the top level, regardless of the current context of D1

|||

Hi,

Can you please tell me where should I write this scope statement.

Shoul I write it where I create the calculated member.

Thanks

Abhishek

|||You can only use scopes in the calculations section of the cube. You can either switch to script view and enter them or click the button on the tool bar that lets you add a new script command.|||

"The only solution that comes to my mind is by creating a duplicate of the fact table" - how about adding a Named Query on the fact table, which eliminates the field for D1 in the fact table? Then a new measure group could be created with this Named Query as the fact table, and measures M2, M3, M4. If the original fact table is like:

FT: <D1, D2, D3, D4, M1, M2, M3, M4>

then the Named Query could be like:

select D2, D3, D4, sum(M2) as M2, sum(M3) as M3, sum(M4) as M4

from FT

group by D2, D3, D4

Measure Dimension

Hi

What's the difference between the two following queries as the returne value of the second one is "#Error".
My aim is to count the number of customers that purchased the mountain product through Internet.
I've used the Adventure Works sample database.

WITH MEMBER [Measures].[Number Of Mountain Internet Sale] AS
DISTINCTCOUNT(EXISTS([Customer].[Customer].MEMBERS,
[Product].[Product Line].Mountain, "Internet Sales"))
SELECT [Measures].[Number Of Mountain Internet Sale] ON COLUMNS
FROM [Adventure Works]

WITH MEMBER [Measures].[Number Of Mountain Internet Sale] AS
DISTINCTCOUNT(EXISTS([Customer].[Customer].MEMBERS,
[Product].[Product Line].Mountain, [Measures].[Internet Sales Amount]))
SELECT [Measures].[Number Of Mountain Internet Sale] ON COLUMNS
FROM [Adventure Works]

Thanks in advance,
Amir

Hi Amir,

There are two flavours of the Exists function and I think you're getting confused between them here. BOL does a good job of explaining what they do:

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

As does Mosha:

http://sqljunkies.com/WebLog/mosha/archive/2005/10/11/mdx_functions_as2005.aspx
http://sqljunkies.com/WebLog/mosha/archive/2006/10/09/nonempty_exists_necj.aspx
(these links were down at the time of writing though)

Your first query works because it is asking for all the Customers that had entries in the "Internet Sales" measure group associated with the Product Line 'Mountain'; that third parameter is the name of a measure group. Your second query fails because you're passing a measure, Internet Sales Amount, as the third parameter whereas the function is expecting a string.

Anyway, you perhaps want use the NonEmpty function instead which is more likely to give you what you want (Exists would return the Customers who had entries in the fact table even if the measure values for that row were null); you don't need to do a DistinctCount either, a Count is sufficient. Here's an example :

WITH MEMBER [Measures].[Number Of Mountain Internet Sale] AS

COUNT(NONEMPTY([Customer].[Customer].MEMBERS,

([Product].[Product Line].Mountain,[Measures].[Internet Sales Amount] )))

SELECT [Measures].[Number Of Mountain Internet Sale] ON COLUMNS

FROM [Adventure Works]

HTH,

Chris

|||Chris,

Thanks for the reply. I appreciate it.
There is just one thing that I'm not sure of it in your sample using NONEMPTY. Shouldn't it be like this:

WITH MEMBER [Measures].[Number Of Mountain Internet Sale] AS

COUNT(NONEMPTY(CrossJoin([Customer].[Customer].MEMBERS, ([Product].[Product Line].Mountain) ,[Measures].[Internet Sales Amount] ))

SELECT [Measures].[Number Of Mountain Internet Sale] ON COLUMNS

FROM [Adventure Works]

I mean without CrossJoin it is like a set of sets.

Thanks,
Amir|||OOPS,

This is the correct one:

WITH MEMBER [Measures].[Number Of Mountain Internet Sale] AS

COUNT(NONEMPTY(CrossJoin([Customer].[Customer].MEMBERS, [Product].[Product Line].Mountain) ,[Measures].[Internet Sales Amount]

))

SELECT [Measures].[Number Of Mountain Internet Sale] ON COLUMNS

FROM [Adventure Works]|||

No, not necessarily, it's just a matter of style. Although your query returns the same results, you're counting a set of tuples consisting of combinations of Customer and Product, whereas my query is counting a set of Customers alone and finding the ones which aren't empty for Internet Sales Amount and Mountain.

Chris

MdxScript error using role security

The following error is displayed when trying to open a cube...

"An error occurred in the application: MdxScript(Quota Sales)(30,24) Then dimension '[Sales]' was not found in the cube when string, [Sales], was parsed"

I am using role security and am trying to hide a base measure [Measures].[Sales] along with two calculated measures that use the base measure. The cube does not have a [Sales] dimension.

I am using the Dimension Data tab within the role designer and denying all measures then selecting only the ones I want to appear in the cube. Those are written automatically into the "allowed member set" panel on the advanced tab. I have also tried allowing all members and then unchecking the Sales measure and specifying the [Measures].[Sales] measure and the two calculated measures in the "denied member set" panel on the advanced tab. I get the error either way.

I am using SSAS 2k5 and I receive the error when trying to open the cube using the ProClarity Professional client, although I don't think ProClarity is the issue.

Thanks in advance for any help.
Jay Hotchkiss

I've heard of some problems with security and MDX Scripts, although I can't find the relevant link at the moment...

...but, can you post the section of your MDX Script that's causing the error, ie everything around line 30? Is it by any chance something (like a calculated member definition, or named set) which refers to the Sales measure as
[Sales]
rather than using the full unique name of
[Measures].[Sales]
? If so, can you try using the full unique name in the MDX Script and seeing if you still get the same error?

Chris

|||

So that's what MDXScript is referring to, the calculated measure script! Yes, I have a number of calculations (time intellligence stuff) that are checking the Sales measure for NonEmpty behavior. I missed those. I'll do some work on the script and reply back here with the results. I think I can check another measure for NonEmpty instead of having to list all those calculated measure in the "denied set" for the role's security. Thanks very much. Jay

|||Thanks Christopher, I had a few calculated sets that were referring to my Sales measure. I appreciate the help. Jay|||For each dimension, you can set the MdxMissingMemberMode property in SSAS 2005. Perhaps this could solve your problem.

MdxScript error using role security

The following error is displayed when trying to open a cube...

"An error occurred in the application: MdxScript(Quota Sales)(30,24) Then dimension '[Sales]' was not found in the cube when string, [Sales], was parsed"

I am using role security and am trying to hide a base measure [Measures].[Sales] along with two calculated measures that use the base measure. The cube does not have a [Sales] dimension.

I am using the Dimension Data tab within the role designer and denying all measures then selecting only the ones I want to appear in the cube. Those are written automatically into the "allowed member set" panel on the advanced tab. I have also tried allowing all members and then unchecking the Sales measure and specifying the [Measures].[Sales] measure and the two calculated measures in the "denied member set" panel on the advanced tab. I get the error either way.

I am using SSAS 2k5 and I receive the error when trying to open the cube using the ProClarity Professional client, although I don't think ProClarity is the issue.

Thanks in advance for any help.
Jay Hotchkiss

I've heard of some problems with security and MDX Scripts, although I can't find the relevant link at the moment...

...but, can you post the section of your MDX Script that's causing the error, ie everything around line 30? Is it by any chance something (like a calculated member definition, or named set) which refers to the Sales measure as
[Sales]
rather than using the full unique name of
[Measures].[Sales]
? If so, can you try using the full unique name in the MDX Script and seeing if you still get the same error?

Chris

|||

So that's what MDXScript is referring to, the calculated measure script! Yes, I have a number of calculations (time intellligence stuff) that are checking the Sales measure for NonEmpty behavior. I missed those. I'll do some work on the script and reply back here with the results. I think I can check another measure for NonEmpty instead of having to list all those calculated measure in the "denied set" for the role's security. Thanks very much. Jay

|||Thanks Christopher, I had a few calculated sets that were referring to my Sales measure. I appreciate the help. Jay|||For each dimension, you can set the MdxMissingMemberMode property in SSAS 2005. Perhaps this could solve your problem.

MDX-Optimization

hi does anyone see anything that can be improved with the following MDX?
thanks ahead.
--This returns TOP 1000
--Brokers with their ZipCodes and Actual Sales, Actual Tickets, and Actual
Activities
--For a given Territory
--and date range
--and Sales Range
--and Activity Range
with
member [Measures].[zip] as '[Firm].[standard].currentmember.
properties("Zip")'
Member [Measures].[SalesAmt] as 'CoalesceEmpty([Measures].[S
ales Amt],0)'
Member [Measures].[TicketCnt] as 'CoalesceEmpty([Measures].[
Ticket Qty],0)'
Member [Measures].[ActivityCnt] as 'CoalesceEmpty([Measures].
1;Activity
Cnt],0)'
SET [Times] as '{ [Time].[Standard].[Month].&[1/1/2
004] :
[Time].[Standard].[Month].&[12/1/2004] }'
MEMBER [Measures].[Actual_Sales] AS
'SUM({[Measures].[zip]},SUM({[Times]},([Measures].
[SalesAmt])))'
MEMBER [Measures].[Actual_Ticket] AS
'SUM({[Measures].[zip]},SUM({[Times]},([Measures].
[TicketCnt])))'
MEMBER [Measures].[Actual_Activity] AS
'SUM({[Measures].[zip]},SUM({[Times]},([Measures].
[ActivityCnt])))'
SET [FilterSales] AS
'FILTER(
NONEMPTYCROSSJOIN
(
{DESCENDANTS([Firm].[Standard].[All Firms],[Firm].[
Standard].[Contact])},
{[Territory Current].[Standard].[Territory].[Ben Alcid]
},
{[Times]},
{[Company].[Standard].[Organization].[CompanyName]},
{[Transaction Type].[Standard].[Type].[Paid]},
1
),
([Measures].[Actual_Sales]) >= 1
and ( [Measures].[Actual_Sales]) <= 5000000
and ([Measures].[Actual_Activity]) >= 1
and ([Measures].[Actual_Activity]) <= 100
)'the second part of the select is here:
select {[Measures].[zip], [Measures].[Actual_Sales],
[Measures].[Actual_Ticket],[Measures].[Actual_Activity]} on
columns,
{
ORDER(
SUBSET ({[FilterSales]} ,0,1000 ),
[Measures].[zip], BASC
)
}
--DIMENSION PROPERTIES [Firm].[standard].properties("Zip")
on rows
From SalesActivity
"bc" wrote:

> hi does anyone see anything that can be improved with the following MDX?
> thanks ahead.
> --This returns TOP 1000
> --Brokers with their ZipCodes and Actual Sales, Actual Tickets, and Actual
> Activities
> --For a given Territory
> --and date range
> --and Sales Range
> --and Activity Range
> with
> member [Measures].[zip] as '[Firm].[standard].currentmembe
r.properties("Zip")'
> Member [Measures].[SalesAmt] as 'CoalesceEmpty([Measures].[
;Sales Amt],0)'
> Member [Measures].[TicketCnt] as 'CoalesceEmpty([Measures].
1;Ticket Qty],0)'
> Member [Measures].[ActivityCnt] as 'CoalesceEmpty([Measures].&
#91;Activity
> Cnt],0)'
> SET [Times] as '{ [Time].[Standard].[Month].&[1/1
/2004] :
> [Time].[Standard].[Month].&[12/1/2004] }'
> MEMBER [Measures].[Actual_Sales] AS
> 'SUM({[Measures].[zip]},SUM({[Times]},([Measures
].[SalesAmt])))'
> MEMBER [Measures].[Actual_Ticket] AS
> 'SUM({[Measures].[zip]},SUM({[Times]},([Measures
].[TicketCnt])))'
> MEMBER [Measures].[Actual_Activity] AS
> 'SUM({[Measures].[zip]},SUM({[Times]},([Measures
].[ActivityCnt])))'
> SET [FilterSales] AS
> 'FILTER(
> NONEMPTYCROSSJOIN
> (
> {DESCENDANTS([Firm].[Standard].[All Firms],[Firm].
[Standard].[Contact])},
> {[Territory Current].[Standard].[Territory].[Ben A
lcid] },
> {[Times]},
> {[Company].[Standard].[Organization].[CompanyName]
},
> {[Transaction Type].[Standard].[Type].[Paid]},
> 1
> ),
> ([Measures].[Actual_Sales]) >= 1
> and ( [Measures].[Actual_Sales]) <= 5000000
> and ([Measures].[Actual_Activity]) >= 1
> and ([Measures].[Actual_Activity]) <= 100
> )'
>|||Firstly, what is meant by "TOP 1000" - it looks like Subset() is
selecting the first 1000 from [FilterSales].
Secondly, some simplification seems possible - why are the
CoalesceEmpty() calculated measures needed?
[vbcol=seagreen]
--This returns TOP 1000
--Brokers with their ZipCodes and Actual Sales, Actual Tickets, and
Actual
Activities
--For a given Territory
--and date range
--and Sales Range
--and Activity Range
with
member [Measures].[zip] as
'[Firm].[standard].currentmemb_er.properties("Zip")'
SET [Times] as '{ [Time].[Standard].[Month].&[1/_1/
2004] :
[Time].[Standard].[Month].&[12_/1/2004] }'
MEMBER [Measures].[Actual_Sales] AS
'SUM({[_Times]},([Measures].[Sales Amt]_))'
MEMBER [Measures].[Actual_Ticket] AS
'SUM({[_Times]},([Measures].[Ticket Cnt_]))'
MEMBER [Measures].[Actual_Activity] AS
'SUM({[_Times]},([Measures].[Activity C_nt]))'
SET [FilterSales] AS
'FILTER(
NONEMPTYCROSSJOIN
(
{DESCENDANTS([Firm].[Standard]_.[All
Firms],[Firm].[Standard].[Cont_act])},
{[Territory Current].[Standard].[Territory_].[Ben Alcid
] },
{[Times]},
{[Company].[Standard].[Organiz_ation].[CompanyName]},
{[Transaction Type].[Standard].[Type].[Paid]_},
1
),
([Measures].[Actual_Sales]) >= 1
and ( [Measures].[Actual_Sales]) <= 5000000
and ([Measures].[Actual_Activity]) >= 1
and ([Measures].[Actual_Activity]) <= 100
)'
Select {[Measures].[zip], [Measures].[Actual_Sales],
[Measures].[Actual_Ticket],[Me_asures].[Actual_Activity]} on
columns,
{
ORDER(
SUBSET ({[FilterSales]} ,0,1000 ),
[Measures].[zip], BASC
)
}
--DIMENSION PROPERTIES [Firm].[standard].properties("_Zip")
on rows
From SalesActivity[vbcol=seagreen]
Also, if the [Times] set is large, an equivalent set using higher
aggregations can improve performance:
http://groups-beta.google.com/group...erver.olap/msg/
dacc5ced4823970a[vbcol=seagreen]
Newsgroups: microsoft.public.sqlserver.olap
From: "Chris Webb" <OnlyForPostingToNewsgro...@.hotmail.c_om>
Date: Fri, 31 Dec 2004 04:01:01 -0800
Subject: RE: Problem with MDX query
I don't think the query is hanging, I think it's just taking a very long
time
to complete! Summing up all those days in your date range is going to
take a
long time, plus I'll bet that your second query (because it doesn't
mention
the YearMonthDay dimension at all) probably hits aggregations whereas
your
first query doesn't.
Since this is a fairly common problem I've just made it the subject of
the
first entry of my new blog, which you can read here:
http://spaces.msn.com/members/_cwebbbi/Blog/cns!1pi7ETChsJ1un__2s41jm9I.
.
In your case, I think the approach of replacing the day members in your
day
range set with common ancestors could solve the problem.
HTH,
Chris[vbcol=seagreen]
- Deepak
Deepak Puri
Microsoft MVP - SQL Server
*** Sent via Developersdex http://www.codecomments.com ***
Don't just participate in USENET...get rewarded for it!|||hi thank you for responding, here is the query again and let me explain the
cube structure I have:
I have 2 cubes (Activities, Sales) and a virtual cube SalesActivity.
The Measures SalesAmt and TicketCnt came from the Sales Cube.
The Measure ActivityCnt came from Activity Cube.
These 3 are actual columns from the Fact tables which made up the cube
(Fact_Act and Fact_SLS).
The problem is the query seems to take a long time to run.
And yes you are right about the [Times] set. If I change to Year Level
instead of a lower level Month it is faster. But unfortunately I can not do
this because originally the requirement from the business is that the user
can choose any day range
so it was worst before when it was at the Day Level...since then I have
moved up to the Month Level...I can not go up to the Year Level because the
users want to see results across multiple months.
The CoalesceEmpty functions you see are there because since this is a
virtual cube I am referencing (SalesActivity) the numbers can be Empty so I
am just replacing it with zero where appropriate (because zero means
something here, example: we have sales but no activities for the criteria
selected or vice versa).
The other thing I want to mention is that I have the ZipCode as the member
properties of a dimension [Firm].[Standard] with 2 levels
Firm and Contact. A firm has many contacts and the zipcode is the zipcode
for the contacts. So I have 2 physical tables (Firm and Contact, one to man
y
relationship).
The Firm table is the source for the Firm Level and the Contact table is the
source for the Contact Level (the zipcode column is in the contact table).
Since the ContactID is the Leaf Level here, it is referenced in the 2 Fact
tables Fact_Act and Fact_SLS (it is a foreign key in the Fact tables).
Should I Create a new Location dimension
(State,County,City,ZipCode,Latitude,Long
itude) with ZipCode as the Lowest
Level and Latitude and Longitude as Dimension Properties and put the column
ZipCode inside the Fact Tables so that it can be referenced quickly? This is
because the query below returns a list of zipcodes that match the criteria
and then the user choose a particular zipcode to drill down to see further
detail on the facts...it is a mappoint application integrated with OLAP.
thank you for your help
--This returns TOP 1000
--Brokers with their ZipCodes and Actual Sales, Actual Tickets, and Actual
Activities
--For a given Territory
--and date range
--and Sales Range
--and Activity Range
with
member [Measures].[zip] as '[Firm].[standard].currentmember.
properties("Zip")'
Member [Measures].[SalesAmt] as 'CoalesceEmpty([Measures].[S
ales Amt],0)'
Member [Measures].[TicketCnt] as 'CoalesceEmpty([Measures].[
Ticket Qty],0)'
Member [Measures].[ActivityCnt] as 'CoalesceEmpty([Measures].
1;Activity
Cnt],0)'
SET [Times] as '{ [Time].[Standard].[Month].&[1/1/2
004] :
[Time].[Standard].[Month].&[12/1/2004] }'
MEMBER [Measures].[Actual_Sales] AS
'SUM({[Measures].[zip]},SUM({[Times]},([Measures].
[SalesAmt])))'
MEMBER [Measures].[Actual_Ticket] AS
'SUM({[Measures].[zip]},SUM({[Times]},([Measures].
[TicketCnt])))'
MEMBER [Measures].[Actual_Activity] AS
'SUM({[Measures].[zip]},SUM({[Times]},([Measures].
[ActivityCnt])))'
SET [FilterSales] AS
'FILTER(
NONEMPTYCROSSJOIN
(
{DESCENDANTS([Firm].[Standard].[All Firms],[Firm].[
Standard].[Contact])},
{[Territory Current].[Standard].[Territory].[Ben Alcid]
},
{[Times]},
{[Company].[Standard].[Organization].[CompanyName]},
{[Transaction Type].[Standard].[Type].[Paid]},
1
),
([Measures].[Actual_Sales]) >= 1
and ( [Measures].[Actual_Sales]) <= 5000000
and ([Measures].[Actual_Activity]) >= 1
and ([Measures].[Actual_Activity]) <= 100
)'
select {[Measures].[zip], [Measures].[Actual_Sales],
[Measures].[Actual_Ticket],[Measures].[Actual_Activity]} on
columns,
{
ORDER(
SUBSET ({[FilterSales]} ,0,1000 ),
[Measures].[zip], BASC
)
}
--DIMENSION PROPERTIES [Firm].[standard].properties("Zip")
on rows
From SalesActivity
"Deepak Puri" wrote:

> Firstly, what is meant by "TOP 1000" - it looks like Subset() is
> selecting the first 1000 from [FilterSales].
> Secondly, some simplification seems possible - why are the
> CoalesceEmpty() calculated measures needed?
>
> --This returns TOP 1000
> --Brokers with their ZipCodes and Actual Sales, Actual Tickets, and
> Actual
> Activities
> --For a given Territory
> --and date range
> --and Sales Range
> --and Activity Range
> with
> member [Measures].[zip] as
> '[Firm].[standard].currentmemb-er.properties("Zip")'
> SET [Times] as '{ [Time].[Standard].[Month].&[1/-
1/2004] :
> [Time].[Standard].[Month].&[12-/1/2004] }'
> MEMBER [Measures].[Actual_Sales] AS
> 'SUM({[-Times]},([Measures].[Sales Amt]-))'
> MEMBER [Measures].[Actual_Ticket] AS
> 'SUM({[-Times]},([Measures].[Ticket Cnt-]))'
> MEMBER [Measures].[Actual_Activity] AS
> 'SUM({[-Times]},([Measures].[Activity C-nt]))'
> SET [FilterSales] AS
> 'FILTER(
> NONEMPTYCROSSJOIN
> (
> {DESCENDANTS([Firm].[Standard]-.[All
> Firms],[Firm].[Standard].[Cont-act])},
> {[Territory Current].[Standard].[Territory-].[Ben
Alcid] },
> {[Times]},
> {[Company].[Standard].[Organiz-ation].[CompanyName
]},
> {[Transaction Type].[Standard].[Type].[Paid]-},
> 1
> ),
> ([Measures].[Actual_Sales]) >= 1
> and ( [Measures].[Actual_Sales]) <= 5000000
> and ([Measures].[Actual_Activity]) >= 1
> and ([Measures].[Actual_Activity]) <= 100
> )'
> Select {[Measures].[zip], [Measures].[Actual_Sales],
> [Measures].[Actual_Ticket],[Me-asures].[Actual_Activity]}
on columns,
> {
> ORDER(
> SUBSET ({[FilterSales]} ,0,1000 ),
> [Measures].[zip], BASC
> )
> }
> --DIMENSION PROPERTIES [Firm].[standard].properties("-Zip")
> on rows
> From SalesActivity
>
> Also, if the [Times] set is large, an equivalent set using higher
> aggregations can improve performance:
> http://groups-beta.google.com/group...erver.olap/msg/
> dacc5ced4823970a
> Newsgroups: microsoft.public.sqlserver.olap
> From: "Chris Webb" <OnlyForPostingToNewsgro...@.hotmail.c-om>
> Date: Fri, 31 Dec 2004 04:01:01 -0800
> Subject: RE: Problem with MDX query
> I don't think the query is hanging, I think it's just taking a very long
> time
> to complete! Summing up all those days in your date range is going to
> take a
> long time, plus I'll bet that your second query (because it doesn't
> mention
> the YearMonthDay dimension at all) probably hits aggregations whereas
> your
> first query doesn't.
> Since this is a fairly common problem I've just made it the subject of
> the
> first entry of my new blog, which you can read here:
> http://spaces.msn.com/members/-cwebbbi/Blog/cns!1pi7ETChsJ1un-_2s41jm9I.
> ..
> In your case, I think the approach of replacing the day members in your
> day
> range set with common ancestors could solve the problem.
> HTH,
> Chris
>
> - Deepak
> Deepak Puri
> Microsoft MVP - SQL Server
> *** Sent via Developersdex http://www.codecomments.com ***
> Don't just participate in USENET...get rewarded for it!
>|||Here are inline responses to your clarifications:
1)
----
If I change to Year Level
instead of a lower level Month it is faster. But unfortunately I can not
do
this because originally the requirement from the business is that the
user
can choose any day range
so it was worst before when it was at the Day Level...since then I have
moved up to the Month Level...I can not go up to the Year Level because
the
users want to see results across multiple months.[vbcol=seagreen]
The link that I provided in my post lets the user specify an arbitrary
time range at a lower level, then converts it into an optimized
combination of higher and lower members. So this should work in your
scenario, even at [Day] level.
----
2)
----
The CoalesceEmpty functions you see are there because since this is a
virtual cube I am referencing (SalesActivity) the numbers can be Empty
so I
am just replacing it with zero where appropriate (because zero means
something here, example: we have sales but no activities for the
criteria
selected or vice versa).[vbcol=seagreen]
But you don't need to do this for Sum() function, since it will
effectively treat empty as 0 - this could be faster.
----
3)
----
Should I Create a new Location dimension
(State,County,City,ZipCode,Latitude,Long
itude) with ZipCode as the
Lowest
Level and Latitude and Longitude as Dimension Properties and put the
column
ZipCode inside the Fact Tables so that it can be referenced quickly?[vbcol=seagreen]
Since "Zip" is already a Member Property, you could create a Virtual
Dimension, and compare its performance to adding a new dimension from a
ZipCode field in the fact table. But can multiple contacts have the same
ZipCode - in which case, do you still need to return the contacts along
with the zip codes? If this is so, then something like:
NonEmptyCrossJoin([ZipCode].[Zip].Members,
DESCENDANTS([Firm].[Standard].[All Firms],[Firm].[Standa
rd].[Contact]),
{[Territory Current].[Standard].[Territory].[Ben Alcid]
},
{[Times]},
{[Company].[Standard].[Organization].[CompanyName]},
{[Transaction Type].[Standard].[Type].[Paid]},
2) could be returned on rows, already ordered by Zip.
----
- Deepak
Deepak Puri
Microsoft MVP - SQL Server
*** Sent via Developersdex http://www.codecomments.com ***
Don't just participate in USENET...get rewarded for it!|||Hi Deepak,
"The link that I provided in my post lets the user specify an arbitrary
time range at a lower level, then converts it into an optimized
combination of higher and lower members. So this should work in your
scenario, even at [Day] level."
thank you for your response but I can't seem to get to the link you provided
.
can you put the code here or give me the link again?
Yes I do need to return contacts along with the zipcodes and yes one zipcode
can belong to multiple contacts. I will try a virtual dimension as you
suggested and let you know. I also tried partition the cube to different
year but the performance seems to be the same.
"Deepak Puri" wrote:

> Here are inline responses to your clarifications:
> 1)
> ----
> If I change to Year Level
> instead of a lower level Month it is faster. But unfortunately I can not
> do
> this because originally the requirement from the business is that the
> user
> can choose any day range
> so it was worst before when it was at the Day Level...since then I have
> moved up to the Month Level...I can not go up to the Year Level because
> the
> users want to see results across multiple months.
> The link that I provided in my post lets the user specify an arbitrary
> time range at a lower level, then converts it into an optimized
> combination of higher and lower members. So this should work in your
> scenario, even at [Day] level.
> ----
> 2)
> ----
> The CoalesceEmpty functions you see are there because since this is a
> virtual cube I am referencing (SalesActivity) the numbers can be Empty
> so I
> am just replacing it with zero where appropriate (because zero means
> something here, example: we have sales but no activities for the
> criteria
> selected or vice versa).
> But you don't need to do this for Sum() function, since it will
> effectively treat empty as 0 - this could be faster.
> ----
> 3)
> ----
> Should I Create a new Location dimension
> (State,County,City,ZipCode,Latitude,Long
itude) with ZipCode as the
> Lowest
> Level and Latitude and Longitude as Dimension Properties and put the
> column
> ZipCode inside the Fact Tables so that it can be referenced quickly?
> Since "Zip" is already a Member Property, you could create a Virtual
> Dimension, and compare its performance to adding a new dimension from a
> ZipCode field in the fact table. But can multiple contacts have the same
> ZipCode - in which case, do you still need to return the contacts along
> with the zip codes? If this is so, then something like:
> NonEmptyCrossJoin([ZipCode].[Zip].Members,
> DESCENDANTS([Firm].[Standard].[All Firms],[Firm].[Stan
dard].[Contact]),
> {[Territory Current].[Standard].[Territory].[Ben Alci
d]},
> {[Times]},
> {[Company].[Standard].[Organization].[CompanyName]},
> {[Transaction Type].[Standard].[Type].[Paid]},
> 2) could be returned on rows, already ordered by Zip.
> ----
>
> - Deepak
> Deepak Puri
> Microsoft MVP - SQL Server
> *** Sent via Developersdex http://www.codecomments.com ***
> Don't just participate in USENET...get rewarded for it!
>|||Here's a Google link to the thread where Chris Webb discussed the
possibility to optimize a time series, and I posted some sample MDX
code. You can refer to Chris's BI Blog (referenced in this thread) for
more details:
http://groups-beta.google.com/group...erver.olap/msg/
dacc5ced4823970a[vbcol=seagreen]
Newsgroups: microsoft.public.sqlserver.olap
Subject: RE: Problem with MDX query
Chris Webb Dec 31 2004, 4:01 am
I don't think the query is hanging, I think it's just taking a very long
time
to complete! Summing up all those days in your date range is going to
take a
long time, plus I'll bet that your second query (because it doesn't
mention
the YearMonthDay dimension at all) probably hits aggregations whereas
your
first query doesn't.
Since this is a fairly common problem I've just made it the subject of
the
first entry of my new blog, which you can read here:
http://spaces.msn.com/members/_cwebbbi/Blog/cns!1pi7ETChsJ1un__2s41jm9I.
.
In your case, I think the approach of replacing the day members in your
day
range set with common ancestors could solve the problem.
HTH,
Chris
Deepak Puri Jan 3, 1:07 pm show options
Hi Chris,
Here's an alternative formulation that doesn't refer to the individual
time dimension levels explicitly, so it should work with various
hierachies. An assumption here is that 'MYRANGE' is at the leaf level of
the time hierarchy:
*The initial time range expressed in months*/
SET MYRANGE AS '{[Time].[1997].[Q1].[3]
:[Time].[1998].[Q3].[7]}'
/*Test whether Time member is a subset of range */
Member [Measures].[InRange] as
'Except(Descendants([Time].Cur_rentMember,, LEAVES),
MYRANGE).Count = 0'
/* Include all members in range whose parents are not in range */
SET MYNEWRANGE AS
'Filter([Time].Members, [Measures].[InRange]
AND Not ( [Measures].[InRange], [Time].Parent))'
/*Helper calculated members to display the steps*/
MEMBER MEASURES.STEP1 AS 'SETTOSTR(MYRANGE)'
MEMBER MEASURES.STEP5 AS 'SETTOSTR(MYNEWRANGE)'
/*Show working*/
SELECT {MEASURES.STEP1,
MEASURES.STEP5} ON 0
FROM SALES[vbcol=seagreen]
- Deepak
Deepak Puri
Microsoft MVP - SQL Server
*** Sent via Developersdex http://www.codecomments.com ***
Don't just participate in USENET...get rewarded for it!

MDX: Getting the first child at a lower level

I'm trying to get the first day in a specific week or month or year. I've tried with HEAD, but the following measure returns #Error for every row.

Isn't HEAD returning a member ?

What am I doing wrong ? I have tried TopCount too.

member [Measures].[LY] as

(head(descendants([Calendar].[Fiscal].currentmember,[Calendar].[Fiscal].[Day]),1), [Measures].[Sales])

... rows ...

where ([Calendar].[Fiscal].[Month].[2007-02-26])

You are using hierarchy [Calendar].[Fiscal] in Axis 0 and in WHERE clause. You can use same hierarchy just in one place.

So you should do

member [Measures].[LY] as

(head(descendants([Calendar].[Fiscal].[Month].[2007-02-26],[Calendar].[Fiscal].[Day]),1), [Measures].[Sales])

... rows ...

And: Head returns set, not member.

Vidas Matelis

|||

Thanks Vidas,

How do I get the first member of the set ?

|||

Alan,

I pointed about member versus set as head function can return more than 1 member if specified, so it returns set. This will work without you actually worrying if this is set or tuple or member and that is nice feature of SSAS 2005.

Function .Item(0) gets first tuple from set or first member from tuple.

Vidas Matelis

|||That did it ! Many thanks.

MDX Training : Need review

Hi Gurus ...

Planning for a MDX Training for me and my development team ...
The following has been proposed for a training course spread over three days ...

I need to know whether this will be enough or should some more things be added on to the same (anything that has been missed)...

I have the option of removing and adding from the list.

Module 1: OLAP Review

OLAP Databases
OLAP Definitions
Warehouse Data Flow

Module 2: MDX Foundations

The Role of MDX
MDX Member Names
Using the Calculated Member Builder
Working with Calculated Members

Module 3: Using MDX Expressions

Displaying Member Information
Displaying Family Tree Relatives
Working with Member Properties
Using Conditional Expressions

Module 4: Retrieving Values from a Cube

Understanding Multidimensional Coordinates
Retrieving a Value from a Cube
Percent of Total Calculations
Growth Calculations

Module 5: Creating Simple MDX Queries

Understanding MDX Query Statements
Creating Simple MDX Query Statements

Module 6: Creating and Manipulating Sets

Using Set Creation Functions
Using Set Manipulation Functions
Using Subquery Set Functions
Working with Dimension Interactions

Module 7: Using Aggregation Functions

Understanding Aggregation Functions
Using Time Set Functions
Calculating a Trend

Module 8: Case Study - Implementing Budget Allocations

Allocating Values from a Grand Total
Allocating Values from a Subtotal
Allocating Values Across Multiple Dimensions

Module 9: Using Calculated Cells

Respose needed urgently since I have to get back to the training team by tomorrow morningWhat is MDX?|||he he .. nice joke|||Originally posted by Enigma
he he .. nice joke

I'm serious...

This?

http://www.mdxsoftware.com/|||You are serious ... :confused:

Worked on OLAP ... Analysis Server ?

MDX = Multi - Dimensional Expressions

MDX is query language used for querying OLAP Cubes|||Sorry...no warehouse tools...

Had to build them manually...

No magic....

What do you think you can do with analysis services that you can't do manually?

My Multi Billion $$ Company only bought standard edition...

I did find 1 box with Enterprise though...|||Hmm ...

It seems you havent used OLAP Cubes yet ...

I agree it can be done manually too ... but you too would love the power of analysis a Cube gives you (just like a pivot table in excel) ...|||Any way I can sneak into the back of the class? I don't think Microsoft has developed a course for MDX, as yet. Probably because they know their MDX implementation has a few kinks to work out...|||MS does have a course for mdx and it has been out for about 3 or 4 years.
you will find an outline at this link.
Course 2093: Implementing Business Logic with MDX in Microsoft SQL Server 2000 (http://www.microsoft.com/traincert/syllabi/2093AFinal.asp)

it is MOC (Microsoft Official Curriculum)
i have taught it a few times and it is pretty good.

it appears that someone is going to be using the same exact outline

MDX select with no aggregation

Dear All,

I tryed to do the following

OpenQuery([ssas DS], select {[Measures].[Weekday], [Measures].[Weekday], [Measures].[sales count]} on columns,
{[Dim Time].[Weekday].members} on rows
from CubeName)

It returns me the followings:

Weekday Weekday Sales Count
All 5 5 20402295
1 1 1 3445560
2 2 2 3950970
3 3 3 4532115
4 4 4 4498965
5 5 5 3974685

But I don't want the row "All 5 5 20402295" to be included in the row how can I remove it from the rowset?

Thanks

Tony Chun Tung Siu

Try replacing {[Dim Time].[Weekday].members} with {[Dim Time].[Weekday].[Weekday].members}