Analysis :: Using Attribute As Second Parameter For FILTER?

Aug 13, 2015

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]

View 2 Replies


ADVERTISEMENT

Analysis :: Using Named Set To Filter On Dimension Attribute

Jun 4, 2015

I am unable to find solution for the problem while writing a Named Set in my cube.

I have a calculated measures which gives me difference in Sales in PERCENTAGE (%).

When I try to filter out those product codes which went a less than 5 %, I get no records.

I have also tried to filter direct values lets say - Products with sales > 100000 which is working fine.

Following is sample of my Named Set

FILTER([X].[Products Code].members, [Measures].[Diff in Sales]<5)

I believe as the values are in percentage, I am facing this issue.

View 21 Replies View Related

Question On Filter Itemset By Up To One Attribute In Association Rules

Jul 17, 2007

Hi, all experts here,

Thanks for your kind attention.

I want to filter the itemsets or rules based on more than 2 attributes, how can we achieve that? (I can only filter them by only one attribute?). Is it possible to achieve that?

Thanks a lot and I am looking forward to hearing from you shortly.

With best regards,

Yours sincerely,

View 5 Replies View Related

Master Data Services :: MDS Filter Entity Based On Attribute From Another Domain?

Oct 28, 2014

I have an entity (A), in which I use domain based attribute. The second entity (B) has several attributes. My problem is that, I would like to filter the first entity (A) based on an attribute that belongs to the second entity. The only way I can filter it (in MDS Excel add-in or Explorer) is by using Code or Name from the second entity.

I have in mind a couple of solutions, but they require some coding with xml saved query from Excel.

View 4 Replies View Related

Analysis :: How To Get Dimension Key Attribute In MDX

Apr 30, 2015

Many dimensions don't have unique members.  Instead, the dimension source data has duplicates at the leaf level: it's left up to SSAS to aggregate up to the actual leaf level used in hierarchies.

Every cube I've worked on in the past, a dimension is clearly defined in the source data, with uniqueness already present there: we don't make a dimension out of duplicated, sort of facty data.  This kind of design seems as weird to me as an unnormalised SQL database.

Here's an example to illustrate what I mean; I'll use that Adventureworks database.

We have a Geography dimension with a Geography hierarchy.  Levels go like this from top to bottom:

Country
State-Province
City
Postcode

The Geography dimension has a key attribute called Geography Key.  It's there in the cube design as a dimension attribute, but it's not in any of the hierarchies, so I can't query it in MDX.  But that's fine: it has the same cardinality as the lowest level (Postal Code), because the dimension has some kind of normal design.

In the cube I'm dealing with, it's all messed up.  Using the AdventureWorks example above as a parallel, someone made a Geography dimension with source data keyed on [PostalCode, ExactAddress], but only wanted the dimension granularity to be PostalCode.

This makes it very hard to debug why the data in this dimension is incorrect.  I can't match up the dimension members in the cube to the source data, because the dimension doesn't actually go down to the real leaf level!

So I have a dimension attribute called ExactAddressKey, but I can't query on it in MDX, because it's not part of any dimension hierarchy.  Unfortunately changing any part of this cube design is not possible, so I can't even experiment with settings and see what happens.

How I could get to the leaf level of the data imported?  Something like

Geography.Geography.TheInvisibleLeafLevel.Members.Properties('Key')

Or does this kind of dimension design result in SSAS discarding all the data that's more granular than the most granular attribute defined in any hierarchy - so that the data actually isn't there to be queried?

View 2 Replies View Related

Analysis :: Move Attribute From One Dim To Another

Aug 28, 2015

How to move attribute from one dimension to another in a cube?

View 2 Replies View Related

Analysis :: MDX - Find The Parent Attribute

Aug 13, 2015

I have date hierarchy with year - qtr- month - date. from the below query, if i have a date or month or year on rows, i want to derive the top member in the hierarchy that is year. I f i have date on rows,

[Date].[Calendar Hierarchy].[Date].&[20150106],I should get [Date].[Calendar Hierarchy].[year].&[2015].

How to find the parent?

with member [dt7] as Drillupmember([Date].[Calendar Hierarchy].currentmember,
                            [Date].[Calendar Hierarchy].[Calendar Year]) 
select {[dt7]} on 0,
([Date].[Calendar Hierarchy].[Month].&[201501]) on 1 
from Cube

View 5 Replies View Related

Analysis :: DAX - Use Of Alternate Attribute Hierarchy

Jul 31, 2015

I have a Tabular model with a situation where I want to have three alternate attributehierachies in one dimension.

Dimension FruitAndVegetables (with 4 columns: Id, Name, Fruit and Vegetable)
Id Name     Fruit      Vegetable
1 Apple      Apple   
2 Onion                   Onion
3 Banana   Banana
4 etc

Now I would like to put Vegetable on rows in a report without getting a blank row (with the sales of all Fruits)..I would like to supress all those Fruit records without adding  a separate filter to the report, just let the user pick this Attribute should do the move.

View 2 Replies View Related

Analysis :: Change Dimension Name Attribute?

Sep 8, 2015

I have 2 dimensions that pull their Facility Name from the same Location Dimension.  The business users want to change Facility Name in the Material Facilities dimension to “Material Facility Name”, but keep Facilities dimension attribute the same. What is a good way to go about completing this task.  

View 2 Replies View Related

Analysis :: Calculated Attribute In SSAS?

Sep 17, 2015

I've been working with SSAS for a good few years now but I keep bumping into this problem - my users are trying to build a measure that is based on a calculated attribute and finding it difficult to work out how to write the MDX to do so. Intuitively, they thought a Calculated Member would work, but I don't think a Calculated Member is quite the same thing from my understanding.

So, here's the scenario.

We have a Product Dimension. We have a Measure that is the Number of days the Product took to make, e.g. 5 days. We also have a Product Count measure that counts the number of Products.

The user would like to write a calculated measure that works out the number of products that took <5 days, 5-10 days, 10-15 days etc.It would be easy to write a set of calculated measures for each of these bandings, but the user wants effectively a single dynamic attribute to use in the calculation in order to automatically distribute these values across the columns in their pivot table.

Is this even possible? I was thinking I could build an attribute on the Product Dimension in the ETL to do this quite easily, but the user wants to be able to change the bandings on the fly by changing the MDX for the attribute, rather than go back to the developer every time.

View 4 Replies View Related

Analysis :: Duplicate Attribute Key Has Been Found When Processing

Jul 8, 2014

1) Errors in the OLAP storage engine: A duplicate attribute key has been found when processing:

Table: 'dbo_Dim_x0020_Document_x0020_Type',Column: 'Item_x0020_No_', Value: '1100'. The attribute is 'Item No'.

How can I resolve this on package level.

2) I am also not able to see all the fields of a fact table when creating cube, where I can se all fields in dataview.

View 6 Replies View Related

Analysis :: SSAS Displaying Latest Dimension Attribute

Jun 30, 2015

I have a cube with a fact table and 3 dimensions.  One of the dimensions is a type 2 and surrogate key is stored in fact table.  If i query the database, the dimension attributes display correctly, however the cube is always displaying the latest dimension attribute and not preserving the history.  

The measures are correct for the time period displayed, but the dimension attributes always show the latest values.

View 3 Replies View Related

Analysis :: Best Way To Add Additional Parent Child Attribute Values

Apr 16, 2015

I have a parent Child attribute in my dimension.  I am currently displaying the correct ID value as the business wants.  So now they can see the rollup of the ID(intOrgNodeID ) values.They would also like to see the same rollup of the Name (vcharOrgNodeName)  for this ID.However they do not want it concatenated.  They want to be able to see them separate.You cannot create two parent child attibutes in one dimension so not sure if there is some simple trick to make this work? It seems like there should be some simple trick for this. 

My dimension table looks something like this
intdimOrgNodeID int Key (surreget key)
intOrgNodeID int (Actual ID)
intDimParentOrgNodeID
vcharOrgNodeName
In the Propertys I have set this below.
KeyColumns  = tbldimOrgNode.intDimParentOrgNodeID
NameColumn = tbldimOrgNode.intOrgNodeID

View 8 Replies View Related

Analysis :: SSAS Named Set With A Two Dimension Attribute Tuple

Jul 15, 2015

In my SSAS Cube I have created a dynamic named set "top 10 e-learnings by language" which consists out of a set of tuples. Each tuple has two attributes out of the same base dimension "training": attribute 1 is "sprache" (language) and attribute 2 is "training text".

CREATE DYNAMIC SET CURRENTCUBE.[Top 10 eTrainings pro Sprache]
AS Generate(
{ [Training].[Sprache].[Sprache].Members },
TopCount(
EXISTING { [Training].[Sprache].CurrentMember * [Training].[Training Text].[Training Text].Members },
10,
[Measures].[Teilnahmen eTraining]
)
), DISPLAY_FOLDER = 'Training' ;

Normally a named set would be automatically visible in Excel Pivot under the dimension you used to create the named set, but it seems that named sets with tuples which have more than one attribute are placed in a separate folder "Sets" in between the measures and dimensions.Additionally in the SSAS cube browser this named set is not visible at all.Is there any way to tell the named set in which dimension it should appear or any workaround?

View 2 Replies View Related

Analysis :: SSAS - Display Measure Value Based On Other Attribute Member?

Sep 2, 2015

getting Correct Measure based on Member Present in Other Attribute .

I am working on SSAS 2012 and have cube build and ready ..

I have Two Measure in Cube 

[MEASURES].[Actual] and [MEASURES].[Target] and I need to create One more Calculate Measure 

I have dimension DimProduct 

I want to Display [MEASURES].[Actual] if Color "Purple" is present for PRODUCT1 Else Display [MEASURES].[Target].

MDX to create Calculate Measure for this logic?

View 4 Replies View Related

Analysis :: Attribute Relationships Impacts On Members Full Unique Name

Aug 17, 2015

Our SSAS integration didn't initially use attribute relationships.Now that our system has been running for a few years and we have bigger databases, we think we need to add them to improve performance. So we're in the process of adding them but we found out that, when attribute relationships are added, the full unique name of our members all go from something like:

[DIM].[HIERARCHY].[LEVEL].&[GRANDPARENT].&[PARENT].&[MEMBER]
to something like:
[DIM].[HIERARCHY].[LEVEL].&[MEMBER]

It looks nice and SSAS will accept the longer names fine but it will return the short ones in response to 'discovery' requests and in the XMLA response of MDX queries. This is causing problems in our low level XMLA-based modules that assume the long names in and out. is there any clean way to use attribute relationships and still have SSAS generate the long member names. We fiddled with the various documented dim/attribute properties but to no avail. It also appears that some switches are obsolete.

View 6 Replies View Related

Analysis :: Setup Multiple Default Members For A Dimension Attribute In SSAS?

Jun 2, 2015

I have a dimension report with an attribute reporttype which have different member let say A,B,C,D....etc.

I want to set more than one default members for dimension report.

I've read all about how dimensions can have only one default member, but I need to set more than 1.

View 2 Replies View Related

Analysis :: Errors In OLAP Storage Engine - The Attribute Key Cannot Be Found When Processing

Jun 12, 2015

I 've read that there is a workaround for this issue by customizing errors at processing time but I am not glad to have to ignore errors, also the cube process is scheduled so ignore errors is not a choice at least a good one.

This is part of my cube where the error is thrown.

DimTime
PK (int)MyMonth (int, Example = 201501, 201502, 201503, etc.) Another Columns
FactBudget
PK (int)Month (int, Example = 201501, 201502, 201503, etc.)

I set the relation between DimTime and FactBudget doing DimTime MyMonth as Primary Key and FactBudget Month as Foreign Key.
The cube built without problem, when processing the errror: The attribute key cannot be found when processingwas thrown.

It was thrown due to FactBudget has some Month values (201510, 201511, 201512 in example) which DimTime don't, so the integrity is broken.

My actual question: is there a way or pattern to redesign this DWH to correctly deploy and process?

View 4 Replies View Related

Analysis :: Data Masking For Dimension Attribute Based On User In SSAS 2014 Multidimensional?

Jun 15, 2015

I am trying to implement data masking based on user login and not sure why this is not working. I have the dimensions DimBrand, DimProduct and DimUser. I should mask the BrandCode with 'XXXX' nothing but in the report all the BrandCode should appear but few of the code will be masked if the user is not belongs to that group. I have a fact table FactProduct in this. In the cube I created all these 3 dimensions and the fact table. I created a new dimension DimBrandMask and I separated the code over there with a relationship with the actual DimBrand dimension. In the cube a reference relationship is set up with the measure group. Created a role with read access.

In the dimension data tab of role I put the below MDX to allowed set.

NonEmpty([DimBrandMask].[Brand Code].Members, (StrToMember("[DimUser].[Login Name].[Login Name].[" + UserName() + "]") ,[Measures].[Dim User Count]))

And in Denied Member set i put the below MDX

IIF( (StrToMember("[DimUser].[Login Name].[Login Name].[" + UserName() + "]"), [DimUser].[Access Right].&[False]), NONEMPTY( [DimBrandMask].[Brand Code].Members,(StrToMember("[DimUser].[Login Name].[Login Name].["
+ UserName() + "]"), [DimUser].[Access Right].&[False], [Measures].[Dim User Count])),{})

Note I created one measure group from the DimUser table and the measure [Dim User Count] is used in the above query.

I am expecting some result like below

Brand      BrandCode           Count
Brand1      b1                       6
Brand2     XXXXX                  5
Brand3     XXXXX                 10

View 9 Replies View Related

Analysis :: Using A Filter In CALCULATE

Sep 8, 2015

I'm having what i think it's a strange error in CALCULATE function:

Calculation:=CALCULATE( DISTINCTCOUNT([SK_CUSTOMER]); 'fact x'[_sales net]<>0)

where _sales net is a calculated column, sum of 3 existing columns lets call columnA , columnB, columnC

This calculation doesn't work well, the results seem to be like as if _sales net was only equal to columnA.

Naturally I've already done build deploy and full process.

View 2 Replies View Related

Analysis :: Conditional Filter In DAX

Aug 4, 2015

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:

evaluate
calculatetable (
addcolumns (
summarize (
'Event'

[code]...

but if the Account_ID is passed as zero, condition 'Event'[Account_ID] = 2 should be removed.Is this possible to implement in DAX?

View 9 Replies View Related

Analysis :: MDX Filter With Alias

Sep 9, 2015

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

View 4 Replies View Related

Populate Parameter List With Caption From One Attribute And Value From Another Using MDX

Dec 7, 2007



I have a units conversion table. I'd like to populate the parameter drop down with the name of the unit and have the value selected be the numrical value found in a different attribute.

Here is my current MDX:

WITH MEMBER [Measures].[ParameterCaption] AS '[Dim Units].[Units].CURRENTMEMBER.MEMBER_CAPTION' MEMBER [Measures].[ParameterValue] AS '[Dim Units].[Units].CURRENTMEMBER.UNIQUENAME' MEMBER [Measures].[ParameterLevel] AS '[Dim Units].[Units].CURRENTMEMBER.LEVEL.ORDINAL'

SELECT {[Measures].[ParameterCaption], [Measures].[ParameterValue], [Measures].[ParameterLevel]} ON COLUMNS , [Dim Units].[Units].ALLMEMBERS ON ROWS FROM ( SELECT ( STRTOSET(@DimSalesTypeDescription, CONSTRAINED) ) ON COLUMNS FROM [Heidtman DW])

Units Parameter Caption Parameter Value Parameter Level
(null) All [Dim Units].[Units].[All] 0
Gross Ton Gross Ton [Dim Units].[Units].&[Gross Ton] 1
Hundred Weight Hundred Weight [Dim Units].[Units].&[Hundred Weight] 1


I can't seem to get different attribute to show up for ParameterValue.



Also, is there a way to NOT include the "all" member? For what I am trying to do it would have no meaning.

Thanks.

View 2 Replies View Related

Analysis :: Filter A Set Based On Same Measure?

Jun 10, 2013

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.

View 3 Replies View Related

Analysis :: Filter Data For Two Hierarchies

Sep 23, 2015

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...

H1    H2   Amount
1      1      100
2      1      50

View 2 Replies View Related

Analysis :: How To Filter Tabular Perspectives

Oct 5, 2015

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.

View 2 Replies View Related

Analysis Service: Filter Fact Table ?

Oct 6, 2004

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 ?

Thanks

View 2 Replies View Related

Analysis :: Distinct Count Using Filter Function

May 27, 2015

My requirement is to count the customer order number for premium order type orders which has some order quantity.I am using below MDX

count

Filter
(([Customer Order].[Dim Customer Orderkey].[Dim Customer Orderkey].members,
[Outbound Order Attributes].[Order Type].&[P]),[Measures].[Ordered, pcs]>0 ) ,
)

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

count

Filter
(([Customer Order].[Dim Customer Orderkey].[Dim Customer Orderkey].members,
[Outbound Order Attributes].[Order Type].&[C]),[Measures].[Shipped, pcs]>0 ) ,
EXCLUDEEMPTY)

View 3 Replies View Related

Analysis :: IIF Does Not Aggregate AND Filter In SCOPE Does Not Work

Oct 22, 2015

We have two measures

[Measures].[NSPO]
is in FACT1  Measure Group
[Measures].[NetSignedPremium] is in FACT2  Measure Group

The requirement is Measure.[NetPremium] should be the greater of

[Measures].[NSPO] OR [Measures].[NetSignedPremium].
 
All three measures are created in the cube and this logic is to be applied in the Calculations section of the cube.

Using an IIF statement like one mentioned below does the job, but data does not aggregate correctly for the

Measure.[NetPremium]
Measure.[NetPremium] = IIF ([Measures].[NetSignedPremium] > [Measures].[NSPO], [Measures].[NetSignedPremium] , [Measures].[NSPO])

To ensure that the columns are aggregated I have to use a SCOPE statement at the leaf level

[01 UWR Reference].[UWR Reference].Members  
 
SCOPE(
    FILTER( NONEMPTY( [01 UWR Reference].[UWR Reference].Members   ) ,
          ( ( ([Measures].[Net Signed Premium Rcvd])
          - ( [Measures].[NSPOTest]  )  )
             < 0 )  )) ;
        This =   [Measures].[Net Signed Premium Rcvd]  ;       
END SCOPE;  

My problem is the FILTER within the SCOPE statement does not work. How to modify the filter in SCOPE? OR How to make IIF Aggregate correctly?

View 4 Replies View Related

Analysis :: Filter Measure On Intersection Of Dimensions

Jul 24, 2015

SSAS 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.

View 4 Replies View Related

Analysis :: MDX Need To Filter Measures Based On A Dimension

Oct 26, 2015

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

Here's the DESIRED output........

View 3 Replies View Related

Analysis :: Filter Numeric Values With Integer Value In SSAS

Jun 3, 2015

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

View 3 Replies View Related

Analysis :: Total Calculation With Filter For Parallel Period

Nov 30, 2015

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.

View 4 Replies View Related







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