.NET Tutorials, Forums, Interview Questions And Answers
Welcome :Guest
Sign In
Win Surprise Gifts!!!

Top 5 Contributors of the Month
Gaurav Pal
Post New Web Links

Need Help in creating different sets within a same hierarchy (Period Hierarchy)

Posted By:      Posted Date: September 14, 2010    Points: 0   Category :Sql Server
Hi, I have a period hierarchy which is set up like , Period -> All -> AllPeriod -> Then it has last 24 months (rolling) ie Period->All->All Period ->Jul08, Aug 08, ....Jun 10. So in all it has got last 24 months in it. Now I need to create a chart in SSRS which will show data for all the years ie in this case it will have 3 series in all, one for Year 2008 (it will contain Jul 08-Dec 08), the second series will contain data for 2009 (Jan 09-Dec 09) and the third will contain the data for year 2010 (Jan 10-Jun 10). In my cube there are no levels like year, half year etc..all the members are in the same level. Can someone guide me to write a proper MDX for this scenario ? What I have tried is this, with   // Getting the data for 2010, I have hard coded 6 for now in the below query as I know the currentmonth is June. set   [StartMonth] as ' bottomcount( {[PERIOD].Members}, 6)' member   [Measures].[SalesValue2010] as ' [Measures].[SalesValue]' member   [Measures].[PrevYearSalesGrowth2010] as '[MeasuresSalesGrowth]' select   non empty {[Measures].[SalesValue2010],[Measures].[PrevYearSalesGrowth2010]} on 0, non   empty {[StartMonth]} on 1   from [MyCube] where   [Condition] I ideally need three salesvalues on my columns . I guess this would be easy but I am very new to MDX and as of now d

View Complete Post

More Related Resource Links

Creating a Hierarchy with more than one dimenstions.

I have one fact tables and four dimensions which are related only through fact table. My requirement is to create a single hierarchy with different attributes from these dimensions. After reading the previous posts on the same subject, I created a Named query in my data source view which is nothing but the a query that joins all the dimensions and a fact table with their SK and I have the required attributes into the select statement. My question is, Do I have to create a relationship with this Named query with any of the dimensions or Fact table? If so, what would be the foreign and primary key?

Key Columns property when creating dimension hierarchy in Analysis Services 2008


HI All

I have 3 dimensions in our Data Warehouse, dimCampaign, dimCampaignList and dimCampaignTarget
Think of dimCampaign as a marketing campaign, that contains one or more Campaign Lists (offers) for which many customers (dimCampaignTargets) receive.  The are foreign key relationships between these 3 tables, and it is the dimCampaignTarget ids that are written to associated fact tables

I have created a single dimension in Analysis Services 2008 that contains these 3 tables and their respective relationships were set up in the DSV so that I could do this.  Rather than use the 'referenced' dimension method, I wanted a 'single' dimension containing the 3 tables and their snowflake relationships in order that I can create a hierarchy that contains attributes from differing tables (namely the Campaign Description from dimCampaign, and Campaign List Description from dimCampaignList). 

My hierarchy has those 2 levels, top level is Campaign (description), and the 2nd level is Campaign List (description).  The Campaign Targets are not shown in the hierarchy, their attribute is only used in the dimension as the join to most of the fact tables.

Problem is when I set up my hierarchy, I go to change the Key Columns property of the Campaign List attribute to make it a collection of keys (Campaign and Campaign List) as I did in

Creating a default member on date hierarchy



I am trying to set a default member on a date dimension.

I have an attribute form the database of IsToday which is a boolean with the value of today eg 23/11/2010 showing as true.

When i go into the Date dimension and try to select [Date].[Is Today].&[True] in Choose a member to be the default I get the error below

DefaultMember(Date,Is Today) (1, 1) The level '[Date]' object was not found in the cube when the string, [Date].[Is Today].&[True], was parsed.

Does anyone have any ideas why this is?


Hierarchy ID: Model Your Data Hierarchies With SQL Server 2008


Here we explain how the new hierarchyID data type in SQL Server 2008 helps solve some of the problems in modeling and querying hierarchical information.

Kent Tegels

MSDN Magazine September 2008

Test Run: The Analytic Hierarchy Process


Most software testing takes place at a relatively low level. Testing an application's individual methods for functional correctness is one example. However, some important testing must take place at a very high level-for example, determining if a current build is significantly better overall than a previous build.

James McCaffrey

MSDN Magazine June 2005

Organization Hierarchy - Manger not showing


SharePoint 2007 SP2 on Server 2008 R2

I am doing a custom profile import using AD and a BDC connection.  All the data comes in and populates correctly, including the 'Manager' field which is being populated from the BDC entity. All main fields are being populated from the BDC entity.  Fields are; First name, last name, email, office and cell phone, and manager.

However, when viewing the Organization Hierarchy, the users 'Manager' is not showing up in the Hierarchy. Nothing above the current user is shown in the Hierarchy.  The Manager field is populated with a valid user.  If I display the Manager field in the 'Details' section, I can see the name and click on it to see their profile page as well, so it is all valid and accurate.

Colleagues ( users with the same Manager ) are listed in the Hierarchy, but none of their hierarchy's show the manager either.

I have deleted every profile and then done a full import several times with the same result. Incremental import does ont have any affect either.

Any suggestions or advice on how to get the Manager to display?

Thanks to all!


Video: SharePoint 2010 Object Model Hierarchy

This video describes the hierarchy of the most commonly-used objects in SharePoint 2010. (Length: 2:18)

MDX to get a count on a hierarchy

Hi I want to get the number of customers that placed orders for items from more than 2 Product Category in a month in the Adventure Works 2008 DW database. Can someone help me with the MDX expression? thanks

WWF and hierarchy

Good morning guys: We are currently on the process of doing some research to find the right technology for our next project and even I've read a bit on WWF I have these questions that I would really appreciate if anyone can help me solve these: 1. We have a product component hierarchy, it means a product is composed by many components. Products and components might have different states (state machine), so, is it possible to define an workflow that handles this hierarchy? I mean, finally this is like 2 workflows right? 2. Would these be like 2 different workflows? Thanks for any help!

Business explainfor tables and hierarchy in AdventureWorks database and REAL_Warehouse_Sample_V6 cub

AdventureWorks (ProductModel table, what is this table for) ------------------------------ REAL_Warehouse_Sample_V6 (Periodicity(only have one attribute:period)) (Replen Strategy(three level hierarchy(strategy type:backlist,frontlist);(strategy:Modeled,Buyer Managed,No replenishment,Store Managed)) Another one is what is the difference and relation between buyer and customer in cube REAL_Warehouse_Sample_V6? -------------------------------------------- I can not understand the business meaning behind these tables and architecture,can any one help explain the meaning for upper hierarchy and table? happyMan

Unable to get Lastperiods 4 periods in time hierarchy at different levels.

Hi, I am unable to get last 4 periods when the weeks are falling at two different months. Example: The calendarsales contains the following hierachy : Year, HalfYear, Quarter, Month, Week August month contains the following weeks:1033,1034,1035 July month contains the follwing weeks: 1032,1031,1030. So i am selecting the week 1035 and i need last 4 weeks till 1032. But the below qry is returning last 3 weeks 1035,1034,1033. It is unable to retrieve 1032 as it is coming under previous month of July.  Below is the mdx query i am using. **************************************** WITH     SET ORDEREDCAT AS   Order ( [Dim_Product].[ProductDefaultHierarchy].[Category]. MEMBERS ,[Measures].[Sales Units] , BDESC )     MEMBER [Measures].[SalesUnits_L_4WKBP] AS   Sum ( ( {   LastPeriods (4 ,[Dim_RetailerSalesCalendar].[CalendarSales]. CurrentMember ) } ,[Measures].[Sales Units] ) )     SELECT     NON EMPTY {[OrderedCat]} ON 1 ,{ [Measures].[SalesUnits_L_4WKBP]     } ON 0 FROM   [Aroyale] WHERE   {[Dim_Retailer].[Retailer].&[5]}* {[Dim_RetailerSalesCalendar].[CalendarSales].[week].&[1035]} ***************************** Please can any one help me on this. Regards, Srini.

modelling SCD2 hierarchy changes in analysis services 2005

its seems to me its really not possible to implement SCD2 changing hierarchy in analysis services such the hierarchy is properly displayed in excel. for instance if scd2 changes happen on leaf level then excel correctly shows the data but if they happen on branch level analysis services picks up one parent eg at time 1 hierarchy looks like this A->B at time 2 hierarchy is A->C when hierarchy is displayed in excel 2007 it shows      time       1       2 C    100   200   A  100    200   but this not a correct display of hierarchy changing over time the correct display is this      time       1       2 B    100   A  100 C            200   A          200      

SSAS 2008 attritube hierarchy doesn't group records and repeat rows

We are having problems with the dimension attributes in lower level hierarchies not grouping under 1 single level. Here is the hierarchy: VP    College      Department         Departmental Course            Course Level               Course Number The top 3 levels are grouping correctly without duplicate rows and they don't have compoud keys. The lowest 3 levels are not grouped correctly and they have compounds keys because the attributes are not unique by themself. The result of the hierarchy looks like this: VP Academic Affairs    Business School of Management        International Business School            Mgm101                 Lower                       Course Number 123            Mgm101                Upper                

MDX to retrieve data from Time hierarchy

Hi there, I have a time hierarchy Year->Quarter->Month->Day. I need to get all the level data by one MDX.  Desired result set should look like Year    Quarter    Month    Day  MeasureValue 2010    Q1           Jan       01     1000 2010    Q1           Feb       28     500 Thanks, Palash

Dynamic SQL statement for a multilevel hierarchy report

Basically I have a requirement from the functional team that need to show a multilevel hierarchy report.   ID ParentID A NULL B A C A D B E C F C G F   The above is the sample of the table.   I need to show the cascading relationship dynamically base on the available level. The issue was we would not know how deep the relationship in the table.   Target Result:   ID Parent Level 1 Parent Level 2 Parent Level 3 A NULL NULL NULL B A NULL NULL C A NULL NULL D B A NULL E C A NULL F C A NULL G F C A

SSAS - How to get other members from dimension that has Parent Child hierarchy?

I have a Sales Territory dimension that has employee and parent employee attribute on which parent child hierarchy is defined and it gives below hierarchy while browsing - - Mark Rolls --- Lumin Jacs ----- Larry Gomes ------- Messica Owens ------- Tom Ted ----------- Jackson Lopez ----- Matthew Ron --- Fred jacob - Jason Ron --- Jecy Pedro   But beside this parent-child hierarchy I have other attributes like employee address, email and telephone. My facts related sales transaction is tagged to lowest level. For example here facts are available only for Jackson Lopez (being Field Executive). When use below query I get complete sales reporting hierarchy result with some measures like sales amount, sales volume. But while accesing other attributes like address or email, it's repeating address/email/telephone of jackson Lopes everywhere to whom fact record is linked, Actually I want address/email/telephone of each sales employee from that dimension within hierarchy. How do I get it? The query I used is: Here Parent Terr ID is parent child hierachy. SELECT ( Descendants( { [Dim SalesRegion].[Parent Terr ID].[Employee Level 01].&[538018] /* here Mark Rolls is 538018 */ }, 0,AFTER), NONEMPTY([Dim SalesRegion].[Emp Address].[Emp Address].Members), NONEMPTY([Dim SalesRegion].[Emp Email].[Emp Email].Members) ) ON ROWS , { ([Dim Date].[The Year].[The Year].[CY-2010], [Dim

Parent-Child, Unbalanced Hierarchy and unexpected Root behaviour

I have a chart accounts that contains an account key and parent account key (like the AW Enterprise sample). Each account ultimately has either Assets or Liabilities as parent. However, There also some accounts appear in the root because they have less levels then the other accounts. to put in other words: the acounts with exactly four levels, ultimately show up with either assets or liabilities in the root but the members with less then four levels, also appear in the root instead of under Assets or Liabilities. Where am I going wrong? The dimension settings appear to be exactly the same as the AdventureWorks-sample.
ASP.NetWindows Application  .NET Framework  C#  VB.Net  ADO.Net  
Sql Server  SharePoint  Silverlight  Others  All   

Hall of Fame    Twitter   Terms of Service    Privacy Policy    Contact Us    Archives   Tell A Friend