Memory allocation

I'm getting ready to build a new SQL 2000 server. Due to budget constraints
,
I am going to be building SQL Standard on Windows 2000 Standard. I'm unclea
r
as to whether or not, in this scenario, I can allocate more than 2 GB of RAM
.
I know you can put a switch in the boot.ini if you're running Advanced
Server, but is this switch useable with Windows 2000 Standard?
Thanks,
MarshaThe Books Online <architec.chm::/8_ar_ts_8dbn.htm> states that SQL 2000 SE
has a 2GB memory limit. Also, the /3GB boot.ini switch is unsupported under
Windows 2000 Standard as described in
http://support.microsoft.com/kb/291988. The /3GB is available with Windows
2000 Standard but only for testing purposes.
Hope this helps.
Dan Guzman
SQL Server MVP
"Marsha" <Marsha@.discussions.microsoft.com> wrote in message
news:CD2B35E4-A9FA-48B8-A466-AF6E687321F3@.microsoft.com...
> I'm getting ready to build a new SQL 2000 server. Due to budget
> constraints,
> I am going to be building SQL Standard on Windows 2000 Standard. I'm
> unclear
> as to whether or not, in this scenario, I can allocate more than 2 GB of
> RAM.
> I know you can put a switch in the boot.ini if you're running Advanced
> Server, but is this switch useable with Windows 2000 Standard?
> Thanks,
> Marsha|||Thank you for your response.
"Marsha" wrote:

> I'm getting ready to build a new SQL 2000 server. Due to budget constrain
ts,
> I am going to be building SQL Standard on Windows 2000 Standard. I'm uncl
ear
> as to whether or not, in this scenario, I can allocate more than 2 GB of R
AM.
> I know you can put a switch in the boot.ini if you're running Advanced
> Server, but is this switch useable with Windows 2000 Standard?
> Thanks,
> Marsha|||Note that Windows 2003 Standard supports up to 4GB RAM, according to
http://www.microsoft.com/windowsser...s.msp
x.
However, SQL2000 Standard still only supports 2GB so it doesn't currently
help.
If you look to the future however, SQL2005 standard will support "unlimited"
RAM when run on 64-bit systems like the new AMD Opterons. This would require
Windows 2003/64 or whatever it's called. I've heard that MS is going to
offer a free upgrade from XP or W2k3 to the 64bit equivalent edition. So
depending on the memory limitations MS sets on W2k3/64 Standard you may be
able to use significantly more RAM in the not too distant future.
Here's a link to some SQL2005 info.
http://www.microsoft.com/sql/2005/p...005features.asp
Mike Kruchten
"Marsha" <Marsha@.discussions.microsoft.com> wrote in message
news:EFC6C0D6-514D-4729-B985-C0E800891D12@.microsoft.com...[vbcol=seagreen]
> Thank you for your response.
> "Marsha" wrote:
>

Memory allocation

I'm getting ready to build a new SQL 2000 server. Due to budget constraints,
I am going to be building SQL Standard on Windows 2000 Standard. I'm unclear
as to whether or not, in this scenario, I can allocate more than 2 GB of RAM.
I know you can put a switch in the boot.ini if you're running Advanced
Server, but is this switch useable with Windows 2000 Standard?
Thanks,
MarshaThe Books Online <architec.chm::/8_ar_ts_8dbn.htm> states that SQL 2000 SE
has a 2GB memory limit. Also, the /3GB boot.ini switch is unsupported under
Windows 2000 Standard as described in
http://support.microsoft.com/kb/291988. The /3GB is available with Windows
2000 Standard but only for testing purposes.
--
Hope this helps.
Dan Guzman
SQL Server MVP
"Marsha" <Marsha@.discussions.microsoft.com> wrote in message
news:CD2B35E4-A9FA-48B8-A466-AF6E687321F3@.microsoft.com...
> I'm getting ready to build a new SQL 2000 server. Due to budget
> constraints,
> I am going to be building SQL Standard on Windows 2000 Standard. I'm
> unclear
> as to whether or not, in this scenario, I can allocate more than 2 GB of
> RAM.
> I know you can put a switch in the boot.ini if you're running Advanced
> Server, but is this switch useable with Windows 2000 Standard?
> Thanks,
> Marsha|||Thank you for your response.
"Marsha" wrote:
> I'm getting ready to build a new SQL 2000 server. Due to budget constraints,
> I am going to be building SQL Standard on Windows 2000 Standard. I'm unclear
> as to whether or not, in this scenario, I can allocate more than 2 GB of RAM.
> I know you can put a switch in the boot.ini if you're running Advanced
> Server, but is this switch useable with Windows 2000 Standard?
> Thanks,
> Marsha|||Note that Windows 2003 Standard supports up to 4GB RAM, according to
http://www.microsoft.com/windowsserver2003/evaluation/features/compareeditions.mspx.
However, SQL2000 Standard still only supports 2GB so it doesn't currently
help.
If you look to the future however, SQL2005 standard will support "unlimited"
RAM when run on 64-bit systems like the new AMD Opterons. This would require
Windows 2003/64 or whatever it's called. I've heard that MS is going to
offer a free upgrade from XP or W2k3 to the 64bit equivalent edition. So
depending on the memory limitations MS sets on W2k3/64 Standard you may be
able to use significantly more RAM in the not too distant future.
Here's a link to some SQL2005 info.
http://www.microsoft.com/sql/2005/productinfo/sql2005features.asp
Mike Kruchten
"Marsha" <Marsha@.discussions.microsoft.com> wrote in message
news:EFC6C0D6-514D-4729-B985-C0E800891D12@.microsoft.com...
> Thank you for your response.
> "Marsha" wrote:
>> I'm getting ready to build a new SQL 2000 server. Due to budget
>> constraints,
>> I am going to be building SQL Standard on Windows 2000 Standard. I'm
>> unclear
>> as to whether or not, in this scenario, I can allocate more than 2 GB of
>> RAM.
>> I know you can put a switch in the boot.ini if you're running Advanced
>> Server, but is this switch useable with Windows 2000 Standard?
>> Thanks,
>> Marsha

Memory allocation

I'm getting ready to build a new SQL 2000 server. Due to budget constraints,
I am going to be building SQL Standard on Windows 2000 Standard. I'm unclear
as to whether or not, in this scenario, I can allocate more than 2 GB of RAM.
I know you can put a switch in the boot.ini if you're running Advanced
Server, but is this switch useable with Windows 2000 Standard?
Thanks,
Marsha
The Books Online <architec.chm::/8_ar_ts_8dbn.htm> states that SQL 2000 SE
has a 2GB memory limit. Also, the /3GB boot.ini switch is unsupported under
Windows 2000 Standard as described in
http://support.microsoft.com/kb/291988. The /3GB is available with Windows
2000 Standard but only for testing purposes.
Hope this helps.
Dan Guzman
SQL Server MVP
"Marsha" <Marsha@.discussions.microsoft.com> wrote in message
news:CD2B35E4-A9FA-48B8-A466-AF6E687321F3@.microsoft.com...
> I'm getting ready to build a new SQL 2000 server. Due to budget
> constraints,
> I am going to be building SQL Standard on Windows 2000 Standard. I'm
> unclear
> as to whether or not, in this scenario, I can allocate more than 2 GB of
> RAM.
> I know you can put a switch in the boot.ini if you're running Advanced
> Server, but is this switch useable with Windows 2000 Standard?
> Thanks,
> Marsha
|||Thank you for your response.
"Marsha" wrote:

> I'm getting ready to build a new SQL 2000 server. Due to budget constraints,
> I am going to be building SQL Standard on Windows 2000 Standard. I'm unclear
> as to whether or not, in this scenario, I can allocate more than 2 GB of RAM.
> I know you can put a switch in the boot.ini if you're running Advanced
> Server, but is this switch useable with Windows 2000 Standard?
> Thanks,
> Marsha
|||Note that Windows 2003 Standard supports up to 4GB RAM, according to
http://www.microsoft.com/windowsserv...editions.mspx.
However, SQL2000 Standard still only supports 2GB so it doesn't currently
help.
If you look to the future however, SQL2005 standard will support "unlimited"
RAM when run on 64-bit systems like the new AMD Opterons. This would require
Windows 2003/64 or whatever it's called. I've heard that MS is going to
offer a free upgrade from XP or W2k3 to the 64bit equivalent edition. So
depending on the memory limitations MS sets on W2k3/64 Standard you may be
able to use significantly more RAM in the not too distant future.
Here's a link to some SQL2005 info.
http://www.microsoft.com/sql/2005/pr...05features.asp
Mike Kruchten
"Marsha" <Marsha@.discussions.microsoft.com> wrote in message
news:EFC6C0D6-514D-4729-B985-C0E800891D12@.microsoft.com...[vbcol=seagreen]
> Thank you for your response.
> "Marsha" wrote:

Measure change updates in Fact tables

Is there an industry standard method to populate Fact table columns with the
amount of change of a measure from one row to the next?
I have a FactSubscriptions (1 row = 1 week), and I wold like to have a
column of the price change from the previous week.
I'm wrestling with using tsql to join the Fact to itself offset by one week.
Progress is slowly being made, but I'm saying to myself "There's gotta be an
easier way!"
I at least need some keywords to do further research.
thanks,
Conrad
This type of query is usually done in the OLAP structures using MDX. Doing
it in t-sql would result in a poor performance.
If you do need to do it in t-sql please post sample of data and structures.
For example, how is date stored in your tables? As a datetime or you have a
week number or something?
MC
"Conrad" <Conrad@.discussions.microsoft.com> wrote in message
news:45C056A5-A015-4517-9CD8-F9D46D868968@.microsoft.com...
> Is there an industry standard method to populate Fact table columns with
> the
> amount of change of a measure from one row to the next?
> I have a FactSubscriptions (1 row = 1 week), and I wold like to have a
> column of the price change from the previous week.
> I'm wrestling with using tsql to join the Fact to itself offset by one
> week.
> Progress is slowly being made, but I'm saying to myself "There's gotta be
> an
> easier way!"
> I at least need some keywords to do further research.
> thanks,
> Conrad
>
|||1. Could you give me some OLAP/MDX keywords I could research further?
2. I have both WeekID and a date, but the "offset join" is done by WeekID.
I don't know OLAP or MDX yet, so I'll need to do this project in tsql. The
data is small, so
performance is not a problem.
Here's a representation of the data and tsql:
Table A
ID WeekID CustID Price PriceChange
1 1 90 100 0
2 2 90 110 10
3 3 90 110 0
4 4 90 110 0
5 1 91 100 0
6 2 91 100 0
7 3 91 120 20
8 4 91 120 0
Insert Table A Into ##TempB [sic]
Select ID, WeekID, CustID
From A
Inner Join ##TempB
On A.WeekID = ##TempB + 1
And A.CustID = ##TempB.CustID
tia,
Conrad
"MC" wrote:

> This type of query is usually done in the OLAP structures using MDX. Doing
> it in t-sql would result in a poor performance.
> If you do need to do it in t-sql please post sample of data and structures.
> For example, how is date stored in your tables? As a datetime or you have a
> week number or something?
>
> MC
>
> "Conrad" <Conrad@.discussions.microsoft.com> wrote in message
> news:45C056A5-A015-4517-9CD8-F9D46D868968@.microsoft.com...
>
>
|||You obviously need price change over the weeks for each custID (customer?).
Does this wokr for you?
select
A.price,
isnull((select A2.price from tableA A2 where A2.weekID = A.WeekID - 1
and A2.CustID = A.CustID), A.price) as PreviousPrice,
A.WeekID,
A.CustID
from
tableA A
Alternatively, you can use join as you did but using the same table.
select
A.price,
isnull(A2.Price,A.Price) as PreviousPrice,
A.WeekID,
A.CustID
from
tableA A
left join TableA A2 on A.WeekID = A2.WeekID - 1 ANDA.CustID = A2.CustID
MC
"Conrad" <Conrad@.discussions.microsoft.com> wrote in message
news:0E83399D-4B5C-40CE-988F-D58F9898F9E5@.microsoft.com...[vbcol=seagreen]
> 1. Could you give me some OLAP/MDX keywords I could research further?
> 2. I have both WeekID and a date, but the "offset join" is done by WeekID.
> I don't know OLAP or MDX yet, so I'll need to do this project in tsql. The
> data is small, so
> performance is not a problem.
> Here's a representation of the data and tsql:
> Table A
> ID WeekID CustID Price PriceChange
> 1 1 90 100 0
> 2 2 90 110 10
> 3 3 90 110 0
> 4 4 90 110 0
> 5 1 91 100 0
> 6 2 91 100 0
> 7 3 91 120 20
> 8 4 91 120 0
> Insert Table A Into ##TempB [sic]
> Select ID, WeekID, CustID
> From A
> Inner Join ##TempB
> On A.WeekID = ##TempB + 1
> And A.CustID = ##TempB.CustID
>
> tia,
> Conrad
>
> "MC" wrote:

Measure change updates in Fact tables

Is there an industry standard method to populate Fact table columns with the
amount of change of a measure from one row to the next?
I have a FactSubscriptions (1 row = 1 week), and I wold like to have a
column of the price change from the previous week.
I'm wrestling with using tsql to join the Fact to itself offset by one week.
Progress is slowly being made, but I'm saying to myself "There's gotta be an
easier way!"
I at least need some keywords to do further research.
thanks,
ConradThis type of query is usually done in the OLAP structures using MDX. Doing
it in t-sql would result in a poor performance.
If you do need to do it in t-sql please post sample of data and structures.
For example, how is date stored in your tables? As a datetime or you have a
week number or something?
MC
"Conrad" <Conrad@.discussions.microsoft.com> wrote in message
news:45C056A5-A015-4517-9CD8-F9D46D868968@.microsoft.com...
> Is there an industry standard method to populate Fact table columns with
> the
> amount of change of a measure from one row to the next?
> I have a FactSubscriptions (1 row = 1 week), and I wold like to have a
> column of the price change from the previous week.
> I'm wrestling with using tsql to join the Fact to itself offset by one
> week.
> Progress is slowly being made, but I'm saying to myself "There's gotta be
> an
> easier way!"
> I at least need some keywords to do further research.
> thanks,
> Conrad
>|||1. Could you give me some OLAP/MDX keywords I could research further?
2. I have both WeekID and a date, but the "offset join" is done by WeekID.
I don't know OLAP or MDX yet, so I'll need to do this project in tsql. The
data is small, so
performance is not a problem.
Here's a representation of the data and tsql:
Table A
ID WeekID CustID Price PriceChange
1 1 90 100 0
2 2 90 110 10
3 3 90 110 0
4 4 90 110 0
5 1 91 100 0
6 2 91 100 0
7 3 91 120 20
8 4 91 120 0
Insert Table A Into ##TempB [sic]
Select ID, WeekID, CustID
From A
Inner Join ##TempB
On A.WeekID = ##TempB + 1
And A.CustID = ##TempB.CustID
tia,
Conrad
"MC" wrote:

> This type of query is usually done in the OLAP structures using MDX. Doing
> it in t-sql would result in a poor performance.
> If you do need to do it in t-sql please post sample of data and structures
.
> For example, how is date stored in your tables? As a datetime or you have
a
> week number or something?
>
> MC
>
> "Conrad" <Conrad@.discussions.microsoft.com> wrote in message
> news:45C056A5-A015-4517-9CD8-F9D46D868968@.microsoft.com...
>
>|||You obviously need price change over the weeks for each custID (customer?).
Does this wokr for you?
select
A.price,
isnull((select A2.price from tableA A2 where A2.weekID = A.WeekID - 1
and A2.CustID = A.CustID), A.price) as PreviousPrice,
A.WeekID,
A.CustID
from
tableA A
Alternatively, you can use join as you did but using the same table.
select
A.price,
isnull(A2.Price,A.Price) as PreviousPrice,
A.WeekID,
A.CustID
from
tableA A
left join TableA A2 on A.WeekID = A2.WeekID - 1 ANDA.CustID = A2.CustID
MC
"Conrad" <Conrad@.discussions.microsoft.com> wrote in message
news:0E83399D-4B5C-40CE-988F-D58F9898F9E5@.microsoft.com...[vbcol=seagreen]
> 1. Could you give me some OLAP/MDX keywords I could research further?
> 2. I have both WeekID and a date, but the "offset join" is done by WeekID.
> I don't know OLAP or MDX yet, so I'll need to do this project in tsql. The
> data is small, so
> performance is not a problem.
> Here's a representation of the data and tsql:
> Table A
> ID WeekID CustID Price PriceChange
> 1 1 90 100 0
> 2 2 90 110 10
> 3 3 90 110 0
> 4 4 90 110 0
> 5 1 91 100 0
> 6 2 91 100 0
> 7 3 91 120 20
> 8 4 91 120 0
> Insert Table A Into ##TempB [sic]
> Select ID, WeekID, CustID
> From A
> Inner Join ##TempB
> On A.WeekID = ##TempB + 1
> And A.CustID = ##TempB.CustID
>
> tia,
> Conrad
>
> "MC" wrote:
>

MDX Script: How do I create a YTD-Balance Measure?

Hi,

I am using the Standard Edition of SQL Server 2005. I have a financial reporting cube with the measure Amount and several dimensions (Time, Account, etc). A simplified version of the data is:

AccountType

Month1

Month2

Month3

Month4

etc...

Asset

200

20

25

30

Liability

-100

-5

-10

-15

If I use the default cube then this is how data will show. What I would like to do is to add a YTD-Balance measure to the cube. This YTD-Balance measure would show the following data:

AccountType

Month1

Month2

Month3

Month4

Asset

200

220

245

275

Liability

-100

-105

-115

-130

So instead of showing the difference in a month (eg 20) like the underlying data, I would like to show a running total for the year (eg 200+20=220). My attempt so far is:

Create Member CurrentCube.[Measures].[YTDBalance]
AS SUM({PeriodsToDate([Time].[(All)])}, [Measures].[Amount]),
NON_EMPTY_BEHAVIOR = { [Amount] },
VISIBLE = 1;

This doesn't do what I want however: it sums all months in a quarter, all quarters in a year, and all years in the cube. What I instead want is for each month to be a summary of the months in the year so far. Is anyone able to help me out at all with this?

Thanks, Matt

Assuming that there is a [Year] attribute in the [Time] dimension, whose type is "Years"; and that there is a [Time].[Calendar] hierarchy (as in the Adventure Works Date dimension), then you should be able to use the YTD() function:

Create Member CurrentCube.[Measures].[YTDBalance]
AS SUM(YTD([Time].[Calendar].CurrentMember), [Measures].[Amount]),
NON_EMPTY_BEHAVIOR = { [Measures].[Amount] },
VISIBLE = 1;

|||A word of caution: You should only set the NON_EMPTY_BEHAVIOR if you are certain that there actually is a value in [Measures].[Amount] for the cells in which you want to display YTDBalance. Example: If you wanted to show the YTDBalance for all months in 2006, but you only had data up until July 2006, the calculated member suggested by Deepak would only return values for YTDBalance up until July 2006.

MDX Query Tool Problem

I am trying to get to grips with cube reporting and am running the MDX Query
Tool against AdventureWorksDW on SQL 2005 sp1, Standard Edition (I am not
fluent in MDX). No matter what dimensions I drag on, the only output I can
get is a single cell / report services field.
For example, if I drag on the geography.country dimension and Sales
Order.Order Count measure, I just get a count of all orders.
--
simonI have found some sample MDX code for AdventureWorks that does work with the
Report Builder. Clearly I need to get more proficient with writing good
queries.
--
simon
"simon p" wrote:
> I am trying to get to grips with cube reporting and am running the MDX Query
> Tool against AdventureWorksDW on SQL 2005 sp1, Standard Edition (I am not
> fluent in MDX). No matter what dimensions I drag on, the only output I can
> get is a single cell / report services field.
> For example, if I drag on the geography.country dimension and Sales
> Order.Order Count measure, I just get a count of all orders.
> --
> simon