The result is accurate but the query execution time is 3-4 minutes for 10 fact records, when i use multiple dimension. it is showing me 0 valus for this measure for all the members for the dimesion attribute which doen't have any customer order. example it shows all the member of date dimension. is there any way to reduce the rows. i think this is the reason to take more execution time.when i use EXCCLUDEEMPTY the result is NULL
i am currently trying to build a distinct count on my cube (mssql2005 analysis services).But after i added the discount count on the field i want to and start the processing, the following errors appear.Errors in the OLAP storage engine: The sort order specified for distinct count records is incorrect.
set buyerset as exists(dimcustomer.leval02.allmembers,custoertypeisRetailers,"Sales") set saleset(buyerset) set custdimensionfilter as {custdimensionmemb1,custdimensionmemb2,custdimensionmemb3,custdimensionmemb4} set finalset as exists(salest,custdimensionfilter,"Sales") Set ProdIP as dimproduct.dimproduct.prod1 set Othersset as (cyears,ProdIP) (exists(([FINALSET],Othersset,dimension2.dimension2.item3),[DimCustomerBuyer].[ParentPostalCode].currentmember, "factsales")).count
Background: I have a Huge fact dimension table(table has both measures and dimensions) that i am using to build a SSAS Cube.
The table didn't have a unique identifier, so the database team added ROW NUM as a column to the table which i am using as a PrimaryKey in my Cube build. I was able to create a cube successfully with it without any issues.
Problem: Now customers are asking for a 'Claim Count calculation' which shows the Distinct Claim Count.
Its defined as below :
Count(Distinct Claim_Number || Claim Year || Claim Month)
. All the 3 columns are available in table, but when i am trying to create this Count Distinct Object in the DSV the Cube processing time increased by 5 times, as now i have to use a GroupBy function in my SQL.(There are around 30 columns to group by).
Is there a better way to achieve this Count(Distinct Claim_Number || Claim Year || Claim Month) without using groupBy in DSV SQL logic? I cant seem to find any Count(Distinct) function in the Cube Calculation functions?
Which works fine for all my other calculations except this, where I need a distinct count. CNT is a calculated measure. The browser would look like this:
Category Year1 Year2 ..... MDX what I have now MDX what I need
I am doing a distinct count on a related table's column, but get an out of memory error if I run it for the entire table (works great for just a few rows when filtered down).The error I get is: "We couldn't get data from the external source.The operation has been cancelled because there is not enough memory available for the application. If using 32-bit version of the product consider upgrading.
I know I can add a related column and that works fine...but that seems to me like I've defeated the purpose, I have a good and proper lookup table, and should be able to run my query against its relationship.Here is the query and details below *Note I supplied a scaled down sample, on my actual model I receive these errors, not in the sample as it has only a few rows
List Workers Distinct Project Customers:=CALCULATE(DISTINCTCOUNT(Projects[CustomerID]),'WorkersToProjects') Other measure which returns no errors, but included for completeness: List Workers Projects:=CALCULATE(DISTINCTCOUNT([ProjectID]),ISBLANK(WorkersToProjects[ProjectID])=FALSE())
My goal here is to allow the user to view the workers assigned to a project, but also get counts of the workers assigned to the CUSTOMER of a project. For example, suppose we lose a customer, we want to see how many workers would be impacted by that, so a count of projects per worker is not useful there, we need to see a count of workers per project's customer (owner of project whom project work is being done for)The question being: How can I accomplish this:
1. WITHOUT adding a calculated column to WorkersToProjects (of Projects.CustomerID) 2. WITH better performance?
There must be a better way to write this DAX to still get the correct answer?*Pic of pivot table, again, the numbers are accurate but the formula used to List Workers Distinct Project Customers measure does NOT scale :( 3 count for red , the number of Projects John has and 2 count for blue, the unique customers/owners of those projects "Veridian Dynamics" and "Massive Dynamic". URL....
Hello All,I've got a stored proc that I can't change that creates a quite largedataset and takes in 5 parameters. I need to be able to have each ofthe parameters selectable from a drop down box so that as you gothrough the list of parameters you restrict the results. I have itable to load the parameters but I end up with this:Location-LONDONLONDONLONDONNEW YORKNEW YORKNEW YORKNEW YORKTORONTOTORONTO....Any idea how i can filter the list of locations by distinct? fromwithin SQL Reporting Services. I tried a temp tbl in query analyserand that works but SQL reporting services doesn't like it.
I HAVE A SELECT STATEMENT WITH TEACHERS AND STUDENTS AND SOMETHING ELSE TOO. FOR EACH TEACHER I ONLY NEED ONE(FIRST ONE) STUDENT. HOW THE STATEMENT SHOULD BE?
I need to conditionally include a filter in the below DAX query such a way that if an Account_ID is passed (as client side parameter) the filter should be included in the query otherwise if it is zero, the query should return results across all account. For example, if total purchase is queried for Account_ID=2 the query should be like this:
I am trying to get a list of years from a Date dimension that includes a limited number of values. I want the current year and the previous 5 years. The year number in the cube is actually a char(4) value so I will have to cast that.
Also I want to alias the field, since Im using this list to use as a parameter in an SSRS report.
The MDX I have at the moment is this:<br/> WITH MEMBER [SALESYEAR] AS CInt([DimCalendar].[Year].CURRENTMEMBER)
SELECT NULL ON COLUMNS,FILTER( [DimCalendar].[Year].MEMBERS , [SALESYEAR] > 2012) ON ROWS FROM [MySalescube]
The above returns empty, but I also need to define an expression that is the current year - 5 years
For example, I have a Date dimension with attributes like Current Day and Current Month. If I run the following, I get exactly what I expect: a list of the days in the current month.
select NULL on 0, [Case - Date - PSPT Entry].[Year - Quarter - Month].[Date] on 1 from [Customer Support] where [Case - Date - PSPT Entry].[Current Month].&[True]
When I run the following, I'm getting a list of the days in the current month *plus the first couple days of the next month*. with
set [Days of Interest] as filter([Case - Date - PSPT Entry].[Year - Quarter - Month].[Date], [Case - Date - PSPT Entry].[Current Month].&[True]) select NULL on 0, [Days of Interest] on 1 from [Customer Support]
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.
I have an existing MDX query returning the correct resultset. However, I want to add a filter so that for a combination of value in Hierarchy1 and Hierarchy2, the data is filtered out.
For example I have data like
H1 H2 Amount 1 1 100 2 1 50 3 1 45
I am getting a value of 100+50+45=195.
I want to filter the data for the combination H1=3 and H2=1. Expected result would be 100...
I have been living in the T-SQL world for years, and am working on my first Tabular model.
I have a fact table that I would like to use for many reports. My idea is that I would use Perspectives in the tabular model to pull in the columns that I need for each purpose. In some cases, I also need to filter out some rows. Is there a way to filter perspectives? The only solution I am coming up with is to create views in SQL Server and import the fact table multiple times for each purpose. That doesn't seem very efficient.
I have a big table with several types of transsactions: PO (Puchase Orders, SO,( Sales orders), INV (Invoices) .... I want to create cubes with only one type of transactions (1 cube for PO,...)
Where and how can I filter the rows I want to use in my cube ?
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.
I am pretty new to MDX and am having trouble getting what I need out of this MDX query. Some business rules:
Gross Amount applies to all clients, whether Type A or Type B. I always want to return Gross Amount.Some clients are Type A, some are Type B, some are both, and some are neither.There are Type A Net Amount and Type B Net Amount values for all clients, but I only want to display the Type A Net value if the client is a Type A client, only Type B if the client is a Type B, or both for both, and neither for neither. I would like to return blank/null, not $0.00, for those values that should not be displayed.
Here's the basic query.
SELECT { [Measures].[Gross Amount], [Measures].[Type A Net Amount], [Measures].[Type B Net Amount] } ON COLUMNS, NON EMPTY {[Dim Client].[Parent Client Code].[Parent Client Code] * [Dim Client].[Child Client Code].[Child Client Code] * [Dim Client].[Is Type A].CHILDREN * [Dim Client].[Is Type B].CHILDREN } ON ROWS FROM ClientInfo
I have SSAS cube with Fact that include values in kg (e.g. 25.3, 32.5, 18,3...).What kind of attribute or other solution should I create If I want to filter those kg's in browser with integer values e.g.:weight between 10 and 25
I have developed a cube in my work place for analyzing current year sales with previous year sales in Time Hierarchy (Year- Quarter- Month) using Parallel period. If we want to see data for particular Quarters i.e. Q1 and Q2 then total at the year level should also get change. Currently if we only choose 2 quarters in the filter then current year data gets change, however data using parallel period is not getting change accordingly and its shows Total of full year.
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?
sometimes, when tables are poorly joined, we have exactly same rows as a result.. so we use distinct.. but how can we count(*) the distinct rows?
select distinct d.docrefid from tbljobDocuments d left join tbljobitems j on j.docrefid=d.docrefid where d.docrefid='DC01C06027' and j.itemid='39J1667-H86700'
will return me
docrefid DC01C06027
but when i try to count(*) only the distinct record, it count all..
select distinct d.docrefid, count(*) from tbljobDocuments d left join tbljobitems j on j.docrefid=d.docrefid where d.docrefid='DC01C06027' and j.itemid='39J1667-H86700' group by d.docrefid
select a.patientid, a.providerid from PatientDrugList a JOIN Admin..Loadinstance b on a.intLoadInstanceId = b.intLoadInstanceId where a.MailReasonTypeId = -1 and b.SubClientId = 22 and b.StatusTypeId > 0
Now how could I get distinct patientid count and distinct providerid count
I have a table which stores the shift information for employees. The table contains 10 columns as Employeename,Employeeno,month,year,shifttimings etc. If an employee works a day in a particular shift, then a row will be inserted in to the above table for that employee.
Now at the end of the month i wanted to calculate the shift details for each employee for a particular month of a given year like employeename,employeeno, noofdays(countof shiftdays).
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.
