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

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

SSAS - Besides 2 big user defined hierarchies, plently of small hierarchies

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

Hi All

I am working on a project where we have to define an Organization Dimension (we defined a standard dimension not parent-child for performance reasons).

The company I am working for is using codeblocks at the lowest level of the hierarchy (combination of business unit, operating unit, etc...).

We created two user-defined hierarchies on that dimension

- One organizing the codeblocks Geographycally (we have attributes like Area, Subarea, CountryGroup, Country)

- One organizing the codeblocks from a Service perspective (ServiceLine, Subserviceline)

The table looks like:

Codeblock, Area, SubArea, CountryGroup, Country, ServiceLine, SubserviceLine, ...

An additional business requirement we have is to define plenty of very small hierarchies besides those two main hierarchies.

One example would be to have a hierarchy that combines values for Country(Switzerland) and Country(Germany). Of course it would be possible to create calculated members in the MDX Script but we have the requirement to be able to drill down from the sum to the individual countries. Since calculated members are not allowed to have children, they are not an option.

We would like to avoid using a parent-child dimension (I know the possibility of using a parent-child with multiple hierarchies).

One ugly option I found is to add dum

View Complete Post

More Related Resource Links

Multiple Parent Child Hierarchies in SSAS 2008 R2


I'm trying to create a dimension containing financial accounts in SSAS 2008 R2 where individual accounts can belong to one or more hierarchies.  I also need to be able to specify a unary operator since a leaf level account may be subtracted from one parent but additive to another parent.  For example revenues and expenses may be grouped one way for statutory reporting and a completly different way for management reporting and further belong to some arbitraty groupings that users find necessary.

I can't seem to find a way to have a leaf level member in a dimension more than one time in a Parent child hierarchy, and I can't seem to find any way to include the unary operator in a hierarchy where I use attributes to define the hierarchy.

Are there any work arounds that anyone knows about?  What I found from the help file was that multiple parent child hierarchies which supported in SSAS 2000 are not supported in 2008.  However there is no suggestion for what to do.

Any insight would be greatly appreciated.


Bill Webster

YTD values with two time hierarchies in SSAS


Hi All,

We have YTD business intellegence time calculations in our cube. Here we have set YTD calculations for two time hierarchies TimeMonthHierarchy - Year-Qtr-Month-Date and TimeWeekHierarchy - Year-Week.

When I use two hierarchies seperately I get accurate figures for YTD but when I use both these hierarchies together it do not work as expected. Also at every month start - YTD restarts.

Please look at the discrepancy below:

Sales (Data Types)







Current Year


User Defined Functions in Microsoft SQL Server

User Defined Functions are compact pieces of Transact SQL code, which can accept parameters, and return either a value, or a table. They are saved as individual work units, and are created using standard SQL commands. Data transformation and reference value retrieval are common uses for functions. LEFT, the built in function for getting the left part of a string, and GETDATE, used for obtaining the current date and time, are two examples of function use. User Defined Functions enable the developer or DBA to create functions of their own, and save them inside SQL Server.

"Type ASP.x is not defined" error for dynamic user control when publishing updateable


I have a user control (ContainerCtrl) which programmatically instantiates another user control (SimpleCtrl) in OnLoad. It builds and runs fine, but when I publish it I get the error "error BC30002: Type 'ASP.SimpleCtrl' is not defined." (If I publish without the "updateable" option there's no error, but I need the site to be updateable.)

I assume this has to do with the particulars of how the assembly(ies) are built for the different compiler options. My question is, how can I get it to work properly for "updateable" publishing?

VS 2008 / .NET 3.5 sp1

The code follows (web.config is the default from Visual Studio).

Many thanks, Dave


<%@ Control Language="VB" ClassName="SimpleCtrl" CodeFile="SimpleCtrl.ascx.vb" Inherits="_SimpleCtrl" %>

    Simple control


Partial Class _SimpleCtrl
    Inherits UserControl

    'Nothing to see here.

End Class


<%@ Control Language="VB" CodeFile="ContainerCtrl.ascx.vb" Inherits="_ContainerCtrl"

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

Creating a table-valued function on a user defined type in SQL Server CLR

We would like to be able to create a table-valued function on a user defined type.  We would like the syntax in SQL to look like it works for the XML nodes function:   DECLARE @myXml XML = '<a><b>1</b><b>2</b><b>3</b></a>' ; SELECT  node.query('text()') FROM    @myXml.nodes('a/b') nodes (node) ;   In other words, in the FROM clause, we can access the "nodes" method of the xml variable and it returns a rowset. Thanks!

User Defined Data Type - using SMO *** NEED HELP ! ***

Hi,Is it possible to script out the User Defined Data Types via SMO in .NET and specifically VB.Net?  I have not been able to find some examples on how to do this.  If you have some experience with this, pleasepost some examples or provide links where I can find this information.Thank you in advance.-Sydney

how to include a user defined table type as input for stored procedure

Hi ,  I have a user defined table type which i need to pass as input parameter to the stored procedure .How can i do that?

Auto Increment User defined Id

Hi can anyone give me an idea how to create an auto generated id like ED01,ED02 etc., so that when i am entering data the id should be automatically incremented

Errors attempting to process SSAS Cube. I get Either user does not have permission to create a new o

Hi All, One of my production server deployed cubes i am not able to process the cube.When i tried to process the cubes i will get an Error: TITLE: Microsoft SQL Server Management Studio ------------------------------ Either the 'NA\gsad478' user does not have permission to create a new object in 'JNJAUSYSVR01', or the object does not exist.  (Microsoft.AnalysisServices)   Over the source server i do have sysadmin access and also  i have added read only role of particular cube. I right click on the database and tried to create new database of SSAS that also thrown with same error. Please help me in this regard.   Thanks, Gangadhar

SSAS 2008 Metadata Repository data - User Knowledge base

Hello, We have the SQL Server 2008 Analysis services instance with some databases, cubes, Dimensions, Measure groups, partitions, etc. The business users are find difficulty to search for a particular measure. I mean, it is difficult for them locate a measure from the available cubes; again finding the respective Measure Group/ Folder is not an easy task for them. Also, they need to know the source system of a particular attribute in a dimension, formula for the calculated measure and etc. So, we have planned to develop a knowledge repository, which needs to store the business metadata for the SSAS databases. The user needs to be access this information using the SharePoint site and should help them locate and understand the data. The User Interface needs to be user friendly too. I need your valuable suggestions for the design of this system. Note: I have referenced the below articles http://dwbi1.wordpress.com/2010/01/01/ssas-dmv-dynamic-management-view/ http://social.msdn.microsoft.com/Forums/en-US/sqlanalysisservices/thread/c610850f-16b4-4eaf-9b91-a2d32816f40e

Best practice for user defined error messages in SQL Server 2005/2008 --Need help

My requirement is to setup a standard practice for our team in handling DB erros and also  user defined messages. We are handling exceptions using Raiseerror method with in Try and Catch blocks.  For user defined message, we are using Sp_addmessage to add our application specific error/warning messages with error codess>80000.  Ex: 80001 | Company name already exists. please check. Here is the sample code ********************************************************************* CREATE PROCEDURE [dbo].[usp_CompanyProfile_insert]  -- Add the parameters for the stored procedure here  (@company_name VARCHAR(50),  @company_code VARCHAR(3),  @user_id INT  ) AS BEGIN  -- SET NOCOUNT ON added to prevent extra result sets from  -- interfering with SELECT statements.  SET NOCOUNT ON;  BEGIN TRY -- Begin Try Block      -- Validate company name   IF EXISTS (SELECT company_id FROM companyprofile WHERE company_name = @company_name)    RAISERROR(90021, 16, 1)      INSERT INTO companyprofile (company_name,       company_code,       createdby)   VALUES(@company_name,     @company_code,     @user_id)    END TRY  -- End Try Block &

Variation Hierarchies Creation Failed

I have setup a new SharePoint 2010 farm with the following hotfixes installed: KB 2028568, KB 983319, KB 983497, KB 2281364, KB 2124512, KB 2204024. I have provisioned a new site collection from the Publishing Portal site definition and have configured content deployment to push content to another site collection on another web application, other than that there have not been any sites, pages, features, etc. created or provisioned within the site collection. I have setup variation settings and have created two variation labels of en-us and es-es with en-us as the source variation.  When the  Variations Create Hierarchies Job Definition is run I see the following in the Variations Log: The variation system failed to create a new site variation for en-us under the en-us label. Provisioning did not succeed. Details: Failed to create the 'Images' library. OriginalException: A duplicate content type name "Document" was found. In the ULS logs I see: 08/23/2010 11:36:25.34  OWSTIMER.EXE (0x09AC)                    0x14E0 Web Content Management         Publishing Provisioning        6wz8 Medium   Adding list Url='PublishingImages', Title='$Resources:cmscore,ListN

MDX Expression - One Expression for Two Hierarchies (same dimension)

I'm trying to write a MDX expression for a calculated member/Custom Rollup. I’ve got two Hierarchies which looks like below:   [Organisation Hierarchy] NAME                                                   LEVEL BELUX HYPERION…          –          Reporting Unit Adjustment/BELUX…     –          Unit Type BEL/LUX/PSF…                  –          Legal Entity BEL AS/ BEL IS…                 –          Department     327AAN/327ACM…         –          Production Unit   [Global Department Hierarchy] NAME             

MDX Expression - One Expression for Two Hierarchies (same dimension)

I'm trying to write a MDX expression for a calculated member/Custom Rollup. I’ve got two Hierarchies which looks like below:   [Organisation Hierarchy] NAME                                                   LEVEL BELUX HYPERION…          –          Reporting Unit Adjustment/BELUX…     –          Unit Type BEL/LUX/PSF…                  –          Legal Entity BEL AS/ BEL IS…                 –          Department     327AAN/327ACM…         –          Production Unit   [Global Department Hierarchy] NAME             

CREATE GLOBAL CUBE statement - losing hierarchies due to slicing

Hi,   I have a SSAS cube from which I generate local cubes with the MDX statement below:   CREATE GLOBAL CUBE MyLocalCube   storage 'MyLocalCube.cub'   FROM [MyCube]  (          MEASURE [MyCube].[Measure1],          MEASURE [MyCube].[Measure2],           DIMENSION [MyCube].[Dimension1],          DIMENSION [MyCube].[Dimension2]          (             LEVEL [HLevel1],                     LEVEL [HLevel2],                     LEVEL [HLevel3],             MEMBER [Dimension2].[Hierarchy1].[HLevel2].&[MemberX]       ),         DIMENSION [MyCube].[Dimension3]  )   The problem is that this statement generates a local cube file which works fine but all other hierarchies defined in Dimension2 are not included. I cannot find the syntax for adding them manually. So, in other w
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