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!
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.
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!
I'm trying to create a percentile rank function based on the standard WIKI version:
I've seen Brian Knight's article here, but that only deals with percentile.
Where I'm struggling is getting the count of members in a set using a measure, in the current context on the same hierarchy, as the filter expression. I'm using the comparative set as in belonging to the same geographical location, and therefore associating by another attribute.
So, cl as below:
MEMBER [Measures].[RegionPercentileCount] AS
Count(
Filter(
NonEmpty(
descendants(Ancestor(
[Supplier].[NameMap].CurrentMember,
[Supplier].[NameMap].[Region]),
[Supplier].[NameMap].[Supplier Id]),
[Measures].[ActiveMeasure])
, [Measures].[ActiveMeasure] <
([Supplier].[NameMap].CurrentMember,
[Measures].[ActiveMeasure])))
Using the same measure and context hierarchy is always going to be equal, and therefore the count is always zero. Its almost as if I need a nested context for the FILTER which allows me to use enumerate the set on the same hierarchy whilst maintaining the external reference.Â
I'm thinking that perhaps I'm going to have to create another hierarchy and use that as the filter set and reference through StrToMember or similar.
We have a fact table where one of the measures in that fact table is invoice balance. Some of our reports requirements list that they only want to see the sum of those invoices where the balance is greater than X dollars. How do I go about filtering calculations based on a measures value?
View 3 Replies View RelatedSSAS 2008 R2
Is it possible to filter out a measure only at the intersection of Two dimension members? I have a date dimension, Â a Hospital dimension and a wait time measure.
For Example, is it possible to filter out Wait time for Bayside Hospital for the Month of June 2015?
I want Wait time to continue to be displayed for all other months and roll up into the totals without the filtered value.
One of my models has order data, cost per order/invoice ID and then dimensions on Fiscal Year, category, etc...the usual.
A user wanted to search it for an exact order amount. (They knew for example that one of our accounts was not balancing by single order worth $746.13 and assumed it must be an order that was placed but never marked shipped that slipped through the cracks).
Now, in the model I have "order amount" as a field and then a measure that sums that.
I could expose that "order amount" field as a label and let them filter on it in Excel (and that works).
However, I haven't had any luck filtering on the actual measure "Total Order Amount". Such as OrderID-> View Filter -> "Total Order Amount" equals 746.13.
I assume this is due to a few things:
Measure calculates at different levels so filtering on a measure is difficult as you would have to place all the "slicers" and set them first before the measure would "exist" at a level where it could be $746.13. Orders by year would have $746.13 as part of it's year sum, but wouldn't exist as a stand alone line item orders by year 2015 might be 2 million.Â
Orders by category might exist at 500,000, 8,000, 15,146.36, etc... but not $746.13.
So I would need OrderID on there as a column so the measure could return at the value of $746.13 for one row for it to match the filter?
Basically:
1. Why it can't really filter on a measure?
2. Is there a better way to accomplish this other than exposing the actual column in the fact table "order amount" as it feels like that could cause all kinds of confusion if other users try to slice/filter on that not realizing exactly what it is meant to be?
Any way to create a measure that filters the second column to mimic the behavior of only filtering for slicer for the Open Pool Date values as per the image below. Ultimately, I need to create a measure that only includes accounts that were opened 6 months prior to the month row context.
Trying to get the values in the second column to only include aging accounts 6 months prior instead of 12 months prior.
Trailing6Month Conversion:=CALCULATE([TOTAL LTD Converted Amount] ,Filter(Settlement700,Settlement700[OpenDatePool]>=RELATED(DimDate[Trailing6MonthsEnd])))
Trailing6Month LTD Conversion Amount:=CALCULATE([Trailing6Month Conversion],DATESINPERIOD(DimDate[FullDate],LASTDATE(DimDate[FullDate]),-6,Month))
The user wants to be able, using excel, to apply a filter to all measures in every measure group. I though that I can create a dimension with a single level with two members, let´s say "on" and "off" and depending on the selected member and using an IIF statement decide which formula applies to the calculated measures.
I have serious doubts about the performance and for this technique because I am thinking as a .Net developer and not as a cube developer. Maybe it is better to resolve it scoping the measures but I cannot figure it out.
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.
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
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 RelatedSo 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?
Hello,
Here is my problem:
I use SQL Server 2005. I have approx. 50 tables in my database and 30 of them have a filed named "CompanyID". Example:
create table A (ID int identity, NAME varchar(100), COMPANYID int)create table A (ID int identity, REF_ID int, FIELD1 varchar(100), FIELD2 varchar(100), COMPANYID int)
Also there are nearly 200 stored procedures that read data from these tables. Example:
create procedure ABCasbegin /* some checks and expressions here ... */ select ... from A inner join B on (A.ID = B.REF_ID) where ... /* ... */end;
All my queries in the Stored procedure does not filter the tables by CompanyID, so they process the entire data.
However, now we have a requirement to separate the data for each company. That means that we have to put a filter by CompanyID to each of those 20 tables in each query where the tables appear.
Firstly, I put the CompanyID in the context so now its value is accessible through the context_info() function. Thus I do not need now to pass it as a parameter to the stored procedures.
However, I don't know what is the easiest and fastest way to filter the tables. Example:
I modified the above mentioned procedure in the following way:
create procedure ABCasbegin /* some checks and expressions here ... */
-- gets the CompanyID from the context: DECLARE @CompanyID int; SELECT @CompanyID = CONVERT(float, CONVERT(varchar(128), context_info()))
select ... from A inner join B on (A.ID = B.REF_ID) where ...
and A.COMPANYID = @CompanyID and B.COMPANYID = @CompanyID /* ... */end;
Now I have the desired filter by CompanyID. However, modifying over 200 stored procedures is rather tedious work and I don't think that this is the best approach. Is there any functionality in SQL Server that can provide the possibility to put an automatic filter to the tables.
For example: when I wrote "SELECT * FROM A", the actual statements to be executed would be "SELECT * FROM A WHERE CompanyID = CONVERT(float, CONVERT(varchar(128), context_info()))".
I was looking for something like "INSTEAD OF SELECT" triggers but I didn't manage to find any answer.
I would very grateful is someone suggests a solution for something like "global table filter" (that will help me make an easy refactoring)?
Thanks in advance.
Best regards,
Beroetz
Howdy,
I have a table that has a group. In this group, I want to filter by 2 different expressions, concatenated with an OR. BUT I can't change the "And/Or" column value for the first entry because it is grayed out. The column will automatically change to an OR value if both my expression column fields are the same (which I don€™t want) but if I put any other value in to the expression field of the second row, the "And/Or" field of the first row automatically changes to an AND.
PLEASE! How do I get the And/Or field "ungrayed" so I can change it to what I want?
The 2 filters I and using check the UserID = to the user, and the other is checking a count to get the Top N 1. (So just showing the current user and the top producer)
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
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.
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
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
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?
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 RelatedI 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!
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.
Hi, everyone:
Does anyone have the idea how to measue the transaction log speed? Do I need a special tool?
Thanks.
ZYT
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
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 RelatedI 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
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 RelatedHi,
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.
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
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.
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?
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