Dynamic Measure?

May 31, 2004

Hi,
I am newbie in Analysis services. My problem is that I have to show a yearly report for credit expiry. In my fact table the measure is credit rate.
I have attributes of start_day_key and end_day_key. Now when I go through analysis services it does not let me create a measure so that I can count end_day_key for a year (end_day_key is linked to day table with daykey. It has date) and show. The thing is the measure I want is not stored (i.e: the count of end days for a year ). How do I go about it?

Should I create a calculated cell? (I tried that, but mdx does not work)
I used : Count({[Measures].[End Key]})< 366
Now this is not wha I want, I want the end_key count by matching date in day dimension for the current year? Any help is greatly appreciated.

Mdx

View 1 Replies


ADVERTISEMENT

Analysis :: Dynamic Calculated Measure In MDX

May 8, 2015

 I have a dimension called as DIM1 which has list of all measures and has an attribute called as ATTRMDX Formula. The formula will be like

([DIM1].[ATTR Measure Code].&[M1],[Measures].[ATTR MEASURE VALUE])+([DIM1].[ATTR Measure Code].&[M2],[Measures].[ATTR MEASURE VALUE]).

I want to pass this formula to a calculated measure as given below -

MEMBER [Measures].[FormMeasure] as ([Measure].[ATTRMDX Formula].currentmember.MEMBERVALUE)

but I get the string value itself as output, but when I put the formula as a string in the calculated measure I obtain the value.

View 2 Replies View Related

Analysis :: Dynamic Measure Value Based Upon Role / Department

Sep 28, 2015

I have an issue related to SSAS security. We have an SSAS multidimensional cube which needs 3 types of security:

- Access to the entire cube => OK, based upon a role
- Restricted access to one department (= dimension) => OK, based upon a role
- Access to the entire cube, but with dynamic security on 2 measures.

Let's say, we have 2 departments (food and non-food). Users within food are allowed to see sales and pieces from the food department, but not from the non-food department. 

It is not an option to restrict access to the non-food department because there are other measure which they have access to. I tried cell security, but this is very slow and generates multiple empty rows on my selections.

View 3 Replies View Related

Analysis :: Calculated Measure To Check Measure Value Updates By Month?

May 13, 2015

In my cube there are two measures which are used in different calculations.Now I'm need to show in report if there any months in data when both or even another one of the measures is not updated (value = 0 or NULL).

how should I create the calculated measure for that?

I have tried in mgmt studio to plan this but I'm in a loop of errors.

View 4 Replies View Related

How To Change The Column Measure Into Row Measure In Reporting Services

Sep 25, 2007



Hi,

I am wondering how to create a matrix that contains 1 dimension for Top Label (Column), let's say "Year-Month"
and then 2 Measure to be in the row format rather than columnar format.
Example as below :
Year-Month on the column, and the measure is on the row :









2007-04
2007-05
2007-06

Amount Sales
1000
2000
3000

Unit Sales
10
20
30

Total
1010
2020
3030



Please share with me if you have this solution in Reporting services as it works in excel, hyperion brio, bo, cognos but somehow cannot see that function in Reporting Services.

Thanks
best regards,
Tanipar

View 1 Replies View Related

SQL 2012 :: Add Calculated Measure As Child Measure

Sep 15, 2015

In SSAS, I want to add a calculated measure and set that measure as the Parent of existing measure. For example I have the measures as A and B from the fact table. Now i want to add a calculated measure as C and set this measure as Parent for measures A and B. How to do that in SSAS.

View 4 Replies View Related

Analysis :: MDX Filter A Measure Based On Another Measure

May 3, 2015

I want to filter a measure based on another measure (both are measures on the same FACT table).Distinct Number of Users HAVING.User Cost above 0.

I tried doing having but because it’s two measures and not a tuple with a dimension it writes an eror.My query is something like:

SELECT
  [Measures].[Distinct Number of Users ]
having [Measures].[User Cost] >0
 ON
COLUMNS,

[code]....
 
Please note, that I want it in a MDX query not needing to change the cube or DWH table.

View 4 Replies View Related

MSOLAP To Do SUM(measure A) /SUM (measure B) In Different Levels

May 17, 2001

So far we are happy with MS OLAP 2000 service.

In the past we did cubes with aggregation method using summary.

MS OLAP server allows aggregate functions (Sum, Min, Max, Count, and Distinct Count).

But now the customer wants to know percentage of SUM(measure A) /SUM (measure B) in different levels and dimension combination.

Any idea?

View 1 Replies View Related

Percentage Measure

Sep 14, 2004

Hi,

easy question for advanced users:

I have a dimension called "Movement rate". The members are "1" to "6".
There is a measure called "No. of Products".

What i easily like to do is showing the percentages "No. of Product" for each "Movement rate" of the amount of "No. of Products" without using the frontend-tool.

Thx in advance for any help.

Andreas

View 2 Replies View Related

Dimenstion On Measure

Dec 2, 2005

There is count measure in the cube. I need to create a dimension that should list the range of values based on that count values:
Exp:

0-99
100-199
200-399
...
....
..
and so on.
User wants to pick the any range level in the dimension and cube should show corresponding cell values of count measure which would fall into range of the dimension level value.
For example:
if user picks up level 100-199 then cube would filter cell values of measure that has a value between 100 and 199 within 12 month range. So, it would be year to date value of the measure. My problem is how go about creating Dimension that based on measure in the cube and dimension should have level with range of measure values.
Any hint would be appreciated greatly.
Thank You.

View 4 Replies View Related

Filter A Measure With MDX

Dec 27, 2005

HI all friends


I have a Fact table like this



DIMENSSIONSMEASURES
------------------------------
idempidprodcutsalessalesws




I just want make the next query with MDX


SELECT sales
FROM myFactTable
WHERE salesws > 0


Note that salesws is a measure not a dimmension


How it would be in MDX? I need a lot of help


Regards!

View 4 Replies View Related

Total Sum Of A Measure

Apr 13, 2004

Hi,

for a calculated measure I need the total sum of a measure in different dimension. So for example I want to get in my first dimesion 1500 as my sum and in my second dimension too. Is there a way to get it?
(FirstDimension.CurrentMember.Parent, [Measures].myMeasure) doesn´t work!

Best regards,
Thorsten

View 11 Replies View Related

Filter Measure

Jan 18, 2007

I have a delima.



I have a dataset that needs to return "Fatalities Involving drivers age 15-17". For years 2003 , 2004, 2005.

So i set up 3 different filters: Year : 2003,2004,2005

Person type: Driver

Age : 15,16,17



Now this is fine for getting a crash count and fatalities (for teen drivers). But i want the fatalities for everyone ( All Person Types) and (All Ages) ...how do i go about this. All i get right now, is the fatalities for Drivers 15-17.



Please help..im confused!

View 5 Replies View Related

How To Measure Performance?

Mar 6, 2008

I need to create one or two tables in the database. There are a few ways I can design these tables, so I€™d like to measure data loading performance. My primary concern is level of IO generated by SELECT. I don€™t expect CPU utilization to have any meaningful impact, but it would be nice to know how to measure processor utilization as well.
PerformanceMonitor application is not really an option, because it measures IO and CPU at SQL Server process level.
My development box is not going to match production boxes, so I would like to measure IO in terms of amount of data loaded from disks instead of timing stored procedure execution. The same is true for stored procedure CPU utilization.
It is my understanding that SQL Server keeps table content loaded in memory as much as possible to improve performance. How do I negate effect of caching on my IO numbers?

View 5 Replies View Related

Measure The Table Length

Dec 28, 1999

I have created a table in the sql server 7.0 and I wanted to know the what is the size of the table, is there any storeprocedure or command that can be used to measure the space used by the table in the database

View 2 Replies View Related

Comparing 2 Fields As A Measure?

Sep 1, 2000

I need to build a cube for membership over time. Active membership is the gap from the record's CreationDate to SuspendedDate, as compared to a reference date (today's active members have CreationDates before today, and SuspendedDates > today, or NULL).

Any idea how to build a cube so I can count membership over a Time dimension? The only approach I've been able to come up with is to create a table with a record for each active member on each date (brute force).

AtDhVaAnNkCsE!

View 1 Replies View Related

Calculation From Measure && Dimension, Please Help !!

Dec 16, 2005

Hello

I am a newbye with Analysis Services and am desperately trying to find a way to include a calculation between one of my measures (Teus), divided by the vessel capacity, where vessel is one of my dimensions (and is therefore not depended on other dimensions...)

Any ideas how I could implement that ?? This would help a lot, thanks for your help,

Aurore Bui.

View 2 Replies View Related

How To Measure The Transaction Speed?

Apr 2, 2004

Hi, everyone:

Does anyone have the idea how to measue the transaction log speed? Do I need a special tool?

Thanks.

ZYT

View 3 Replies View Related

Measure Displayed Without Aggregation ?

Sep 27, 2007

Hello,

I have a Sales cube and I want to be able to display Products and the
Price at which they were purchased in a given period. I have a
Product dimension while Price is a measure in my Sales Fact Table. Is there a way to have a "Group By" aggregation instead of a Sum? This way, I can show Products grouped by their list price.

My Product Dimension consists of product_numbers (such as 100, 101,
102 etc...)
My Sales Fact Table consists of sales data (such as product_key,
price, net_sales, returns, etc...) at the transaction level.


I want to be able to view the data like this:


Price Net_sales Returns
Product
100 $5.99 $2005 $320
101 $3.51 $7110 $998


where Net_sales and Returns are "summed" and Price is simply a "Group
By". In other words, this report would show the net sales and returns
by product for a given price.


I'd rather not use a Price dimension since we have hundreds of
products at hundreds of different prices. Moreover, this data is
already in the Sales Fact table.


Thanks for any help provided

View 1 Replies View Related

Replication Performance - How To Measure It ?

Aug 24, 2006

Hello,could you please advice on how to measure replication performancein Oracle, DB2 & MS SQL Server RDBMS installed in Windows servers ?I've got two servers with databases installed and configured,I prepared set of data using DBGEN from TPC and I already imported theminto databases.Also, I configured the replication.Now I have to do a test with a few kind of replications methodimplemented in these RDMBS, but I don't know which tool or reports or"v$iews" should I use to measure replication performance.The replication is configured only between the same RDBMS, I meanOracle <-Oracle, DB2 <-DB2 and MSSQL<-MSSQL.Most of applications are great for checking performance of local DB,not for replicated/distributed.I've found description of CA Unicenter Database Performance Managementfor distributed RDMBS, and I think it could be the right one, but Ican't find any demo or trial version :(Could you please advice any place to download it, or other application,script, description, just whatever.Perhaps just any other idea how to check the replication mechanismefficiency ?Regards,Mark

View 5 Replies View Related

Analysis :: Get Measure Value By Different Condition

Jul 17, 2015

I am very new to MDX. I have the sales amount measure and date dimension.

I need two measure sales value for the below condition. I am going to display this result in SSRS.

1. For between from date and todate
2. For last week range

View 2 Replies View Related

Analysis :: MDX - More Than 1 Measure On Output

Nov 12, 2015

Any example on how to output more than one measure in an MDX query. Lets say I want to display stdev(x) and average(x) (or any other two measures)?

View 7 Replies View Related

Importing Excel Sheet Which Have Dynamic Column Name And Dynamic Number Of Columns

Aug 25, 2007

Hi Craig/Kamal,

I got your email address from your web cast. I really enjoyed the web cast and found it to be
very informative.

Our company is planning to use SSIS (VS 2005 / SQL Server 2005). I have a quick question
regarding the product. I have looked for the information on the web, but was not able to find
relevant information.

We are getting Source data from two of our client in the form of Excel Sheet. These Excel sheets
Are generated using reporting services. On examining the excel sheet, I found out that the name
Of the columns contain data itself, so the names are not static such as Jan 2007 Sales, Feb 2007 Sales etc etc.
And even the number of columns are not static. It depends upon the range of date selected by the user.

I wanted to know, if there is a way to import Excel sheet using Integration Services by defining the position
Of column, instead of column name and I am not sure if there is a way for me to import excel with dynamic
Number of columns.

Your help in this respect is highly appreciated!

Thanks,


Hi Anthony, I am glad the Web cast was helpful.

Kamal and I have both moved on to other teams in MSFT and I am a little rusty in that area, though in general dynamic numbers of columns in any format is always tricky. I am just assuming its not feasible for you to try and get the source for SSIS a little closer to home, e.g. rather than using Excel output from Reporting Services, use the same/some form of the query/data source that RS is using.

I suggest you post a question on the SSIS forum on MSDN and you should get some good answers.
http://forums.microsoft.com/msdn/showforum.aspx?forumid=80&siteid=1
http://forums.microsoft.com/msdn/showforum.aspx?forumid=80&siteid=1

Thanks



Craig Guyer
SQL Server Reporting Services

View 12 Replies View Related

SSRS 2005 - Email Report On Execution To Dynamic List With Dynamic Parameters = No Schedule

Nov 23, 2007

Hi,
I have a need to display on screen AND email a pdf report to email addresses specified at run time, executing the report with a parameter specified by the user. I have looked into data driven subscriptions, but it seems this is based on scheduling. Unfortunately for the majority of the project I will only have access to SQL 2005 Standard Edition (Production system is Enterprise), so I cannot investigate thoroughly.

So, is this possible using data driven subscriptions? Scenario is:

1. User enters parameter used for query, as well as email addresses.
2. Report is generated and displayed on screen.
3. Report is emailed to addresses specified by user.

Any tips on how to get this working?

Thanks

Mark Smith

View 3 Replies View Related

How To Create A Measure With Count Function

Jan 22, 2005

Hi,
i created a cube that has 2 measures. I created the measures by selecting the columns from my fact table, but the function that applied in the measures was the sum function. I need to apply the count function in my measure. How can i do that?

Thanks in advance.

View 2 Replies View Related

Problems With Aggregation Of Distinctcount-measure

Mar 26, 2005

Hi Experts,

I have a problem:
I have various cube packed in a virtual cube to give me distinctCount Values of customers who purchased products (DC customer] and purchased products (DC product) along with Saleries [RW] over numerous dimensions such as productgroups customergroups etc.
In the calculated measure [RW kum] I can sum the the salaries from the beginning of the year until [time].currentmember with the following MDX-Code:
With member [Measures].[RW kum] As 'Sum(Periodstodate([time].[year]).[Measures].[RW])'
Select {[Measures].[RW],[Measures].[RW kum], [Measures].[DC customer]} on rows,
[Productgroup].[Itemgroup].Allmembers on columns
From [vcub_Sales]
Where [Time].[2005].[march]

IF I use this PeriodsToDate-Formular with the the measure DC customer I will get the sum of distinctcounts of the periods.

What I want is the distinct count of all customers who have purchased since the beginning of the Year until the [Time].currentmember in the same Level.

Given this problem, do I..
1. have make another Cube or
2. another time dimension or
3. is there a way in MDX to get the result?

I would appreciate any replies.

Zannadar

View 1 Replies View Related

Calculating From Measure And Dimension Attribute, Please Help !!!

Dec 19, 2005

Hello

I am a newbye with Analysis Services and am desperately trying to find a way to include a calculation between one of my measures (Teus), divided by the vessel capacity, where vessel is one of my dimensions (and is therefore not depended on other dimensions...)

Any ideas how I could implement that ?? This would help a lot, thanks for your help,

Aurore Bui.

View 2 Replies View Related

MDX Statement - How To Select Last Changed Value Of A Measure

Jun 4, 2012

I am hopelessly lost on a MDX-Statement. We are trying to select the last changed value of a measure within the last 30 days and compare this to the current measure value to see wether this has increased, decreased or not changed at all.

We have basically 2 Dimensions (Timeline and Opportunity) and 1 Fact (Revenue). As already described we now want to evaluated, whether revenue has changed within the last 30 days. The result will be displayed in a SSRS-Report (don't have a problem here).

"All" I need to select via MDX, would be the last changed value and the current value. I tried to use the KPI function, but unfortunately to no avail.

How to do such a select in MDX?

View 1 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

Calculated Measure (Previous Act Value) Problem

Mar 1, 2004

G'Day all,

I am having trouble with a calculation that on the surface seems to be working until it is opened up in excel.

The measure calculates the Actual Value as of last year and is as follows - ([Measures].[Act Val],ParallelPeriod([Time].[Year],1))

This works fine until I look at the totals within EXCEL 2002/2003

1)If I choose to view a whole year then the total shows me a whole year. That is fine and as expected.

2)If I choose part of the year the total shows me the whole year. This is wrong and am wondering if it is an Excel probem or my calculation? But here is the weird part -

3)If I choose another year and select the same months as I have already chosen (ie Year 20023 Jan, Feb and Year 2003 Jan and Feb) then the total for the year that is already selected works!!(shows only the total of the two months - whereas the extra year I have selected (remember - Jan Feb only) shows me a total for the whole year.

WAIT - There's more

4)If I add another month to the 2002 selection (ie add March) then the total for 2003 (Jan Feb only) includes the March value also (even though it is not selected)!!!!!!

Any Ideas??

Does it make sense??

Thanks

Matt

View 3 Replies View Related

Calculated Measure From 2 Fact Tables

Dec 6, 2007

I have a cube with 2 fact tables and I would like to create a calculated measure that uses values from both tables like:

fact1.measure1 / fact2.measure2

I can't see how to do this?

View 1 Replies View Related

Undersatnding Of ShoWPLAN_DATA Measure Estimated I/O

May 27, 2008

Hi,
I would like what represente the value of Estimated I/O . Is that estiamted number pages or somethings else?

Thanks for help.

Tietie

View 6 Replies View Related

Analysis :: How To Get Unique Values For Each Measure

Sep 2, 2015

I am getting same value all over how to get unique values for each measures.

how to resolve it.

View 5 Replies View Related







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