ssas,mdx , Named Set using iif in mdx

Named Set using iif in mdx


Tag: 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 invalid syntax (gives an error).

create set [pet types]

iif([Vw Cube Customer Dim].[Has Pet].&[Yes],
    iif([Vw Cube Customer Dim].[Has Cat].&[Yes],
        iif([Vw Cube Customer Dim].[Has Dog].&[Yes],
            iif([Vw Cube Customer Dim].[Has Other Pet].&[Yes],
                "Multiple Pets","Cat and Dog"),
    iif([Vw Cube Customer Dim].[Has Dog].&[Yes] 
        and [Vw Cube Customer Dim].[Has Pet].&[Yes],
        "Dog and other Pet","Dog")),
"No Pet")


To improve readability (but it decreases scalability) I'd rather use matrix of states with a coefficient of each hierarchy's member: Pets Matrix

Than add several members with their weights:

CREATE MEMBER CURRENTCUBE.[Measures].[Has Pet Value] as
IIF([Vw Cube Customer Dim].[Has Pet].CurrentMember
 IS [Vw Cube Customer Dim].[Has Pet].&[Yes],8,0);

CREATE MEMBER CURRENTCUBE.[Measures].[Has Cat Value] as
IIF([Vw Cube Customer Dim].[Has Cat].CurrentMember
 IS [Vw Cube Customer Dim].[Has Cat].&[Yes],4,0);

CREATE MEMBER CURRENTCUBE.[Measures].[Has Dog Value] as
IIF([Vw Cube Customer Dim].[Has Dog].CurrentMember
 IS [Vw Cube Customer Dim].[Has Dog].&[Yes],2,0);

CREATE MEMBER CURRENTCUBE.[Measures].[Has Other Pet Value] as
IIF([Vw Cube Customer Dim].[Has Other Pet].CurrentMember
 IS [Vw Cube Customer Dim].[Has Other Pet].&[Yes],1,0);

And last two members: 1) for support, 2) to get desirable result.

CREATE MEMBER CURRENTCUBE.[Measures].[Pet Types Value] as
([Measures].[Has Pet Value]
+[Measures].[Has Cat Value]
+[Measures].[Has Dog Value]
+[Measures].[Has Other Pet Value]);

case [Measures].[Pet Types Value]
    when 15 then "Multiple Pets"
    when 14 then "Cat and Dog"
    when 13 then "Cat and other Pet"
    when 12 then "Cat"
    when 11 then "Dog and other Pet"
    when 10 then "Dog"
    else "No Pet" end;

Adding another dimension/hierarchy for analysis (let's say [Vw Cube Customer Dim].[Has Bird].&[Yes]) may cause adding new member with next weight (16 in this example) and recalculate [Pet Types Value] and [Pet Types].


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

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

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

How to go from rows to a hierarchy in icCube

I want to create a one-column hierarchy from multiple columns using mdx. I got this result: from the Sales cube with this mdx: SELECT { {[Measures].[Amount]} } ON COLUMNS, { [Customers].[Geography].firstNotAllLevel().allmembers }*{ [Product].[Product].[Category].allmembers }*{ [Time].[Quarter].firstNotAllLevel().allmembers } ON ROWS FROM [Sales] I want the first three columns to form a hierarchy...

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

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

How to Reload CDA and Mondrian cache in Pentaho CE 4.8?

I'm currently stuck in some performance issue for my Dashboard. I've created a dashboard in Pentaho Community edition 4.8. For my charts, using the SQL and MDX (Mondrian) queries. My Problem is that, When I first time open my dashboards after clearing cda and Mondrian cache. It take 50 secs...

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

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

SSRS cascading parameters: child parameters' default 'All' value drops to blank after user changes parent parameter

I have a big bunch of cascading parameters in my rdl report with default value 'All' everywhere. But all children go blank after user changes some parent parameter, and then he has to choose 'All' value from drop-down list for each of child parameters manually. This is pretty annoying staff....

SSRS mdx report: use dimension on columns

This simple mdx query do work in SSMS: SELECT CrossJoin({[Measures].[Qnt]}, {[Sales_step].CHILDREN}) ON COLUMNS, [City] ON ROWS FROM [SALES_PIPE] But it fails to run within Visual Studio for rdl report. An error accures: The query cannot be prepared: The query must have at least one axis. The first axis of the...

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

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

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

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

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

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

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

format_string in icCube does not format null values

I was trying to display values that are null as 0 using format_string. I currently have the following: WITH MEMBER [test] AS null, FORMAT_STRING = '0.#;Neg (0.#);0.00;0.00' SELECT [test] ON COLUMNS FROM [Sales] The fourth value (;0.00) should format the null values to zeros. I read that here: Currently...

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

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

Mondrian - Parent with filtered children

I want to create a topcount of users per product category. My product dimension is like the following [Product].[ProductCategory].[ProductType] Users of my application can filter the products and it return a set like this: [Product].[ProductCategory1].[ProductType1], [Product].[ProductCategory3].[ProductType5], ... Now I got the topcount per product category, but it doesn't filter the...

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

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

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

How to create an interval in a matrix

I am working with SSRS 2008, my source is a cube and I use this MDX query: SELECT NON EMPTY { [Measures].[Val] } ON COLUMNS, NON EMPTY { ([DimCl].[Cl].[Cl].ALLMEMBERS, [DDate].[Year].[Year].ALLMEMBERS ) } ON ROWS FROM [DW] I created a matrix with the following data 2014 2015 0 10 11 1...

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

Count Product was sold In MDX query

My MDX query as SELECT {[Measures].[SaleAMT]} ON COLUMNS ,NON EMPTY [Account Date13h].[Date].&[2015-01-01T00:00:00] : [Account Date13h].[Date].&[2015-05-05T00:00:00] ON ROWS FROM [Sale_Period_report]; I want to return a Count of product was sold, like that: Date SaleAmt ProductSold 2015-04-01 20.000 150 2015-04-02 36.212 650 2015-04-05 10.333 65 ...

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

Topcount with total of all members

I'm creating a query which shows all continents, their countries and the top 5 users in these countries (1 dimension) with their orders (measure). Now I want to show to total of all users in these countries instead of only those in the top 5. I followed the answer in...

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

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: Top 10 with filtered members

I'm creating a report for JasperServer in iReport. I am creating a report with the top 10 companies with most product downloads of the selected products. Users can select in JasperReports which products with a list (parameter {$P{ProductFormat}}). My dimension for products has the following structure: [Product].[ProductFactory].[ProductType], so input for...

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

SSRS stacked bar chart multiple series, utilizing category groups for different date fields

The source data is an SSAS cube which contains information about tickets, particularly priority, date opened, and date closed. Each ticket has a priority and date opened value, though the closed value may be null or another placeholder value (subject to change). A ticket is represented by a record in...

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

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

drill down on reversed hierarchy gives incorrect results

I have got the following MDX statement in icCube (based on the standard Sales model): with set [time-set] as hierarchize({[Time].[Calendar].[All Periods],[Time].[Calendar].[Year].members,[Time].[Calendar].[Year].[2009].children},post) member [issue] as [amount], caption = "drill down on 2008" select [issue] on 0 , [time-set] on 1 from sales This gives the following result: When clicked on 2008:...

MDX MTD to get the month to date data

Currently i need to show the data which is from the first day of month until the specific date. SUM(MTD([D Time].[calendar].CurrentMember.parent), [Measures].[Wt]) I try the code above is shown the first day of month until the end of the month... Anyone can help to correct my code? Or should i...

Get the amount of days in a month - mdx

In TSQL I can do this to get the amount of days in some month: declare @date as datetime set @date = '2015-02-06' select datediff(day, dateadd(day, 1-day(@date), @date), dateadd(month, 1, dateadd(day, 1-day(@date), @date))) How can I get the same functionality in MDX? P.S. I need the result to be an...

Difference between AdomdConnection and AdomdCommand classes

My understanding is that AdomdConnection initializes a connection with a remote database. This class opens it based upon the ConnectionString. The AdomdCommand then seems capable of setting a CommandText and Executing said command, similar to executing a query. Is this understanding correct? But how are the two related? Do I...