MDX Ratio And All Member

May 21, 2008

Hello,

I have a calculated member that is a ratio calculation. It works fine but I would like that the value for the "All" member to be the sum of the children.

So I have the following MDX where the calculated member [Dim Group Rubric].[Group Rubric Description].[BO + ADM Cost] does not give me good results for the "All" member:


WITH

MEMBER [TOTAL YEAR CONTRACT] AS sum({[Total Year]},[Dim Contract].[Contract Number].[563131])

MEMBER [TOTAL YEAR BU] AS sum ([Total Year])

MEMBER [Dim Group Rubric].[Group Rubric Description].[TOTAL BU PVN] as

([Dim Group Rubric].[Group Rubric Description].[PVN], [TOTAL YEAR BU])

MEMBER [Dim Group Rubric].[Group Rubric Description].[TOTAL BU BO + ADM] as

([Dim Group Rubric].[Group Rubric Description].[BO + ADM], [TOTAL YEAR BU])

MEMBER [Dim Group Rubric].[Group Rubric Description].[BO + ADM Cost] as

([Dim Group Rubric].[Group Rubric Description].[PVN], [TOTAL YEAR CONTRACT])

/ ([Dim Group Rubric].[Group Rubric Description].[TOTAL BU PVN], [TOTAL YEAR BU])

* ([Dim Group Rubric].[Group Rubric Description].[TOTAL BU BO + ADM], [TOTAL YEAR BU])



SELECT

NON EMPTY {[TOTAL YEAR CONTRACT],[TOTAL YEAR BU]}

ON 0,



{[Dim Group Rubric].[Group Rubric Description].[BO + ADM Cost]}

*

UNION(

[Dim BV Organisation Finance].[Business Unit].[All]

,FILTER(

[Dim BV Organisation Finance].[Business Unit].children

,[Dim BV Organisation Finance].[Business Unit Code].currentmember.name

= '071MIND1'

)

,FILTER(

[Dim BV Organisation Finance].[Business Unit].children

,[Dim BV Organisation Finance].[Business Unit Code].currentmember.name

<> '071MIND1'

)

)

ON 1

FROM (SELECT ([Dim Year].[Year].&[2008]) ON 0

FROM (SELECT ({[Dim BV Organisation Finance].[Business Unit Code].[071MIND1]

,[Dim BV Organisation Finance].[Business Unit Code].[071DIDD1]}) ON 0

FROM [BV Contract Margin DW]))


When I launch this query I obtain:
TOTAL YEAR BU
BO + ADM Cost All - 45013 (I would like to obtain here - 90651)
BO + ADM Cost 071MIND1 - 35680
BO + ADM Cost 071DIDD1 - 54971

Please do you know what should be changed in this query to obtain the good results?

Thanks.

Guillaume

View 3 Replies


ADVERTISEMENT

A New Member Could Not Be Added To A Local Group Because The Member Has The Wrong Account Type

Mar 23, 2007

Hi all,

I installed SQL 2005 SP2 + ReportServices Add-in for Sharepoint (WSS 3.0). All it's OK until I try to grant database access in the Sharepoint Central Admin site.

I setup the Reporting Services Integration (Manage integration settings). I use the default SQL instance, I put the USERNAME and the PASSWORD of my ADMIN account in the ENTER CREDENTIALS windows.. When I click the "OK" button, I receive always the error ...

"A new member could not be added to a local group because the member has the wrong account type"

I tried a lot of things... without success.

Is there someone who can help me....

PS: There is no error in the LOG



Thanks

View 18 Replies View Related

A New Member Could Not Be Added To A Local Group Because The Member Has The Wrong Account Type

Mar 23, 2007

Hi all,

I installed SQL 2005 SP2 + ReportServices Add-in for Sharepoint (WSS 3.0). All it's OK until I try to grant database access in the Sharepoint Central Admin site.

I setup the Reporting Services Integration (Manage integration settings). I use the default SQL instance, I put the USERNAME and the PASSWORD of my ADMIN account in the ENTER CREDENTIALS windows.. When I click the "OK" button, I receive always the error ...

"A new member could not be added to a local group because the member has the wrong account type"

I tried a lot of things... without success.

Is there someone who can help me....

PS: There is no error in the LOG



Thanks

View 4 Replies View Related

Master Data Services :: How To Add A Reference To Another Entity Member When Creating A Member

Oct 23, 2014

I need to create a member that one of its Attributes (maybe my term is wrong) is a reference to another entity member named group 
the code 

createRequest.Members.MemberType = MemberType.Leaf;
createRequest.Members.Members = new System.Collections.ObjectModel.Collection<Member> { };
Member aNewMember = new Member();
aNewMember.MemberId = new MemberIdentifier() { Name = uag.groupName, MemberType = MemberType.Leaf };

[code]....

 "The attribute data type is not valid".Is it wrong to add the reference as attribute? How can I embed the reference in the new member? 

View 4 Replies View Related

SQL 2012 :: Delete Rows If Member Does Not Exists In The Member Table

Sep 24, 2015

I have a table where I need to delete rows if the member does not exists in the member table. Will the following syntax work ?

Delete T
FROM Event_Temp_Lead_Screen T
left join member M on ( M.MemberID = T.MemberID )
where
T.Gender is NULL

View 9 Replies View Related

Cache Hit Ratio Vs Buffered Cache Hit Ratio

Mar 13, 2003

On Microsoft performance monitor, what is the difference between SQL Server Cache Manager: Cache Hit Ratio and SQL
Server Buffer Manager: Buffer Cache Hit Ratio? We have a production server where the buffer cache hit ratio is
consistently at 99%, which is normal. However, the cache hit ratio is 73%. What is the difference between the two
hit ratios, and why would we have such a significant difference between the two?

Any help or advice would be much appreciated.

View 5 Replies View Related

Cache Hit Ratio Over 100%

Sep 18, 2000

I have been seeing this strange statistics in one of our servers. The cache hit ratio has gone beyond 100%, it is currently showing 124%. Has anyone seen this before.

Thanks

View 1 Replies View Related

Cache Hit Ratio

Mar 18, 2005

I have a large dell server with 4 processors, and 8 gig of memory on win 2000 advanced with sql 2000 enterprise edition running a 3rd party app. My cache hit ratio averages about 76%. I thought the gneral rule was if you get below 80% to add more memory. However my memory manager shows I am only using 71% of my memory and have a full gig available. I have the sql server set to use about 7.1 gig of the 8 gig on the server. My ? is if I am only using 71% of my memory, will will adding more memory actually help my cache hit ratio

View 5 Replies View Related

Calculate Ratio : Urgent

Nov 14, 2007

I have to get the ratio like A:B with two calculated columns. A and B both are the counts of the rows.
Please help me with this, it's really urgent!

View 2 Replies View Related

Cache HIT Ratio Problem

Jul 23, 2005

HelloI am tring to figure out why our SQL server is a bit sluggish fromtime to time.It is running a dual XEON, with 2.5 GB RAM, and a fast SCSI I/O subsystem setup as follows.OS, mirrored 2 drivesSQL DATA 16 HDD RAID 10SQL LOG 4 HDD RAID 10SQL tempdb 4 HDD RAID 10OS = win 2003SQL = sql2000 standard editiondbcc showcontig shows me nothing special, it looks okI launch performacne monitor and add SQL server cache manager: hitratioand it is constantly at 7% and never changes up or down, it is justconstant.Can this be correct? and if so it sounds rather bad, we have ahandfull of large tables that are heavily used and enough RAM to holdthem all in RAM so I really do not understand why the cache hit ratiois not higher.Any hints would be greatrgdsMatt

View 9 Replies View Related

To Find Ratio Between Two Columns

Oct 16, 2007

Can anyone pls help me to solve my problem in SSRS. Also I wanted to plot the 'Ratio' in to a chart against the 'Year'.

The scenario is:

Year Amount_A Amount_B Ratio(Amount_A/Amount_B)
05 x y x/y
06 a b a/b
07 m n m/n

Its very needy.

View 9 Replies View Related

Transact SQL :: How To Calculate A Ratio

Oct 28, 2015

How do I author a SQL Query which meets the following requirement: Display a ratio of (instances where status is a success)/(instances where status is a success or instance is a failure). The below syntax is incorrect. However, I hope to beter explain what I am trying to accomplish using the incorrect SQL query below.

SELECT SUM(Case When Status = "SUCCESS")/SUM( Case When Status = "FALLBACK")
FROM ruleResults;

View 2 Replies View Related

Buffer Cache Hit Ratio...how To Measure?

Jan 2, 2004

Does anyone know how to measure the buffer cache hit ratio? I have been reading a lot about it but can't find this measurement in Performance Monitor.

Thanks

View 1 Replies View Related

Getting Reliable Information About Cache Hit Ratio

May 5, 2004

Maybe I am just a lot better at this than I thought, but I figure that somewhere there is a mathematical rule that is being overlooked. When I run dbcc sqlperf (lrustats) on some of my production machines, I sometimes end up with a cache hit ratio (which is defined as a percentage, mind you) that is slightly over the limit:

Statistic Value
-------------------------------- ------------------------
Cache Hit Ratio 100.00898
Cache Flushes 0.0
Free Page Scan (Avg) 0.0
Free Page Scan (Max) 0.0
Min Free Buffers 331.0
Cache Size 4362.0
Free Buffers 9434.0


I suspect some counter somewhere is getting wrapped around its 4 byte limit. Is there any reliable source for getting statisics about SQL Server performance? Users tend be unreliable and say everything is slow.

View 2 Replies View Related

T-SQL (SS2K8) :: Procedure Cache Hit Ratio

Jun 20, 2014

I'm putting together some monitor scripts, have buffer cache ratio etc etc but struggling to get an accurate script for the current procedure cache hit ratio...

View 1 Replies View Related

SQL 2012 :: Value Of Buffer Cache Hit Ratio

Jul 16, 2015

What is better values for Buffer Cache Hot Ratio in SQL Server 2012. Is the values changed from 2008r2 to 2012?

View 2 Replies View Related

100% Buffer Cache Hit Ratio Not Always Optimal?

Jun 19, 2008

I am wondering if 100% buffer cache hit ratio is considered not good in general?

Are there instances that it is actually bad and can contribute to server performance degradation?

Any thoughts on the topic most welcome :)


--------------------
keeping it simple...

View 11 Replies View Related

Creating Ratio From Summary Data

Dec 1, 2014

I am doing some work in microstrategy reports and using SQL data cube. I am creating a summary report (Counting on Unique ID) in MS where when I put in a particular attribute/column from cube it splits the data count down into 2 separate values that I have defined in the select case statement of the SQL query.

I want to work out a ratio of the count unique ID between these two values but can't do this in MS report as it is not a physical column but summary split of data.Is there a way I can do this in SQL? My summary looks something like this:

N E
====================== =========================
**Y** N **Y** N
========= ========== ========= =========
570 140 89 56

Where the Y/N field is the one I have split down against the N/E column. I want to work out ratio between the 2 "Y" fields but in SQL. Here is a SQL example snapshot of my data:

Unique_ID New/Existing Application Attend_Interview
========= ======================== ================
12554445 E Y
65766879 N N
53375654 N Y
44323224 E N
93656786 E Y

wondering if I might be able to do a procedure or similar or something more dynamic in SQL query?

View 1 Replies View Related

Troubleshooting SQL Buffer Cache Hit Ratio

Jul 23, 2005

This issue just happen recently. The buffer cache ratio went from > 90%to 50% and has slowly been climbing back up over 8 hours or so. Itscurrently @ 76%. Is this something I should take action on immediately?It seems to be coming back to normal...

View 1 Replies View Related

Estimation The Buffer Cache Hit Ratio

Sep 1, 2006

HiI have trouble with MSSQL2000 SP4 (without any hotfixes). During last twoweeks it start works anormally. After last optimalization (about few monthsago) it works good (fast, without blocks). Its buffer cache hit ratio wasabout 99.7-99.8. Last day it starts work slow, there was many blocks anddedlocks. There are no any queries, jobs and applications was added. Nowbuffer cache hit ratio oscilate about 95-98. I try update statistics andreindex some hard used tables, but there is no effect or effect is weryshort (after few hours problem return).Mayby somene know what it could be?Is it possible to estimate how each table (using DBCC SHOW_STATISTICS orDBCC SHOWCONTIG or others) how the table affect on total buffer cache hitratio?Marek---www.programowanieobiektowe.pl

View 1 Replies View Related

Buffer Catche Hit Ratio Counter Always Is Maximum (100). WHY ?

Sep 26, 2000

Hi,
In m*y SQL server 7.0,
when using Performance, I see in graph: Buffer Catche Hit Ratio Counter
(SQLServer Buffer Manager objects)always is maximum (100). What does this mean ? What's the Buffer Catche Hit Ratio?
How do I configure SQL server to performance?
Thanks in advance.

View 1 Replies View Related

DB Engine :: DMV To Check Buffer Hit Ratio On Several Servers

Oct 30, 2015

I'm collecting performance data via DMV to check the buffer hit ratio on several servers. Sometimes I get values high above 100% hit Ratio.

Example (50050%):

SQLServer:Buffer Manager:Buffer cache hit ratio 2002
SQLServer:Buffer Manager:Buffer cache hit ratio base 4

View 11 Replies View Related

SQL Server Admin 2014 :: How To Test Random / Sequential Ratio

Aug 31, 2015

I am checking some ratio numbers for our system engineers, those are

Read/write ratio?
Random/sequential ratio?
Read/write block size?

For Read/write ratio, I am using below query,

SELECT
m.type_desc
, CEILING(sum(num_of_bytes_read*1.0) / (sum(num_of_bytes_read*1.0) + sum(num_of_bytes_written*1.0)) * 100) AS 'Read %'
, CAST((sum(v.size_on_disk_bytes) / 1024.0 / 1024 / 1024) AS MONEY) AS 'FileSizeGB'

[Code] ....

Random/sequential ratio, I googled but cannot find a similar query to get the result?

View 1 Replies View Related

Reporting Services :: SSRS - Show Percentage Or Ratio In Stacked Bar

Nov 20, 2015

Sample Query :
SELECT 'A' [Type], 1 AS [NUMERATOR]     , 2 AS [DENOMINATOR]
UNION ALL
SELECT 'B' [Type], 2 AS [NUMERATOR]     , 4 AS [DENOMINATOR]
UNION ALL
SELECT 'C' [Type],  3 AS [NUMERATOR]     , 6 AS [DENOMINATOR]

[Code] ...

I need to show the ratios beside each bar (shown in red) as shown in the above diagram.

View 2 Replies View Related

Analysis :: Stored Procedure Ratio To Parent Truncating Data

Jun 11, 2015

I'm trying to use the Ratio to Parent sproc - [URL] .... The problem is that the stored proc appears to be pegged at 4 decimal places (which translates to ##.##%). This is losing precision and isn't adding up to 100% when the ratios are summed individually.

Can this be fixed within SSAS or will i need to modify the assp code? .NET decimals are meant to automagically scale, so not sure why it's coming out as (5,4) but my .net coding knowledge is fairly poor.I get the correct behaviour if hardcode a ratio to parent MDX calc (the resulting ratio has as many decimal points as are required)

View 3 Replies View Related

Power Pivot :: Sum For Events And Time - Calculate Total Ratio

May 22, 2015

I created a PowerPivot measure which is a ratio Ratio = Number of Events/Time calculated on 12 months. I would like the grand total to be this Rate Sum(Number Of Events)/Sum(Time calculated).

In my Pivot I have one measure which I called Value and this value can have different types depending on one attribute.For instance one attribute is number of events, an other one is time and the third one is ratio.I want to display a custom grand total simple sum for events and time and a calculation of my ratio for ratio.

For instance
                            201501 201502 201503 TOTAL
Number of events           8         10        10     28
Time                             5           5         
4     14
Ratio                            8/5       10/5     10/4  28/14

View 3 Replies View Related

SQL 2012 :: DB Engine Page Life Expectancy And Buffer Cache Hit Ratio Is Too Low

Sep 12, 2014

Our server administrator forwarded some messages from SCOM that indicate:

SQL DB Engine 2012 Page Life Expectancy and Buffer Cache Hit Ratio is too low

When I logged into the offending server, I could not find anything in the SQL Log File that indicates this.

I was wondering how did SCOM identify this issue - where in SQL Server would this have been reported to SCOM?

View 3 Replies View Related

MDX - With Member

Apr 15, 2004

for example

with member [Measures].[Accumulated Sales] as 'Sum(YTD(),[Measures].[Store Sales])'
select
{[Measures].[Store Sales],[Measures].[Accumulated Sales]} on columns,
{Descendants([Time].[1997],[Time].[Month])} on rows
from Sales



I need more then 1 member how to do in


Thanks

View 4 Replies View Related

Hi From A New Member

May 8, 2008

Hi. I am Rabbee from Bangladesh who has join recently with the team. I need a Solution. How can I find out Duplicate data in sql server 200 database by generating a query. Thank you.

fazle rabbee

View 4 Replies View Related

Best Practice For Data Size/Log Size Ratio

Jul 23, 2005

Just wanted to know what is a general rule of thumb when determining log file space against a database's data file.We allow our data file for our database to grow 10%, unlimited. We do not allow our log file to autogrow due to a specific and poorly written process (which we are in a three month process of remove) that can balloon the log file size.Should it be 10% of the Data file, i.e. if the Date file size is 800MB the log file should be 8MB?I realize there are a myraid of factors that go against file size but a general starting point would be nice.ThanksJeff--Message posted via http://www.sqlmonster.com

View 4 Replies View Related

SQL Member Rank

May 13, 2008

ive created a photo sharing site, and im trying to show the statisics for certain users, so far i can COUNT all the photos the user has upload, and show them in descending order, but i need to be able to see the ranking of the user, like :

RANK USERID No. Of Photos

1 10 100
2 8 70
3 9 85


is there anyway of doing this? thanks Si!

View 7 Replies View Related

Getting The Last Member Of As400

Mar 14, 2004

Hi,

I need some help here. I am trying to do a DTS from As400 from SQL Server 2000. However, the file that I require have members. IF I just do a select statement, it will retrieve the earliest member.


Does anyone know how I can access the latest member?

Thanks a lot!

View 3 Replies View Related

How To Hide A Member?

Jan 14, 2005

I have a dimension named Products with the following hierarchy:

Type
Sub Type
Product

Due to requirements I want that a particular product, whose name is, say, "XYZ" should not be displayed when the user drills down to the lowest level i.e. the Product level.

I tried playing with the Advanced Properties tab but could not make any progress and thus will be grateful for help.

Thanks.

View 1 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved