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
ADVERTISEMENT
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
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
Aug 28, 2015
How to move attribute from one dimension to another in a cube?
View 2 Replies
View Related
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
View Related
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
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
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
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
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
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
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
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
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
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
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
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
Apr 16, 2015
I have a parent Child attribute in my dimension. It show the correct ID value in the reports but when I change the value from the NameColumn to be something else it still only shows the ID value. I have an identical case in another dimension and it is working correctly. However this dimension for some reason does not seem to work and I believe they are built the same way I must be missing something. I have tried to show the OrgNodeID and tried to display the OrgNodename but neither displays all it displays is the intdimParentOrgNodeID.My dimension table looks something like this
intdimOrgNodeID int Key (surreget key)
intOrgNodeID int (Actual ID)
intDimParentOrgNodeID
intOrgNodeName
[code]....
View 4 Replies
View Related
Jul 20, 2005
Hi experts,having a parent-child-table with the columns child_id, child_name, parent_idin SQL Server 2005 I just cannot create a parent-child dimension in BI DevStudio. Can anyone give me some hints? The Dim Build wizard doesn't createthe hierarchies, manually setting "parent" property to parent_id and "key"to child_id as well as dragging and dropping the stuff into the hierachyfield haven't just led to success. I also tried to right-click bothparent_id and child_id to create a member property. It just never workedout.Any help would be greatly appreciated.Kind regards,Joerg
View 1 Replies
View Related
Oct 15, 2015
I have parent child dimension,
for customer id-101
---------H1-p Sales Value-50
---c1-c Sales Value-100
---c2-c Sales Value-200
If I will see H1 Value then it show =350
But when my head office is not exists for a customer but Child is exist i want to see data of child Aggregate value.
Example
for Customer ID-102
NO --H1 it is null
----c1 c sales Value -200
---c2 c Sales Value-50
When I want to see value of 102 at H1 leval it show me 250 . even H1 is null.
View 3 Replies
View Related
May 21, 2015
I have a parent child dimension. a time dimension i have year.
Assume,
ParentID ChildID sales Year
171 171 10 2014
171 172 200 2014
171 173 300 2014
171 172 44 2015
if I pass 2014 and 2015 in sub select 171 data is not coming in result. i i pass only 2014 in sub select i get value of only 2014. if I pass 2015 in sub select i didn't get any value.
View 2 Replies
View Related
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
Sep 14, 2015
At the 2nd level, the calculated measure will only be correct if it is Averaged. And at the 1st level, the calculated measure will only be correct to take these Average value from 2nd level and Sum then up together.
Level 1 Level 2
A
X1
X2
=================
(Avg of X1 + X2)
B
Y1
Y2
Y3
=================
(Avg of Y1 + Y2 + Y3)
===================================================
(Sum of (Avg of X1 + X2) + (Avg of Y1 + Y2 + Y3) )
Currently, Instead of summing all the 3 averaged value, it is averaging against all the items like,
(Avg of X1 + X2 + Y1 + Y2 + Y3)
My MDX currently looks something like that
[Measure].[Value] / [Dimension].[Attribute.count] -> so i can get the avarage at the 2nd level but it doesn't require on the 1st level but retaining this value.
How can i do an average on the leaf level and using these average values to sum at parent level?
View 4 Replies
View Related
Apr 8, 2015
Include children and exclude children in a single hierarchy in parent child dimension in mdx
*12-parent
**20-parent
- 9-parent
--250-child1
--210-child2
--240-child3
aggregation of 12-parent only
aggregation of 20-parent only
aggregation of 9 with children
View 3 Replies
View Related
Aug 15, 2013
how to find the orphaned value from the below parent/child hierarchy Table.
create table dbo.Hier(parent varchar(100), child varchar(100))
insert into Hier
select 'subramanium','Manickam' union all
select 'subramanium','Munuswamy' union all
select 'Munuswamy','senthil' union all
select 'Munuswamy','sasi' union all
select 'Munuswamy','uma' union all
select 'manickam','vijay' union all
select 'manickam','bhavani' union all
select 'manickam','dhanam' union all
select 'uma','varsha'
Delete from Hier where child='uma'
I tried:
select parent from Hier
where parent not in(select Child from Hier)
and parent <> 'subramanium'
Getting resultset as:
parent
======
uma
I need to know whether my select statement is correct or not,if its correct,how to write the same in CTE?
View 2 Replies
View Related
Mar 5, 2008
How do i find the child tables that belong to a parent table. Thanks.
View 10 Replies
View Related
Oct 22, 2015
I need to write recursive query to find child of a parent until the last leaf. Below is my code.
;WITH Parent AS(
SELECT [ParentID],Value
FROM[DynamicColsValues_TP1]
WHEREValue IS null
UNION ALL
SELECT t1.[ParentID],T1.Value, FROM DynamicColsValues_TP1 t1 INNER JOIN Parent t2
ON t1.[ParentID]=t2.[ParentID]
)
SELECT * FROM Parent option (maxrecursion 0)
When I execute this code. It is returning me millions of rows. Whereas i have only 20 rows in a table max 40 rows it should return.
View 7 Replies
View Related
Aug 10, 2015
I want to insert attibute att1 in field F1 . Value for this attribute is content of another field in this table (F2).
My query :
update MyTable
set F1.modify
('insert attribute att1 {sql:column("F2")} into (/ROOT/Node1)[1]')
Where F1 Is not null
But I get this error :
XML well-formedness check: Duplicate attribute 'att1'. Rewrite your XQuery so it returns well-formed XML.
How do I check the douplicate attribute ?
View 0 Replies
View Related
May 16, 2008
HI,
I had to change the key columns of a dimension attribute to fix an error. I did this in BIDS. The change was from a single key column to a composite key column. Now I am getting these error when I process the cube:
Measure group attribute key column x does not match source attribute ..
I looked at the cube XMLA definition under mesaure groups and it still shows a single key column with inherited binding. However, the BIDS does not give me an option correct this in any way. I have had to do this once before and the only option seems to be removing the dimension from the cube and add it back in. But that is very error prone since I lose any specific settings at the cube dimension level not to mention aggregations no longer include the dimension, etc.
Not seeing an alternative, I went through each measure group (I have 7) and changed the key columns manually in the XMLA and saved the cube. This worked, but I don't understand why BIDS automatically doesn't do it.
Is this a flaw in the BIDS or I should be missing something.
thanks
MJ
View 3 Replies
View Related
May 25, 2015
below data,
Countery
parentid
CustomerSkId
sales
A
29097
29097
10
A
29465
29465
30
A
30492
30492
40
[code]....
Output
Countery
parentCount
A
8
B
3
c
3
in my count function,my code look like,
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
it will take 12 to 15 min to execute.
View 3 Replies
View Related
Jul 24, 2015
I've got two measure groups with a dimension (Dimension A) that is related to one measure group but not the other. I want to find the date ranges of the members of an attribute in Dimension A in Measure Group 1 and apply that to Measure Group 2 in a calculation. This way I can find the sum of a measure from Measure Group A that falls within the time periods of the attributes in Measure Group B. Part of my MDX for this new calculated member starts like this, but doesn't work.
WITH MEMBER [Measures].[New Measure] AS
IIF( ISEMPTY ([Measures].[Measure 1]), NULL,
SUM(([Date].[Hour].[Hour], [Dimension A].[Attribute].[Attribute]), [Measures].[Measure 2]))
View 3 Replies
View Related
Aug 7, 2015
Where to find history of cube backup? I am new to analysis services.
View 4 Replies
View Related
Aug 20, 2015
I have sales from 2010 to today in my cube. my question is i want to see sales amount from 2010 to 2014 if i select 2014. or if I select 201405 ( may 2014), i want to see sales from 20100101 to 20140531. same with date. basically i want to to al the sales til the date/month /year selected. how can i achieve this?
View 2 Replies
View Related
