memory

Hi,

can anyone point me in the direction of any articles or whitepapers that give some ideas on memory requirements for running SQL Server 2005 analysis services.

Say we have 250 concurrent users, can anyone tell how much memory I would require. I know this will also depend on cube sizes and number of dimensions, in our case we have approximately 20 AS databases with an estimated 300 cubes in total. Some of the dimensions get up to 100,000 members.

Thinking that 3gb (32bit mode) isn't going to cut it and we will need to run in 64 bit mode for the additional memory availability.

Need some help with concret figures, and how I can prove that we need to run in the 64bit mode.

Any assistance much appreciated.

Thanks Mick

You are correct about going with 64bit hardware. That is better choice for enterprise-level applications.

About memory consumption. It is hard to predict what it is going to be. Memory consumption can go up depending on various conditions:

You using dimension or some kind of security where you have many different security settings for different users. In this case Analysis Server would have to create separate caches for every set of users with similar security settings.

Your dimensions are large and users are querying lots of dimension data.

You've defined calculations on your cube requiring lots of memory to compute.

Besides these considerations, you should definitely try and run some simulations with your application. Memory often is something you can add later to your system. Try getting machine with 6 or 8 gigs try running with it and see if you need some more.

If your simulations show the bottelneck for application is not memory, but CPU, you should also look into setting another machine side by side with the first one. Set them to work in NLB cluster to be able to handle greater load.

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

Mecanics behind a Transaction (COMMIT/Rollback)

Anyone can point me a document that explain the mecanics
used by SQL Server to perform a transaction (i.e: Begin
Transaction -- Commit or Rollback Transaction).
I would like to know which tables are used to do so and
all the details.
Regards,Basic priciple is that all are updates are written to log file before =being written to the db. Once the transaction commits an entry is made =in the log recording that fact. If the system should fail, then on arestart the log is examined (from =the most recemt checkpoint) and any transactions that have been =committed since that checkpoint are written to the db. any db pages for =transactions that were open (uncommittted) at the point of failure will =be unwound from the db by re-applying the before-image values from the =log.
The idea of a checkpoint is a marker in the log that records the facet =that all "dirty" pages in cache (ie those that have been updated) are =flushed back to disk, so the information in the log is guaranteed =consistent at the tme a checkpoint is taken. These days checkpoint =intervals are automatically tuned by SQl serever and normally are fine.
That's the short version. Inside SQL Server 2000 by Kalen Delaney has =loads more. Or Microsoft 2072 course also covers all of this.
Mike John
"Roger Dion" <RogerRKDDion@.Roditek.com> wrote in message =news:0fe701c351f8$c42febc0$a001280a@.phx.gbl...
> Anyone can point me a document that explain the mecanics > used by SQL Server to perform a transaction (i.e: Begin > Transaction -- Commit or Rollback Transaction).
> > I would like to know which tables are used to do so and > all the details.
> > Regards,
> >=20

MDX Query for today

Can someone please point me how I can issue a MDX query to get today's
date. I know I can use something like VBA!Now()
But I want to get this from a MDX query so I can use in Reporting
Services to populate the
default value for my report parameter called FromParam in the following
format
2006009 which is month 9 for year 2006.
Kindly help me with this MDX query that enables me to get the period in
the above format,
Thanks
KarenTHis is what I do when I need to get "Today" to use as a default:
Connect to a SQL database
Create a query like this:
select cast(DATEPART(year, getDate()) as varchar(20)) +
case len(Datepart(month, getDate()))
when 1
then '0' + cast(DatePart(month, Getdate()) as varchar(2))
else cast(DatePart(month, Getdate()) as varchar(2))
end
as ShowToday, GetDate() as Today,
'[Date].[Year].&[' + cast(DATEPART(year, getDate()) as varchar(4)) + '].&['
+
cast(DatePart(month, Getdate()) as varchar(2))
+']'
as OlapToday
Use either the value returned for ShowToday or OlapToday as my default
value.
OK, it's not MDX, but it does the trick. ;)
Kaisa M. Lindahl Lervik
"KarenM" <karenmiddleol@.yahoo.com> wrote in message
news:1158928968.212395.86710@.i3g2000cwc.googlegroups.com...
> Can someone please point me how I can issue a MDX query to get today's
> date. I know I can use something like VBA!Now()
> But I want to get this from a MDX query so I can use in Reporting
> Services to populate the
> default value for my report parameter called FromParam in the following
> format
> 2006009 which is month 9 for year 2006.
> Kindly help me with this MDX query that enables me to get the period in
> the above format,
> Thanks
> Karen
>

MDX hierarchy problem

Hello,

My point is I wonder how to retrieve a leaf member from a date hierarchy.

I would like to retrieve the leaf of [Hierarchy Calendar] at the CurrentMember, which is a Date (as 2007/01/07).
[Hierarchy Calendar] is composed of : Year, Semester, Quarter, Month, Week of year, Date.

I can't retrieve the date of the hierarchy using [Date].[Hierarchy Calendar].[Date].CurrentMember, and I absolutly need to use a hierarchy, not the dimension : [DATE].[Date]

My final MDX must look like that:
IIF(IsEmpty(([Date].[Hierarchy Calendar].CurrentMember,[Measures].[NAV PER SHARE AMOUNT])),

NULL,

IIF(Not IsEmpty([Measures].[NAV PER SHARE AMOUNT]),[Measures].[PRODUCT FIRST NAV DATE] ,([Measures].[FIRST NAV DATE],[Date].[Hierarchy Calendar].[Date].CurrentMember.PrevMember)

)

)

Any help would be appreciated.

Thx,

Damien

Here are two queries that might help point you in the right direction.

Good luck,
Bryan

Code Snippet

with member [Measures].[x] as

SETTOSTR(DESCENDANTS([Date].[Calendar].CurrentMember,,LEAVES))

select

x on 0,

[Date].[Calendar].[Month].[October 2003] on 1

from [Adventure Works]

;

select

{} on 0,

EXISTS([Date].[Date].[Date].Members,[Date].[Calendar].[Month].[October 2003]) on 1

from [Adventure Works]

;

|||

I know the DESCENDANTS function but I think that's not the solution ...

Maybe another solution ?

|||

How about that EXISTS function (in the second query)?

B.

|||

Thanks for your help.

For information, I resolved my problem with this, it's not very optimized but works under my scheduling constraints

Thanks again.

.FIRSTCHILD:[DATE].[Hierarchy Calendar].CURRENTMEMBER,NOT ISEMPTY([Measures].[NAV PER SHARE AMOUNT])),EXCLUDEEMPTY) =0,

([MEASURES].[FIRST NAV DATE],[DATE].[Hierarchy Calendar].CurrentMember.Parent.Parent.PrevMember),

([DATE].[Hierarchy Calendar].CurrentMember.Parent.Parent,[Measures].[PRODUCT FIRST NAV DATE])

)

)

),

IIF(ISEMPTY(([Measures].[NAV PER SHARE AMOUNT])),

IIF([DATE].[Hierarchy Calendar].CurrentMember.Level IS [DATE].[Hierarchy Calendar].[Year] OR COUNT(filter([DATE].[Hierarchy Calendar].CurrentMember.Parent.FirstChild:[DATE].[Hierarchy Calendar].CurrentMember,not isempty([Measures].[NAV PER SHARE AMOUNT])))>0,

([Measures].[FIRST NAV DATE],[DATE].[Hierarchy Calendar].CurrentMember.PrevMember),

([Measures].[FIRST NAV DATE],[DATE].[Hierarchy Calendar].CurrentMember.Parent.PrevMember)

),

[Measures].[PRODUCT FIRST NAV DATE]

)

)

)

MDX hierarchy problem

Hello,

My point is I wonder how to retrieve a leaf member from a date hierarchy.

I would like to retrieve the leaf of [Hierarchy Calendar] at the CurrentMember, which is a Date (as 2007/01/07).
[Hierarchy Calendar] is composed of : Year, Semester, Quarter, Month, Week of year, Date.

I can't retrieve the date of the hierarchy using [Date].[Hierarchy Calendar].[Date].CurrentMember, and I absolutly need to use a hierarchy, not the dimension : [DATE].[Date]

My final MDX must look like that:
IIF(IsEmpty(([Date].[Hierarchy Calendar].CurrentMember,[Measures].[NAV PER SHARE AMOUNT])),

NULL,

IIF(Not IsEmpty([Measures].[NAV PER SHARE AMOUNT]),[Measures].[PRODUCT FIRST NAV DATE] ,([Measures].[FIRST NAV DATE],[Date].[Hierarchy Calendar].[Date].CurrentMember.PrevMember)

)

)

Any help would be appreciated.

Thx,

Damien

Here are two queries that might help point you in the right direction.

Good luck,
Bryan

Code Snippet

withmember [Measures].[x] as

SETTOSTR(DESCENDANTS([Date].[Calendar].CurrentMember,,LEAVES))

select

x on 0,

[Date].[Calendar].[Month].[October 2003] on 1

from [Adventure Works]

;

select

{} on 0,

EXISTS([Date].[Date].[Date].Members,[Date].[Calendar].[Month].[October 2003]) on 1

from [Adventure Works]

;

|||

I know the DESCENDANTS function but I think that's not the solution ...

Maybe another solution ?

|||

How about that EXISTS function (in the second query)?

B.

|||

Thanks for your help.

For information, I resolved my problem with this, it's not very optimized but works under my scheduling constraints

Thanks again.

.FIRSTCHILD:[DATE].[Hierarchy Calendar].CURRENTMEMBER,NOTISEMPTY([Measures].[NAV PER SHARE AMOUNT])),EXCLUDEEMPTY) =0,

([MEASURES].[FIRST NAV DATE],[DATE].[Hierarchy Calendar].CurrentMember.Parent.Parent.PrevMember),

([DATE].[Hierarchy Calendar].CurrentMember.Parent.Parent,[Measures].[PRODUCT FIRST NAV DATE])

)

)

),

IIF(ISEMPTY(([Measures].[NAV PER SHARE AMOUNT])),

IIF([DATE].[Hierarchy Calendar].CurrentMember.LevelIS [DATE].[Hierarchy Calendar].[Year] ORCOUNT(filter([DATE].[Hierarchy Calendar].CurrentMember.Parent.FirstChild:[DATE].[Hierarchy Calendar].CurrentMember,notisempty([Measures].[NAV PER SHARE AMOUNT])))>0,

([Measures].[FIRST NAV DATE],[DATE].[Hierarchy Calendar].CurrentMember.PrevMember),

([Measures].[FIRST NAV DATE],[DATE].[Hierarchy Calendar].CurrentMember.Parent.PrevMember)

),

[Measures].[PRODUCT FIRST NAV DATE]

)

)

)

MDX Help.

This is my current MDX for a KPI in Performance Point:

(([Current Month].item(0), [Measures].[Revenue]) - ([Current Month].item(0).PrevMember, [Measures].[Revenue]))/ ([Current Month].item(0).PrevMember, [Measures].[Revenue])

I need to modify this to only include Customers of a of Type Dealer and Distributor in my cube.

[Customers].[Customers].[Customer Type].&[Dealer]

[Customers].[Customers].[Customer Type].&[Distributor]

[Customers].[Customers].[Customer Type].&[Direct] (do not include)

Wow...it seems like it should be so easy...but I cannot get the syntax.

Help!!!!

Linda

Hi Linda,

You could create a calculated member to agrgegate the 2 included customer types, like:

Create Member CurrentCube.[Customers].[Customers].[Dealer-Distributor] as

Aggregate({[Customers].[Customers].[Customer Type].&[Dealer],

[Customers].[Customers].[Customer Type].&[Distributor]} )

and another to compute Revenue Growth %, like:

Create Member CurrentCube.[Measures].[RevenueGrowth%] as

([Measures].[Revenue] - ([Date].[Month].PrevMember, [Measures].[Revenue]))

/ ([Date].[Month].PrevMember, [Measures].[Revenue])

Then the KPI could be:

([Current Month].item(0), [Customers].[Customers].[Dealer-Distributor], [Measures].[RevenueGrowth%])