## Ranking Calculated Measures

MS SQL SERVER

### Next

• 1. Date calculation
Hi All, I have the following situation: - date dim (on month level) - plandate dim - project dim - projectstate dim - amount measure Each project has a plandate and at that date a certain amount in planstate x. For example, project x has a plandate of 04-21-2008, a planstate of B and amount 1000. What I want is a calculated member which calculates the amount of the plandate until the end of the year. So the produced results should be: Project X Plandate 04-21-2008 Planstate B Date Calculation 01-2008 02-2008 03-2008 04-2008 1000 05-2008 1000 06-2008 1000 07-2008 1000 08-2008 1000 09-2008 1000 10-2008 1000 11-2008 1000 12-2008 1000 I know this should not be too difficult but I cannot find the solution Any ideas? Q
• 2. Design question - urgent
Seems like a loose end. Is it in DEV or Production? If in DEV perhaps there was/is some re-work going on. "michael vardinghus" <xx> wrote in message news: XXXX@XXXXX.COM ... > In Dimension Usage in cube design I am looking at solution someone else > made and that solution has a dimension which has no mapping to measure > Group Colums > > Is this an ok approach ? > >
• 3. TotalMemoryLimit, AggregationMemoryLimit and BufferMemoryLimit
There are 3 SSAS 2005 memory configuration points which are confusing to me: * Memory\TotalMemoryLimit * OLAP\Process\AggregationMemoryLimit * OLAP\Process\BufferMemoryLimit I have read documents that state they all take either a percentage or a set amount of total physical memory. So if let's say the TotalMemoryLimit is set to 40, AggregationMemoryLimit is 80 and BufferMemoryLimit is 60, would that mean SSAS will try to use 40+80+60=180% of the total memory? Is there one setting that says "you get X % of memory and everything else uses memory from that". This is all very confusing. SQL Server is much cleaner.
• 4. Running sentences mdx in remote
I have a problem: I need that the users believe their own local cube. The idea is that them directly they can execute the statement mdx 'Create Global Cube' , but in remote. Exists some program or utility that it allows to execute statements mdx in remote, without the user has installed the management Studio?. We work with Analysis Services 2005.
• 5. Data Source Pulls
We are pulling data from about 6 sub-systems and change management is hard. At first we were using SMO to transfer across the tables into a pre-staging area. However; any change whatsoever to the table structures break this part of the ETL. We believe creating views on the sub-systems is the answer - however we have 2 choices. Create the view on the sub-system or create the view on our pre-staging area. The pro's to having the views living on the sub-system: * Any changes to that system, the developers of that sub-system would be responsible for not breaking those views The con's: * Would require the DW folks to have access to the subsystems to maintain or add to the views Anyone else have some thoughs on this?

### Ranking Calculated Measures

```Hi,

I have created a calculated measures in one cube that derives a percentage. I
want to group the percentages together. For example group all 25% in one
category and all 50% in another category.

I have tryied to create the percentage in my table but the value is not
aggregated. So the percentage is not correct.

Appreciate all suggestions.

Cheers,
Chillout

```

```Hi,
I am trying to create a calculated measure that rank customers based charge
amount.  I have created a Name Set and Calculated Measure as followed:

Top10 = TopCount([CUSTOMER].Members, 10,([CHARGE TYPE].CurrentMember,
[Measures].[Charge Amt]))

Customer Rank = Rank([CUSTOMER].CurrentMember, {[Top40]},([CHARGE
TYPE].CurrentMember,[Measures].[Charge Amt]))

I have created an PivotTable with the following dimensions and measures:

PageFilter
-----------
OFFICE
DESK
PERIOD
CHARGE TYPE(GROSS COMMISSIOIN, LOCAL BROKER FEE, US CLEARING FEE, ETC.)

ROW
-----
CUSTOMER

COLUMN
-----------
CHARGE AMT
CUSTOMER RANK

Here is my result:

CUSTOMER         CHARGE AMT  CUSTOMER RANK
--------       ------------  ------------
C2350021	        608,093.04	    1
C4525045	        518,528.37	    1
C9034850	        566,929.96	    1
C1256340	        493,519.20	    1
C3456569	      1,625,785.03	    2
C7694950	      1,470,338.30	    3
C9476583	      1,284,734.32	    4
C3487900       1,205,712.97	    5
C0983745	      1,117,468.76	    6
C1289340	      893,224.21	    7
Grand Total	9,784,334.16	1

Not sure why I am getting customer with the same ranking.  Also, when I
change the charge type selection, the ranking does not change.  I need to be
able to select different charge type and display the ranking.

Any assistant is greatly appreciated.

Regards,
Fernando Sanchez
```

```I'm trying to create a measure that will return either an error
message or when aggregated, show the number of errors.  I first
created an Error Count measure that does a simple sum of the errors.
Then I created a calculated measure that basically consits of an iif
that returns the count if the count is > 1 or "some text" if the count
is 1.  The problem I'm having is how to actually refer to the error
text ("some text" is currently a placeholder) in that statement.  The
source column for the Error Count measure is an errorTypeId column
which links to an ErrorType table that contains the description that I
want to show.  I was thinking of somehow creating a dimension off of
the ErrorType table and then getting the appropriate member back by
using the ErrorTypeId that is being used for the Error Count measure.
But I haven't been able to get anything to work so far.  Does anyone
have any ideas?

David
```

```I have a measure group with two facts, one of those with visibility set to
False.

However, when using that hidden fact in a calculated member, I receive an
error when trying to open up the cube.

The fact on it's own really makes no sense and I'd like to hide it from end
users, but I can't figure out a way to use it in a calculated member and
also have it remain hidden.

Ideas?

SSAS2005

```