FAQ Database Discussion Community


What cube deployment changes will force a reprocess?

ssas,cube,ssas-2008
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...

SQL SSAS Cube Deploy Error:

sql-server-2012,ssas,cube,bids,sql-server-2012-datatools
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...

What does an MDX query return?

ssas,mdx
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...

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

ssas,mdx,olap
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...

How to delete default Measure Groups in the SSAS CUBE?

sql-server-2008,ssas,ssas-2008
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, ...

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

sql-server,ssas,mdx,ssas-2008,data-cube
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...

How to get the Percentage using MDX

ssas,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). ...

[SSAS CUBE Partition Strategy for large historical data]

process,ssas,cube,partition
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 install ssas AdventureWorks DW with SQL Server 2008 R2

sql-server,sql-server-2008,sql-server-2008-r2,ssas,adventureworks
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...

Do I need a cube?

sql-server,ssas,reporting,olap
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...

select data from olap MDX query

ssas,mdx,olap,olap-cube
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 |...

Working MDX query doesn't work as a Calculated Member

ssas,mdx
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?

excel,ssas,pivot-table,cube
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...

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

ssas,cubes,msbi
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...

MDX Top Count Sub total

sql-server,ssas,mdx
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...

Creating a Calculated Time Periods Hierarchy

ssas,mdx,calculated-member
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...

Named Set using iif in mdx

ssas,mdx
Ι am trying to make calculated dimension attribute using a named set but I am not very familiar with mdx. I want the attribute to contain a description based on the values of 4 different attributes. What I have tried to use is the following code but it must have...

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

reporting-services,ssrs-2008,ssas
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...

Equivalent to max group by in mdx

ssas,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, {...

tabular in-memory vs multidimensional and molap-mode

ssas,cube,tabular,in-memory,rolap
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...

QTD MDX calculated value displaying differently on SSRS than SSAS

sql-server,reporting-services,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...

Mdx , All and All Members differences

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

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

ssas,ssis-2012,ssas-tabular
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...

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

ssas,hierarchy,olap,granularity
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...

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

sql-server-2012,ssas,ssas-tabular
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...

SQL Server 2014 Analysis Services Deploy Permission Error

ssas,sql-server-2014
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 average aggregation

sql-server,ssas
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...

Set 0 for specific value MDX query

sql,sql-server-2008,ssas,mdx,cube
I've been looking around for the answer but I didn't find anything. Sorry if the answer has been given elsewhere. Here is my problem : I have a calculated member which is the number of items (of the current member) divided by the total number of items (sumitem). with member...

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

sql-server,ssas,olap
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...

Missing tag in xmla to deploy assembly

dll,ssas,clr,.net-assembly,cube
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...

How to view the data from SSAS CUBE

sql-server-2008,ssas,ssas-2008
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

sharepoint,reporting-services,tfs,ssas,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...

Intersect Select Query in MDX

ssas,mdx,olap
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...

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

excel,reporting-services,ssas,mdx,olap
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...

Filter Measure by reference date column

ssas,mdx
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 Query to get employee ID based on their login ID

ssas,mdx,username
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....

Can't create database in SQL SSAS

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...

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

ssas,foreign-key-relationship,data-warehouse,olap-cube,dimensional-modeling
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...

Two sets specified in the function have different dimensionality

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 from [AcoEdsv] ...

Approaching mixed granularity date dimensions for operational periods in cube design

ssas,olap,olap-cube
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...

How to group record by range and put it on rows

ssas,mdx
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 ) }...

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

ssas
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...

Assign Dimension value to SSIS variable

sql-server-2008,ssis,ssas,ssas-2008,data-cube
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...

MDX Start and End Time per transaction

ssas,mdx,cubes
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...

Excel 2010 - filter pivot table by pattern

excel,excel-2010,ssas,pivot-table
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,...

How to make a calculated member in MDX for SSAS?

ssas,mdx,business-intelligence,olap,olap-cube
I have a problem in my calculation and can't solve. Example: Our company sells about 100 products, divided into categories and product ID. Product ID is the smallest granularity. I need to show how this product list we sell in each city. See the picture: Overall, without showing the category...

Mdx query incosistent with cube browser

ssas,mdx
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...