.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

MDX Calculations error - The 'Date Calculations' dimension contains more than one hierarchy, there

Posted By:      Posted Date: September 23, 2010    Points: 0   Category :Sql Server

Hi all,

I am using http://www.obs3.com/pdf/A%20Different%20Approach%20to%20Time%20Calculations%20in%20SSAS.pdf to make a seperate dimension for the data calculations, which works in my Sales cube. But when I use the same dimension in an other cube (Called 'Procurement') and copy the MDX in the calculations tab, I am getting some errors:

Error    57    MdxScript(Procurement) (9, 8) The 'Date Calculations' dimension contains more than one hierarchy, therefore the hierarchy must be explicitly specified.        0    0   

But the 'Date Calculations' dimension does not have a hierarchy!

Now I don't know why I am getting these errors, could you guys help me out so I can understand them and fix the problem?

Thanks you in advance,


View Complete Post

More Related Resource Links

Sharepoint date calculations = WRONG

Can someone show me how to create a calculated column that displays the date only? I'm using the microsoft absence planner. I am simply trying to display a date without the time. I'm getting inconsistent results. Someone suggested that I simply add a day but that is not consistent. I created a calcualted column to show me the day that Sharepoint thinks the time value has. The results are disappointing to say the least someone suggested this formula since it seems to be always off by a day: StartDate is a calculated column = =DATE(YEAR([Start Time]),MONTH([Start Time]),DAY([Start Time]+1)) *sometimes this formula displays the right info, sometimes not The last column is just = Day_[Start Time] so I can try and figure out what is going on :( Attendance item      System Account      Other      6/28/2010 12:00 AM      6/29/2010      28 Attendance item      System Account      Jury Duty      9/29/2009 12:00 AM      9/30/2009      29 Attendance item      System Account      Jury Duty      9/29/2009 12:00 AM      9/30/2009 &nbs

Error on Jquery Date Picker


HI all,

 I am using a Jquery datepicker in my project.There are a lot of pages where I use this date picker and w henever I select any date 

from that ,am getting an error  "length is null or not an object error on line 173"


The HMML for my datepicker control is

<input type="text" id="dpAgreementExpDate" runat="server" maxlength="10" />

Transactional Publication With Article Using DATE Datatype Causes Snapshot Agent to Fail with Error

So I am using SQL Server 2008 Enterprise Edition (64Bit) and have run into a strange problem that is baffling me. I have two tables that I need to replicate.  They are identical in specification except the name (one is for monthly stats and one daily stats) CREATE TABLE [dbo].[statsDailyLite]( [EntryDate] [date] NOT NULL, [SetID] [int] NOT NULL, [ProductID] [smallint] NOT NULL, [Hooks] [int] NOT NULL, [AdViews] [int] NOT NULL, [Clicks] [int] NOT NULL, [UAdViews] [int] NOT NULL, CONSTRAINT [PK_statsDailyLite] PRIMARY KEY CLUSTERED ( [EntryDate] ASC, [SetID] ASC, [ProductID] ASC )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 90) ) GO CREATE TABLE [dbo].[statsMonthlyLite]( [EntryMonth] [date] NOT NULL, [SetID] [int] NOT NULL, [ProductID] [smallint] NOT NULL, [Hooks] [int] NOT NULL, [AdViews] [int] NOT NULL, [Clicks] [int] NOT NULL, [UAdViews] [int] NOT NULL, CONSTRAINT [PK_statsMonthlyLite] PRIMARY KEY CLUSTERED ( [EntryMonth] ASC, [SetID] ASC, [ProductID] ASC )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 90) ) GO They both conatin a thousand or so test rows of data, and they are both in a transactional publication going to another SQL 2008 box. Now when the tables are empty

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

Using derived column for calculations

Hi Guys, Im trying to calculate a value using a derived column. I have this following recordset in my data flow. C1 C2 ITM 88 ITM 10 TX 52.45 DC 92.18 I have this formula: 1. DC = (DC- TX) * 1.12 So the result would be like this. C1 C2 ITM         88.00 ITM         10.00 TX         52.45 DC         44.50 Thanks,  

Getting counts by 2nd Date Dimension Attribute with Snapshot Style Fact Table

  I have an MDX question finding hard to solve.  I have a Snapshot Fact Table with a snapshot of the records in the source system for each batch date.  All records in the fact table are assigned the batch date with the batch date key.  There are many records for each day and each batch date is an entire copy of the source records.  So, the grain of the fact table is one record for each batch date that exists in the source system.  These facts rows have another date in them for when the record was entered.  This date is different from the batch date in that the batch date is based on the day the batch was processed and the entered date is based on when the record was entered.  If a record was entered many days before, its batch date will be today but its entered date will be several days ago.  Therefore each day a copy of all the records entered the previous batch date and all the records added on today's batch date are present. Fact Table : FactSnaphshotKey (surrogate for easier administration) BatchDateKey (link to batch date dimension – date dimension, first in dimension list so it is used for semi aggregate measures) EnteredDateKey (link to entered date dimension – date dimension) Facts Count – measure for fact table - default measure from Analysis Services cube 2 Dim

possible to create or alter calculations on the fly?

is it possible on a processed cube to alter or create (add) calculations without the need to reprocess them ? We tried it with XMLA and MDX but the calculations didn't become visibleBusiness Intelligence professional

Analysis Service Process Dimension Cause : OLE DB error: OLE DB or ODBC error: ORA-00942: table or v

Dear Gurus, I'd face a "STRANGE" problem. I'm using SQL2008 connect to Oracle Data Source (OracleOldDB.1) When I process a snow-flake dimension . There is a ORA-00942 problem. But if I past the SQL Statement to a Oracle Tool . It worked without any problem. Does any body ever face this "STRANGE" problem ? And I'd try to remove this table from data-source-view and re-join it . Still same problem !!  Wilson

The 'Cost Code' dimension contains more than one hierarchy, therefore the hierarchy must be explic

I have a problem with my MDX query as i am newbie. Please help me to solve the problem, even the slightest hint would be very appreciated. I omitted some part of the query to make it more understandable. with  member [Cost Code] as case when [Equipment].[Equipment Type].&[Loader] then [Measures].[Cost Code].&[30321] else [Equipment].[Equipment Type] end select [Date].[Calendar].[All] on columns,  non empty   [Equipment].[Equipment Group].&[BGC]   *  [Equipment].[Equipment Type].children  *  [Cost Code]  *  [Equipment].[Equipment].children  *  {[Measures].[Dam Working Hours],   [Measures].[Gatsuurt Working Hours],   [Measures].[HL Working Hours],   [Measures].[Pit Working Hours],   [Measures].[Plant Working Hours],   [Measures].[Rehabilitation Working Hours],   [Measures].[Total Working Hours] }  on rows from  [Cube]

after restoring content database getting error (HTTP/1.1 200 OK Server: Microsoft-IIS/7.5 Date: Tue,

Hi All, Problem i have taken backup of Old site content database procedure see below 1) first i changed the database to read only mode (Opened SQL Server2005-->selected DB--> properties--> Option-->Database Read-Only=True) 2) taken backup of database from the task--Backup(new.bak) 3) Copied the taken backup to new server and restored (Opened SQL Server2005 --> Right Click on Database Folder--> Restore Dataase) In To database: ihave given DB name From Device: i have selected database from the location where i have saved. click ok.  It has restored successfully 4) I have created a new webapplication and removed its content database from central admin. 5) with the help of stsadm command i restored the above db to this webapplication. then iam getting below error: HTTP/1.1 404 Connection: close Date: Tue, 14 Sep 2010 06:20:08 GMT Server: Microsoft-IIS/6.0 X-Powered-By: ASP.NET MicrosoftSharePointTeamServices: i have refered the below url for restoring http://metahat.blogspot.com/2008/11/backup-restoring-sharepoint-2007-site.html Please help. Thanks in advance.

Running sum calculation based off date dimension?

I have a fairly simple calculated measure which does a running sum of a particular measure : --This does a running sum of the Net Measure up until the previous date (basically a "Starting Count" for a period. Sum(PeriodsToDate([Change Date].[Year - Month - Date].Levels(0), [Change Date].[Year - Month - Date].PrevMember), [Measures].[Net]) The problem is I have added a second hierarchy in the date dimension that includes quarter called [Year - Quarter - Month - Date]. Is there anyway around not having to create a seperate running sum calculated measure to use this new hierarchy? The PrevMember in the initial calculated measure works, although it reports the wrong number when used in conjunction with the Quarter hierarchy because it gets the PrevMember in the other hierarchy. What is the best practice for this situation?Craig

Scope and Time Calculations measures

Hi All I’m using a cube with standard Time Member calculations (YTD, MAT, ..). In my project I need to apply these calculations only to a subset of my measures (ex: 10 measures in total, 3 changes by YTD, MAT,.. selection, the others 7 remain with Default Calculation) I tryed to work on “Scope” statement: I don't put in "Scope" the measures that have to remain unchanged. But it doesn’t works. When I select something (YTD, MAT) measure out of scope returns error. Any idea? Note, I would avoid to forced all other measures to “current Period” Loris

Replace pre-calculated Measures at "All" Level depending upon the Dimension Hierarchy Level

Have an interesting Challenge; thought will reach out to you if you can help Currently I am working on SSAS 2008 OLAP Cube, whereby most aggregate level measures are pre-calculated using the C# Code as part of ETL and are stored in Data Warehouse as well as Measure Groups in Cube (MOLAP). These are mostly non-additive measures have to choose this approach for performance reasons, considering the complexity of the aggregations and data volumes.  Data  Volumes are Huge (about 300+ Million Rows/ per day),  98 % of measures are non additive / semi additive.  Cube is used primarily for advance analytics and will be eventually used for data mining like time series , what if analysis and scenario analysis  Excel is used as front end . Question is how can we replace the aggregate level data for various dimensions attributes (Totals and Grand Totals) from pre-calculated measures, those are also available in the Cube as measure groups? We are currently using Scope and Root statement which is not working as expected SCOPE ([D1].[H1].[A1], M1) Root (D1) = <Get Pre-calculated value for M1 from related Measure Group for D1].[H1].[A1],  > EndScope; SCOPE ([D1].[H1].[A2], M1) Root (D1) = <Get Pre-calculated value for M1 from related Measure Group for D1].[H1].[A2] > EndScope;   Thanks in Advance   Best Regards,   Dave

How to limit a Many to Many Dimension by a secondary Date dimension


Hello MDX gurus,


I have tried posting my problem before, however, I still haven’t gotten a response. Therefore I am going to try again... I am new to MDX, I have had a bit of experience, but nothing more complicating than creating simple calculated measures.


I have a data source which contains Injuries and their Status. An Injury can have it’s Status change depending on whether a doctor visit is involved. For example, an Injury can start out as a First Aid (FA) then change to a Medical Aid (MA) if the person seeks medical attention later on. It can then change to a Lost Time (LT) incident if told to take time off from their doctor. Then, if WCB declines their claim, the Injury can change back to a Medical Aid (MA). The LT/FA/MA status is called the Recordability by our business users.


Therefore, one Injury can have multiple Recordability records, and the ERD looks like this:


Injury -> InjuryStatus <- RecordableInjury


When a report is run, the Status (or RecordableInjury) that appears needs to be the latest one in the selected reporting period (based on StatusDate) for each Injury. Therefore, each Injury may be counted only once within a time period (i.e. Year, Quarter, Month, Week), with the most recent Status appearing for that time period.


Association rules dimension error caused by setting the model algorithm



I got this error meassage when I wanted to process a mined cube:

"Error (Data mining): An error occurred while the 'Bon_DMDim' data mining dimension with the 'Bon' source mining model was being processed. The algorithm of the source model returned data mining dimension content information that is not valid."

Before processing I modified only one algorithm parameter in the Association Mining model properties (AlgorithmParameters):

MINIMUM_SUPPORT = 10 (Default 0.0)

I need to have this adjustment for getting rules...

If I set back this field to 0 or empty, then the processing ends up with no error messages (and I would not have mined rules...).

I also get this message, if I use numbers between 0 and 1 (as a percentage).

How can I build a usable (but with adjusted algorithm parameter) mined dimension and apply it in an OLAP cube?

Thank you and BR,



Isolate only the Date part from MDX returned dimension



I have an MDX query which returns a date dimension values as below

02 Aug 2010
03 Aug 2010
04 Aug 2010

Is there a MDX function i can apply only to isolate only the date part of the values like 02 Aug, 03 Aug.

I can do this quite eaily in SQL using Datepart..but i wasn't able to find a similar function for mdx.

Thank you for your assistance.





SQL error: Conversion failed when converting date and/or time from character string.

I hope someone can advise on the issue I'm having.

This SQL worked beautiful until I added in @startdt and @enddt and this section >> AND [Selldate] BETWEEN ' + @startdt + ' AND ' + @enddt + '

By adding in this condition, I now get the following error:

Msg 241, Level 16, State 1, Line 8
Conversion failed when converting date and/or time from character string.

I have tried changed variables to type = date and various iterations of date formatting, casting with no success. The date in the table is clean as a whistle.

(The CONVERT function is being used in setup for a PIVOT SQL which needs  VARCHAR for column headers)

Thanks in advance for your help, 

declare @itemID varchar(20)
declare @startdt varchar(12)
declare @enddt varchar(12)
set @itemID = ''
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