ssas,ssas-2008,ssas-2012 , Two sets specified in the function have different dimensionality

Two sets specified in the function have different dimensionality


Tag: ssas,ssas-2008,ssas-2012

I have a basic query and having trouble understand exactly what this error means.

Select {[Dim Date].[Date].[Date],[Measures].[Quantity]} on columns


It's not error, but wrong query.

I believe error is:

Members, tuples or sets must use the same hierarchies in the function.

In other words, Set and Member used as a Set.

[Dim Date].[Date].[Date] - it's a set of Level Date's members. E.g. {2/1/2015, 2/2/2015, 2/3/2015} and so on.

[Measures].[Quantity] - it's a member Quantity of special dimension Measures.

Here is list of fails in your query and possible solutions:

1) [Measures].[Quantity] should be on another axis (e.g. , [Measures].[Quantity] on rows)

Select {[Dim Date].[Date].[Date]} on columns
,[Measures].[Quantity] on rows

2) Or you're trying to use aggregation function without determining itself. Let's say SUM:

with member [Measures].[Test]
as SUM({[Dim Date].[Date].[Date]},[Measures].[Quantity])

Select {[Dim Date].[Date].[Date]} on columns
,[Measures].[Test] on rows


Do I need to import the full data in SSAS tabular mode from my SQL database in DirectQuery mode?

I'm trying to build a Analysis service tabular project in tabular mode and want to use DirectQuery mode so that the queries are executed at the backend. When I click on the model, and select import data from source, I see option to retrieve the full data. Now I have...

Excel 2010 - filter pivot table by pattern

in Excel 2010 I am trying to analyze some data from an external analysis service. In a pivot table I am trying to filter the report by one field which has multiple values separated by a comma. These look like this: AB, CD1, EF1-5 AB, CD1,3, EF1 BCD, EFG EXG,...

MDX Top Count Sub total

I am trying to create an MDX query to calculate the top selling stores for a territory, then subtotal the territory. I have used the TOPCOUNT function with the GENERATE function to create a SET for the Top Locations, however I am having real trouble trying to sub total each...

How to get the Percentage using MDX

I want to find the percentage of claims in run time. I learn that it can be achieved using calculated measure, but I do not know how to do that. Please guide me how to find the percentage of claims((line count/grandtotoal) *100). ...

How to install ssas AdventureWorks DW with SQL Server 2008 R2

I am doing a proof of concept to explain the team using cubes improves the application's query performance. I am allowed to use VS 2008 BI tools. I am in need of the AdventureWorks2008R2 DW to be installed to write some mdx queries to query the cubes. I have landed...

Can't create database in SQL SSAS

I just installed Microsoft SQL Analysis Service because I need it to run a forecast analysis from Excel using the Data Mining Plug-in. When I open MS Management Studio and connect to the SSAS I don't know where to create a new database. When I right-click over Databases there is...

Intersect Select Query in MDX

I want to have customers intersection in two mdx querys. 1. SELECT [Measures].[Cs] ON 0 ,NonEmpty([Customers].[Customer].MEMBERS) ON 1 FROM [sfe cube] WHERE ( [Calend].[Period].&[201506] ,[Customers].[BP Territory].&[38UZ1] ,[Materials].[Brand].&[Coca-Cola] ); 2. SELECT [Measures].[Cs] ON 0 ,NonEmpty([Customers].[Customer].MEMBERS) ON 1 FROM [sfe cube] WHERE ( [Calend].[Period].&[201506] ,[Customers].[BP Territory].&[38UZ1] ,[Materials].[Brand].&[Fanta Orange CSD] ); My attempted...

Equivalent to max group by in mdx

How do I get the sales for the last product of a cross join of each product group and brand? I had a look at the Tail function but I can't get it to work properly. This is the MDX I have so far: SELECT {[Measures].[Sales Amount]} ON COLUMNS, {...

Approaching mixed granularity date dimensions for operational periods in cube design

I am building a cube in SSAS, modelling (amongst other things) activity of engineering teams. I have a fact table (TeamActivity), with facts such as Mileage and TimeOnSite on a DAILY granularity. This references a date dimension table (DimDate). DimDate contains typical attributes so data can be analysed by calendar/fiscal...

Creating a Calculated Time Periods Hierarchy

In order to give a nicer Cube browsing experience to end users, I am trying to create a Time Periods hierarchy consisting of Calculated Members. Currently I have used a Calculated Column in my DSV to create a column with the same value on every row in my Dates table...

What does an MDX query return?

I just started learning SSAS and cannot understand the basic idea. What happens when query fixes fewer dimensions than cube has? All examples usually present queries where intersection of dimensions gives either a point or an axis; in the former case we have the value and in the latter one...

Is it possible to disable multiple selection of dimension values in OLAP cubes in SSAS?

I'm designing a MOLAP cube in SSAS and one of my dimensions has a very high cardinality >1000 members. I want to disable multiple selection on this dimension and only allow users to select one member at a time. Currently, when users browse a cube through SSMS, they can drag...

tabular in-memory vs multidimensional and molap-mode

I have a question regarding the ssas-models tabular and multidimensional cube. I've read that both models can work in a real-time-mode (direct query mode & rolap). My questions concerns the tabular model in in-memory-cache-mode and the multidimensional model in molap-mode. How recent is the data there? Can I define myself...

Mdx query incosistent with cube browser

I have built a multidimensional SSAS cube with some calculations. I have a calculated member in which I use the the parallelperiod function to calculate the previous month value according to the following code : Sum ( (EXISTING [TimeDim Transactions].[Year - Quarter - Month - Date].[date].MEMBERS) ,( ParallelPeriod ( [TimeDim...

SSAS average aggregation

I'm currently trying to create an SSAS project which is used to analyse the profitability of each product. One of the facts in my fact table is Profitability, which is a ratio calculated by (ListPrice - Cost) / Cost However I'm having some problems with my Cube because SSAS automatically...

MDX - TopCount plus 'Other' or 'The Rest' by group (over a set of members)

I've got requirement to display top 5 customer sales by customer group, but with other customers sales within the group aggregated as 'Others'. Something similar to this question, but counted separately for each of customer groups. According to MSDN to perform TopCount, over a set of members you have to...

SSAS report action to pass multi-value list values to a SSRS report parameter

I have searched many hours attempting to find an end-to-end solution to this problem. I want to create a report action in an SSAS OLAP cube that generates a list of formatted values to pass into an SSRS report parameter that accepts a multi-valued parameter list. This would be applied...

Assign Dimension value to SSIS variable

I have data cube hierarchy as follows. I can access the highlighted node as SELECT [Calendar].[Report Days].[All Members].[All].[WantInReport].[Yesterday].LastChild ON 0 I tried to run this query in Execute SQL task and assign the output to an SSIS variable. But issue is the column name is changing. I tried to alias...

QTD MDX calculated value displaying differently on SSRS than SSAS

I have created an SSAS cube in SQL 2005 and included calculated members for MTD, QTD as well as YTD. With QTD measure I actually want to get the total over all 3 months of the quarter, not just the up-to-date value. Now when I browse the cube itself the...

Missing tag in xmla to deploy assembly

I am trying to deploy a custom .NET assembly to my SSAS database. While doing so, I am facing Clr Assembly must have main file specified error and changing the target framework as given in the marked answer didn't solve it. I also checked whether the dll was blocked as...

Do I need a cube?

We have a content ingestion system which receives (mobile) digital contents of different types (Music, Ringtone, Video, Game, Wallpaper etc) from various providers (Sony, Universal Music, EA Games etc) and then dispatches them across several online stores (e.g. Store1, Store2 etc). The managers want to know how many of each...

MDX - Retrieving a level of members currently used in where clause

I have a simple data cube with organization structure hierarchy defined. In my calculations inside the cube I would like to have different calculations depending on which level of organization items is currently used in WHERE clause in MDX query. So let's say that I have 5 levels of organization...

Working MDX query doesn't work as a Calculated Member

I have made the following mdx query to calculate a running total using some references I found on internet which works when I ran it on management studio. With member measures.test as ( sum([Card Issue date].[YearQuarterMonth].[Calendar Year Month].&[2014]&>>[2014-10] :[Card Issue date].[YearQuarterMonth].CurrentMember , [Measures].[LoyaltyMembers]) ) select measures.test on 0, [Card Issue...

What does the SSAS setting None in AggregateFunction do?

As I understand Microsoft's explanation setting the AggregateFunction of a measure to "None" should null out the value unless you are viewing the data at the lowest possible level. (i.e. the data element is a copy of the value in one exact cell in the cube). I've created a fact...

SQL SSAS Cube Deploy Error:

I am trying to deploy a cube I created in SQL Server Data Tool using AdventureWorksDW2008R2, I used windows authentication login the database engine with all permissions granted to it. and yet I still get these arrors in the picture below when deploying the cube. Anyone Can point out which...

select data from olap MDX query

Please help to understand how MDX query works. I have connected to cube using excel and construct a mdx query. In short finally I need to get the table like this: 12.01.2015 +-------+-------+-------+-----+-------+------ | 00:00 | 01:00 | 02:00 | ... | 23:00 | TOTAL --------+-------+-------+-------+-----+-------+------ Ivan | null |...

SSAS cube: force Budget measure granularity drill down to the leaf level of calendar dimention (split Quarter Budget between Monthes, Weeks and Dates)

Our Budget/Plan has Quarter grane, while Fact sales data is stored Dayly. To build Plan-Fact reports for Monthes, Weeks and Dates - I need to split Budget evenly between Calendar dates. How can I do this within a Cube? After hours of googling I've found references to [IgnoreUnrelatedDimensions] property of...

What cube deployment changes will force a reprocess?

Sometimes when I deploy a cube that has been changed (from BIDS), I can continue to browse the existing cube data. Other times, the engine insists I reprocess the data before I can browse the cube. I can't find a definitive resource showing which changes require a data reprocess and...

Errors in the OLAP storage engine: The attribute key cannot be found when processing

I know this is mainly a design problem. I 've read that there is a workaround for this issue by customising 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...

How to delete default Measure Groups in the SSAS CUBE?

I have created a CUBE. By default CUBE created few Measure Groups. When I go to Aggregations, I can see the default Measure Groups. I want to delete them all and want to create as per the requirement. Attached is the screenshot, ...

Add value of TOP (ALL) to parameter in SSRS report connecting to SSAS cube

I have a simple SSRS report connecting to an SSAS cube that displays all the sales by employee for a day. The report displays correctly and shows me all employees and its sales, in descending order. But the users don't want to see all the sales; they just want to...

How to view the data from SSAS CUBE

I am new to SQL Server CUBES. I have processed the CUBE in SQL Server 2008 environment. How to view the data based on its dimensions? Do the CUBE needs to be mapped to the Reports to view the data? Whether the CUBE has to be processed on daily basis...

Configuring a standard single server TFS 2013 with SharePoint 2013 and SSRS for reporting

Is following scenario possible? We have got TFS 2013 installed as a standard single server. This server has got the SQL db. We have got SharePoint 2013 installed on another machine. Now we want to integrate TFS 2013 with SharePoint 2013 along with SSRS and SSAS for reports, etc. Please...

How to transfer data from Tabular model (SSAS Tabular) to Relational Database?

I am new to SSAS and Tabular model cubes. I have a data migration task that aims to import data from a Tabular model into a SQL Server database. I have tried SSIS with not success. Using OLEDB Data source connected SSAS instance with Tabular database, I configured the data...

MDX Query to get employee ID based on their login ID

I'm trying to implement dynamic security on a cube where an employer should be able to see the measures associated with all the employees under him. I'm doing this by first getting the login credentials of the logged in user using USERNAME() and then getting the descendants of that user....

Two sets specified in the function have different dimensionality

I have a basic query and having trouble understand exactly what this error means. Select {[Dim Date].[Date].[Date],[Measures].[Quantity]} on columns from [AcoEdsv] ...

SQL Server 2014 Analysis Services Deploy Permission Error

How can I resolve this permissions error when deploying an Analysis Services cube? The user already has SA privileges on the localhost server. ------ Build started: Project: FerrariRusso, Configuration: Development ------ Started Building Analysis Services project: Incremental .... Dimension [Date] : Create hierarchies in non-parent child dimensions. Dimension [Customer] :...

[SSAS CUBE Partition Strategy for large historical data]

I have a big cube with 2.5 million new data per day. 19 million a week. Those data are historical data, no update , no remove and no change.So what's best partition strategy for this kind of data ? You can see only one week there are a lot of...

How to group record by range and put it on rows

Below query compute the percent of work employee wise. i.e. employee on rows and percent of work on columns With Member [Measures].[EmployeeWisePercent] AS [Measures].[Hours] / ([Employee].[Employee].[All], [Measures].[Hours]) * 100 Member [Measures].[TotalHours] AS ([Employee].[Employee].[All], [Measures].[Hours]) SELECT NON EMPTY { [Measures].[Hours], [Measures].[EmployeeWisePercent], [Measures].[TotalHours] } ON COLUMNS, NON EMPTY { ([Employee].[Employee].[Employee].ALLMEMBERS ) }...

Filter Measure by reference date column

In my date dimension I have an attribute called CompareToDatekey which shows the date that should be used to compare measures through time. How can I build a calculated measure that will give the measure value for the compare date when I filter a particular primary date? What I tried...

MDX Start and End Time per transaction

I hope you can help i have tried so many way to try get this right with no luck. I am trying to get out the player account number the date and start and end date time and maybe calculate the play duration between the start and end times. I...

SSAS -How to select a particular attribute when we drag dimension into query editor

I have about 4 attributes in Race dimension as shown in blow dimension name is Race 1)Race 2)RACE DESC 3)RACE KEY 4)RACE SHORT NAME when go to cube browse and right click on Race dimension and select add query as below 1)when i drag Race dimension to browser data panel...

OLE DB error: OLE DB or ODBC error: The SELECT permission was denied on the object ; 42000

I create an analysis service cube on the ssas. When i tried to process the cube, this error showed up : OLE DB error: OLE DB or ODBC error: The SELECT permission was denied on the object 'V_M_Sang', database 'ODSDB', schema 'dbo'.; 42000. I do not know how to solve...

Mdx , All and All Members differences

In MDX what is the difference between [Period].[Period Name].[Period Name].ALLMEMBERS and [Period].[Period Name].ALL are these statement same?...