In SSAS, can parent-child DATAMEMBER name be customised

In a parent-child dimension in SSAS, the datamember is automatically named the same as the parent. E.g. Division X Risk Register Division X Risk Register Department A Risk Register Department B Risk Register Division Y Risk Register Division Y Risk Register Department P Risk Register Department Q Risk Register I want all the datamembers to be named "Executive Risk Register", as follows: Division X Risk Register Executive Risk Register Department A Risk Register Department B Risk

SSAS cube Deployment

I was trying my hands on building Cubes using AdventureWorksOlap database. I successfully build what I was trying to do. Now my concern is that I want to deploy the cube to a server so that rest of the team members can use this cube as a datasource while generating their SSRS reports (might be some other tools). I have heard that SSAS does not allows Sql Authentication. So, 1) how will the members access the cube? 2) What authentication changes do I need to incorporate? 3) How can the other

Parent Child Relationships Between Measure Groups in SSAS

I am having a tough time figuring out how I am supposed to implement parent-child relationships between my measure groups in SSAS. Essentially I am just trying recreate a standard SQL join so that I can lookup measures in my Parent group while using keys in my Child group. For example, let's say I have the following dimensions: Parent Child (FK: ParentID) Time And I have the following measure groups ParentFact (Keys: ParentID, TimeID - Measures Related to the parent over time) ChildFact

Ssas Hierarchy repeating levels

I am trying to create a very simple geographical hierarchy on a Carrier dimension. My data is something like this: Carrier City State Country Carrier A | Chicago | IL | USA Carrier B | Aurora | IL | USA Carrier C | Stockton | CA | USA Carrier D | Winnipeg | MB | Canada Carrier E | Richmond | BC | Canada Carrier F | Valparaiso| ZAC| Mexico Carrier G | Veracruz | VER |Mexioc My Hierarchy is defined as Country State City Carrier Attribute Relationships: Carrier --> City Name --> Stat

Drilling through in ssas to a dimension attribute that has hierarchy disabled

I am using SQL Server 2008 R2, developing using BIDS. I have a very large cube, with many dimensions, and I am building a drill through action. The requirement is for a very broad drill through, which includes many fields that are in the fact table (and are not used in the cube, except for in drill through), and also many dimensions have attributes that are not hierarchy enabled such as textual info. I have created the dimensions, and added all the attributes required for the drill through as d

How to create sub partitions in measure groups of SSAS OLAP

I want to break up one of my partitions of my measure group that has 450 million rows into sub partitions to make processing and querying faster. I currently split my measure group using attr1 of dimension1. Is it possible to split each partition further based on a different attr2 of the same dimension? I know that I can write a SQL query to do this but what I want to know is how to create the slice hint such that SSAS knows to look at the sub partitions. I tried something like this in slice exp

Create siblings SSAS

If you have a parent-child dimension how can the power users easily create new level in the hierarchy? In SSAS is the posiblity to enable WriteBack and easy create new child and new sibling. Can you do this also from excel? I don't want that users to login to SSAS.

Can you create an SSAS Tabular measure with filter constraints?

I am trying to get my head around how to design the SSAS tabular model for a scenario where I have an attribute on a dimension that will modify an otherwise straight-forward measure on a related fact table. The fact table is currently at 155 million rows. The dimension has 6200 rows. The measure we need sums a unit volume fact, then multiplies that sum by a factor that belongs to the dimension. The context used for this measure has some constraints, then: the aggregation can never include multi

Ssas How do you run Multiple XMLA Alter Commands at the same time?

I have the following two XMLA alter commands that individually work fine. I would like to run them at the same time in SQL Management studio. I have tried wrapping them in command, batch, and a number of other elements. I can't seem to get anything to work correctly. <Alter AllowCreate="true" ObjectExpansion="ObjectProperties" xmlns="http://schemas.microsoft.com/analysisservices/2003/engine"> <Object> <DatabaseID>DB1</DatabaseID> <RoleID>Rol

Ssas Aggregation on different level of dimension hierarchy

I have two dimensions, lets say Date Hierarchy and Product and a measure which has MAX (Measures.[Max]) aggregation. The requirement would be to have SUM of Measures.[Max] on DAY or HOUR level of Date Hierarchy and be summarized in Month level. I have the following query: With Member Measures.SumOfMax as SUM([Date].[Hierarchy].[Hour].AllMembers, Measures.[Max]) Select NON Empty { Measures.SumOfMax } ON COLUMNS, NON EMPTY { [Date].[Hierarchy].[Month].A

Ssas How to add a metric for top n count in mdx

I want to create a metric which will show only the top 10 result whenever applied. Suppose the data is item Price A 20 B 45 C 50 D 80 E 10 F 90 G 85 H 55 I 40 J 100 I want to show the top 5 result in descending order. So, the expected result is: j-100 f-90 g-85 d-80 h-55 other-165 I am already getting the result with the following MDX query: With Set [Top10] AS (TOPCOUNT({ORDER( ({[DimProduct].[item].[All].Children}) ,([Measures].[Price]),BDESC)},10)) MEMBER [DimProd

SSAS Calculated Measure for Same Weekday Last Year

I've got a Date dimension and a Fact table containing sales by day. I'm trying to do an easy same period last year calculated measure but I can't get it to work. Using the ParallelPeriod function obviously doesn't work as this is not getting the same weekday for last year and sometimes has no value at all. I have an extra date attribute (PrevDateKey) on my date dimension pointing to the correct day of last year. Is there any way I could use this in a calculated measure? There's currently a rel

Dax Queries in ssas

What's wrong in the below code DEFINE MEASURE DimDate[MyMeasure] = COUNTROWS(DimDate) EVALUATE DimDate[MyMeasure] I'm getting the following error while running the query: Query (1, 1) The expression specified in the query is not a valid table expression

Ssas How to check, which hierarchy is being used

I have 1 dimension Data with 2 hierarchies: Gregorian Calendar, Planning Calendar. [Gregorian Year] - [Gregorian Month] - [Gregorian Day] [Planning Year] - [Planning Month] - [Planning Day] I have 2 MDX measures: [Planning Stock] = Sum(PeriodsToDate([Data].[Planning Calendar].[(All)]), [amount]) [Gregorian Stock] = Sum(PeriodsToDate([Data].[Gregorian Calendar].[(All)]), [amount]) How to create one measure that will recognize the hierarchy? I wrote something like that, but it does not work :(

Ssas Name of the user who has processed the cube

There is a code piece in which I have to get the username of the user who has processed the cube or has made any changes in the cube structure. I have searched in the SSAS DMV's, but didn't find what I needed; I only found the last processed time, but not the name of the user. Any suggestions?

Ssas Calculate Cumulative Month Value in MDX

I wanna calculate the cumulative values by month/year My dimensions period as follow: Period Dimension I ve tried any ways to perform this operation, but unsucessfull with member [Measures].[Valor Acumulado] as Sum(PeriodsToDate([Período].[Mês].[(All)]),[Measures].[Valor Pago] ) with member [Measures].[Valor Acumulado] as Sum(YTD([Período].[Mês].[(All)]), [Measures].[Valor Pago]) Or the cumulative calcule does not work or appears a Error Message CellOrdinal 1 VALUE #Error Q

Ssas OLAP datasets other than Adventureworks?

I'm looking to play around with various cube data to better understand it. I've also created a few of my own to play around with, but it's quite tedious to do, especially with saving the tables/views into the cube. Other than Adventure Works, are there any data sets that I can download to play around with? I've been having a hard time finding any of these via Google, MSDN, etc. I'm open to whatever!

One big SSAS tabular or multiple tabular?

I am just wondering what would be the best option : 1 - Create multiple SSAS tabular, every tabular is for a different business units , than reporting solution (Power bi) would be connected to these multiple tabular. 2 - Create multiple SSAS tabular, every tabular is for a different topic than create like a Master tabular to get data from these multiple tabular and my reporting solution would be connected to the Master tabular. 3 - Create one big SSAS tabular containing data from different topi

Performance Implications of Calculated Members in SSAS

I'm wondering if there are any performance implications from adding a lot of calculated members to my cube. On one hand, it's nice to have things defined once, located centrally, tested, and available for use from any client which doesn't support MDX. On the other hand, some of these members I'm adding might not be used very frequently, so I could just inline them in the one or two reports that might need them. Aside from the clutter of having unnecessary members hanging around, should I keep

Display different dimensions on columns and rows with SSAS and MDX

Hi I'm new to SSAS and MDX and I need to produce a ssrs report based on an OLAP cube. I can't figure out how to display different dimension members on columns and rows. The dimensions are rating and time on columns and clients and statuses on rows like this: | a rated | b rated | c rated | Year-3 | Year-2 | Year-1 | Year* | Total| good clients | medium clients| bad clients | total clients | status 1 | status 2 | status 3 | total status | -----------------------

Ssas MDX Query to Produce Revenue by Top N Advertiser

I need to create a report using SSRS 2008 that will show the top 10 advertisers by revenue. I wrote the MDX query in management studio as follows: Select [Measures].[Value] on 0, non empty generate([Dim Industry].[SK Industry ID], topcount([Dim Advertiser Branch].[Advertiser], [Measures].[Value],bdesc),10) on 1 from FuseCube I get a parse error stating: Query (5, 20) An expression was expected for the function argument and no expression was detected. My goal is to report the top 10 Ad

I am not able to get dimensions and fact values while browsing cube in SSAS 2005

I am new to SSAS. I have two tables, FactAnswers and DimDebit. one dimension and one fact. After creating cube when I try to browse dimensions and fact, I got nothing. I want to get AnswerValue from fact table and Debit values from Dim table. Everything you can see in this figure. Please guide me, Where I am wrong. Thanks

Ssas MDX calculation for count of sum within a range

I have a cube in SSAS with the following measures: NameID Number Date NameID points to a Person dimension with a Name string (there can be multiple records with the same NameID). Date points to a server-side date dimension. I'm looking for an MDX calculation I can include in the cube that will show the count of Person.Names where the sum of the Measure.Number fields for the date range given in the query is within a certain range (say 0-10, 11-20, etc). I've tried various flavors of COUNT(),

Writeback partition secuirty in SSAS

I have a writeback partition working pretty well in a cube. The users access it through Excel 2010. At the moment there are 3 measures in the cube. I'd like to only give users access to writeback to 2 of them while being able to read all 3. Is there a way to accomplish this? I'm very much an MDX novice if MDX is involved. Thanks in advance.

SSAS 3 fact tables, but only 2 relate to a certain dimension

I have a cube with 3 fact tables and 20 + dimensions that relate easily to all 3 fact tables and everything works fine except for the fact that one of the dimensions (Warehouse) is only related to 2 of the 3 fact tables. My problem I guess is a display issue. When the user is viewing measures from all 3 fact tables then drags over the Warehouse dimension, it simply repeats the grand total of the measure in the 3rd fact table for every possible value of Warehouse. This certainly makes sense to me

Ssas I want YTD amount for each category in my MDX query

I've one MDX query where i want to show each of my product month wise count and along with that want to show YTD, MTD, WTD etc. I've first created following MDx query on Adventure Works which give me YTD but its shows each months. instead i need output as below and I want to provide current month+year(Mar 2015) in MDX filter or where clause EXPECTED Results: Category YTD --------- ------ Bike 4500 Accessories 78000 Clothing 8900 Can you please correct below MDX query to ge

Ssas MDX query with count distinct filtered on another measure, that plays with other filters

in the SSAS AdventureWorks schema, I'm trying to figure out a distinct count of how many distinct customers have made Internet orders greater than $1000. I attempted to make an MDX query like this, and it seems to almost work, except that new measure seems to ignore the slicer axis / WHERE condition, showing the count of customers for all countries, not just Australia: With member [measures].[DistinctCustomersHighSales] as distinctcount( filter([Customer].[Full Name].Members, [Measures].[

How to manage attribute permissions on SSAS Tabular model

We have a Tabular model with several Fact tables and several Dim tables. We would like to manages roles so that specific roles will not be able to see members of a certain attribute within a dimension. So in an HR cube with a "Work Hour" measure - i would like to block a specific role from seeing the "Employee Name" attribute but still show the sum of "Work Hours" to the total employee. While using multidimensional, i simply used an MDX expression which filters on the "All" member of the dimen

Masking Dimension attribute / Security in SSAS

We have a cube where we implemented the dimension data level security based on ROLE. This security is working fine where we are restricting the user to see his records only. Now the Customer dimension has another Employee attribute. Based on the value of this field we want to restrict other dimension attributes. Like in the below example Manager_Id is the attribute that should be masked. We want to mask the attribute value of this field with "Employee” so that the restricted user only sees a mas

How to replace null with 0 in SSAS cube

I need to replace null with 0 in my SSAS cube. what is the proper way to achieve that? This is my current query: SELECT {([Measures].[Employee Age Count])} ON COLUMNS, { ([Dim Gender].[Gender Name].[Gender Name].ALLMEMBERS * [Dim Age Ranges].[Age Range ID].[Age Range ID].ALLMEMBERS ) } ON ROWS FROM ( SELECT ( { [Dim Location].[Location].[Location Grp Name].&[BOSTON] }) ON COLUMNS FROM [People Dashboard]) WHERE ( [Dim Location].[Location].[Location Grp Name].&[BOSTON] ) Result fr

Displaying SSAS measure in thousands or millions

I have an Olap cube created using Microsoft SSAS. Inside I have a many-to-many relationship between source transaction currency and required "Reporting" currency. This is all functional, however to display a dynamic currency symbol I am using the "Currency" format string default and passing in a custom LCID based on the currency selected. The problem with using the "Currency" format is the decimal places and large numbers. I am reporting millions of pounds/dollars and my CFO wants to see the

Ssas MDX: How to get the Total Sum of column in one more column

Here i have one attribute TotalPatientCnt values are (30,2013,39), I need TotalSum(2082) of the TotalPatientCnt in one more attribute. WITH MEMBER [ProgramNames] AS [DimPatientProgram].[Program Key].CURRENTMEMBER.MEMBER_CAPTION MEMBER [ProgramKey] AS [DimPatientProgram].[Program Key].CURRENTMEMBER.MEMBER_Key MEMBER TotalPatientCnt AS [Measures].[HealthPlanPatientCnt] MEMBER MetricPercent AS [Measures].[ProgramPatientKeyCnt] * 1 / TotalPatientCnt * 100 ,format_string = '#,##.00' SELECT {

How do you delete an SSAS cube?

According to this you can only delete Databases (or do I read it in an incorrect way?) even if the name of the page suggests cubes can be deleted. There is no Cube type objects on Server manager Studio either. Trying to delete the files directly from .../MSAS11.Mydatabase/Olap/Data doesn't work because they are open by some system process. But we have created several test solutions with their cubes that we would like to delete so users accessing the service don't see them. How can we delete

Ssas How to cast an MDX measure field?

I am very new to ssas and mdx. I am trying to compare the results I am getting from SSAS with the SQL database. Somehow for this particular query it does not match. The data is exactly the same. I could not see any casting function for MDX. Any idea what is happening? SQL: SELECT sum(CAST(LOS_MINUTES as BIGINT)) FROM [db].[dbo].[ FACT_WARD_STAY] Result is: 3311473500 MDX: SELECT NON EMPTY { [Measures].[Stay Minutes] } ON COLUMNS FROM [Ward Stays] Result is: -983493796

SSAS Tabular expression tables fail to load from SQL Server data source

We have been developing a new SSAS Tabular (1400) model, initially doing the PoC by loading the data directly from SQL Server (not using expressions). Once we had an idea of what we wanted, we refactored and did the data imports with Expressions to allow further Power Query (M) processing. At first, all seemed fine. Along the way we started to mash up other data from an ODBC datasource and didn't experience any issues during the process. This morning we opened the project to make some modificat

Ssas Replace a percentage of values in a column with another

I have a tricky problem, maybe one of you can help. I have a column with several distinct values, one of which is "Not Available". I would like to create a calculated column by replacing those "Not Available" values with 4 new values, say "Value 1", "Value 2", etc.. distributed equally i.e. 25% will be "Value 1", 25% will be "Value 2" etc. I really appreciate any help I can get. I should mention that my data lives in SSAS TDM so I can't use the query editor. Thanks! Below you will find som

Ssas MDX -- getting tooltip (or left-hand) data to show in a query

I would like to build a query to get the movie sales per year like this: 2014 2015 MOVIE SALES SALES_IN_US SALES SALES_IN_US Avatar $12,2039 $783 $912,2039 $783 Spider-man $18,293 $11 $188,293 $11 Titanic $100,203 $372 $6100,203 $372 How would Add in additional "properties" into either the tooltip or the second column (after MOVIE but before the me

Ssas Convert string to decimal in Cube

I'm creating a cube and the person who wrote the software made a productSize field in the data a varchar(50) instead of a numeric data type. It is also unusual in that it might, for instance, contain a 9 or a 4.5x2. Can a calculated measure convert the varchar to a decimal in this situation? Edit: Now I realize I can create a Named Calculation by right clicking the table column in the DSV. I still need to figure out how to code the expression though. This is all I have so far... CASE WHEN

SSAS Time Series, predict when something will fail

I would like to use SSAS and a TimeSeries Mining Structure to predict when the predictable value will reach a certain threshold. For Example: SELECT [Info Key], PredictTimeSeries([Free Space], 200) as ForcastedSize FROM [Drive Module Information] WHERE ForcastedSize < 10000 --(<< this does not work) This will tell me the date that it forecasts that the drive space will be below 10000. How do I write an MDX query to accomplish this? Thanks, Brian UPDATE 1: I think I can accomplish

How should one define a second date hierarchy in SSAS?

I have a time table and a sales fact table with multiple dates (order received date, ship date). I want to define a hierarchy on the order received date and another hierarchy on the ship date. The cube editor in visual studio allows me to define multiple relationships on these table, but I don't see a way in the hierachy editor to define which relationship to use for the hierarchy. Am I missing something, or am I supposed to insert the time table into the model twice, which single relations

Ssas Get next member that matches criteria mdx

I am attempting to return a measure two periods from the current member on the time dimension however I need to only include periods that match a certain criteria (Is_Business_Day = true) I currently have: ( [Date].[Calendar].CURRENTMEMBER.NEXTMEMBER.NEXTMEMBER, [Measures].[SOME MEASURE] ) Which accurately returns the value of the measure two members in the future, but now I need to additionally apply the filter, but can't quite figure out how to do so. EDIT: My thinking is that I

Ssas Creating named set

I am trying to create a named set that would return sum of selected members for any measure. When I deploy I am getting error function expects a tuple set expression for the argument. A string or numeric expression was used. Please review my code and suggest solution. I have a dimension named Uline and this named set should sum selected 4 members of this dimension for any measure sum({[Uline].[CU - (46)], [Uline].[FRU - (45)], [Uline].[GL - (38)], [Uline

How to sort a numeric dimension in ssas

In one of my dimensions I have 4 columns with the data type tinyint but in ssas after processing the cube, its been assigned the data type system.byte. I dont know it did that. And because of this my data sort is like 1, 10, 2 etc. but I want it to be 1, 2, 3 etc. How can I overcome this issue?

Ssas How probabilities are calculated in MS SQL Server Analysis Services - Decision Tree model?

I am a new user of MS SSAS models and I'd like to know how the probabilities are being calculated within the node distribution of Decision tree model. Example: I've created a model for sample data ('Iris') using SSAS decision trees and inferred that probability values appeared were irreleveant to data and so, I'd like to know how these values are being calculated. For your reference, please find the screenshot from the following, From the above, we could infer that total cases supported by

Ssas MDX query taking time

I have MDX query as mentioned which is taking too much time to execute. vendor dimensions have ~90k data. SELECT NON EMPTY { [Measures].[Amt] } on Columns FROM ( SELECT ({ Filter([Vendor].[Hr_Vendor].[Vendor], [Vendor].[Hr_Vendor].CURRENTMEMBER.Name='QUINTILES TRANSNATIONAL KOREA CO LTD'), Filter([Vendor].[Hr_Vendor].[Vendor],[Vendor].[Hr_Vendor].CURRENTMEMBER.Name='QUNTICO INC'), Filter([Vendor].[Hr_Vendor].[Vendor],[Vendor].[Hr_Vendor].CUR

SSAS Tabular View

I am in the process of building my first tabular view and have a question. The tabular model I have has 3 tables based on 3 views that exist on SQL server. An incident view, a users view and a category view. On the incident view, I have 2 columns (AssignedUser,CreatedUser) which both can be linked to the users view based on user ID, however I have been told only one relationship between tables can be active at one time? If this is the case how would I link both assigned and created user back t

Ssas ParallelPeriod issues when time is not on axis

What's wrong with the calculated member [Amount LM] (=amount last month) in this query? It seems to work if I specify the time on rows but fails if I don't, even though I have a date in the where clause. WITH MEMBER [Amount LM] as ( [Measures].[Amount], ParallelPeriod ( [Date].[Year - Quarter - Month - Date - Hierarchy].[Year and Month], 1, [Date].[Year - Quarter - Month - Date - Hierarchy].CurrentMember ) ) SELECT {[Am

  1    2   3   4   5   6  ... 下一页 最后一页 共 10 页