Hot answers tagged

11

MDX and SQL are in no way the same, and often not even comparable, as they are querying multidimensional and relational databases respectively. You cannot query your existing relational database with MDX. The main advantage of using a multidimensional model and using MDX to query it is that you are querying pre-aggregated data and that MDX is optimized to ...


8

OLAP Cubes/databases have the following characteristics: Obtain already aggregated information according to the needs of the user. Easy and fast access Ability to manipulate the aggregate data in different dimensions A cube uses classical aggregation functions min, max, count, sum, avg, but can also use specific aggregation functions. MDX versus SQL : MDX ...


6

General notes First of all, T-SQL and MDX are 2 completely different beasts. SQL is intended to query tabular relational data, while MDX is intended to query multidimensional data. I'll start with addressing your question about common mistakes. In my opinion the most common mistake is to try and apply SQL knowledge to MDX. You really need to get into the ...


5

Assuming you have properly set the ValueColumn property of your CreatedOn attribute in your dimension (so that MemberValue comes back a date data type) then the following should work: select {[Measures].[Your Measure Here]} on 0, [DimAccounts].[AccountPK].[AccountPK].Members on 1 from [Your Cube] where { Filter( [DimAccounts].[CreatedOn].[CreatedOn]....


5

I tried to duplicate what you have using a multidimensional Contoso cube with a measure that is Min Inventory Day in Stock and didn't encounter your issue. I think it has to do with the current context when you aggregate [adhoc]. I don't have enough info to duplicate exactly. But this phrase caught my eye when looking at the specifications for MIN(): If ...


5

Your main problem is that you are storing a measure in your dimension. You will keep running into issues with multiselect and or aggregation unless you store the measure where it belongs, namely in the fact table. Either change your ETL to copy the max_weight in your fact table, or use a named query in your DMV joining your fact table to your dimension ...


4

Try WITH Member [Age Band] Instead of WITH set[Age Band] Since you are doing a calculation based on the [Measures].[Age] Try the following and see if you can work with WITH MEMBER with member [Age Band] AS IIF([Measures].[Age]<26,"0-26",IIF([Measures].[Age]<51,"26-51","Over 50")) SELECT {[Age Band] } DIMENSION PROPERTIES ...


4

The basic issue is that the SQL type time is mapped to string in SSAS. So the way I did this came from JNK's comments, so all props to him. In order to use time formatting correctly, I return the following (SELECT DATEDIFF(n, CAST('' AS Time), dbo.FactWorkDaySummary.StartTime) / 1440.0) AS StartTime in my view. In my cube I have a calculated measure to ...


4

Please explain in more detail what you meany by "groupers"? For instance, if you want to rank a member within a set, you need to tell SSAS what the set is that you are ranking within. RANK( Tuple, Set, Measure to rank by) So RANK( Machines.Machines.CurrentMember, Machines.Machines.AllMembers, Measures.Efficiency) Now, I think that what you are trying ...


4

Without sample data or structure it would be hard to provide the exact code, but here goes. You need to look into the PARALLELPERIOD function. That function takes a Level from a hierarchy and navigates back a number of steps in that level. Also, I'm using the assumption here that you want to use the TAIL() function to get the last month in your dimension ...


3

An alternative, and sometimes better performing approach could be to add an attribute to your date dimension along the lines of "IsFuture". Depending on your data load/processing schedule you could have a field in your datawarehouse or a calculated column in your dsv precalculating if the date is in the future or not. Instead of performing an on the fly ...


3

To get this to work you need: To add a column in your DMV to produce the square of the raw value. SELECT [Value], square([Value]) as ValueSquared FROM factTable A measure which sums the raw values. [Sum of Values] A measure which sums the square of the raw values. [Sum of Square] A count measure [Count of Values] The Calculated measure syntax is: ...


3

The StDev function does operate at a rollup grain per the first parameter you pass in. If you always want to calculate the StDev at the lowest level then you can write some mathematically equivalent measures and avoid the StDev MDX function. This approach is described here. Let me know if that doesn't make sense. The MDX would be: (([Measures].[Sum of ...


3

The way `PARALLELPERIOD' works is so that it moves up the hierarchy up to the specified level and then moves back or forward the number of periods, then moves down again using the same path. In this 'moving down the path' parallelperiod will descend the same member ordinal again. So if it moved up from the first member (in your example january) it will move ...


3

If you are browsing in Excel and want to be able to see dimension attribute members that do not have corresponding rows in a fact table (measure returns nothing), there is a pivot table setting you can use. Access the PivotTable Options dialog box by right clicking on the pivot table and choosing PivotTable Options. On the Display tab, check the box next ...


3

Writeback is always written to the leaf level of your dimension, and has to be because of the structure of your writeback table (where the difference between the original value and the new value is stored). Have a look at the writeback tables in your data source and you'll see it contains fields for all your measures, and the key of all your dimensions, so ...


3

Assuming you already have designed a measure Total that counts the accidents a dimension Severity that describes the severity of the accident you can define a calculated member such as: ([Measures].[Total],[Severity].[Severity].&[Fatal])


2

Try adding the All member into the set on rows: SELECT NON EMPTY {[Measures].[Order Count]} ON 0 ,NON EMPTY { [Date].[Year].[Year].ALLMEMBERS* [Date].[Quarter].[Quarter].ALLMEMBERS* { [Order].[Customer].[Customer].ALLMEMBERS ,[Order].[Customer].[All] } } DIMENSION PROPERTIES ...


2

This helped me in getting it working! Just go to your cube and right click. Choose “Distinct count” from the begining. When you click ok. The new Measure Group will be created automatically. And you wont see any warning messages. I hope this post helps anyone who was stuck on creating Distinct Count Measure like me. Complete definition is here:


2

The limitation of a single distinct count per measure group is related to performance. To get both distinct count measures to show up in the same measure group, you can use the workaround suggested here by Darren Gosbell. best of both worlds...performance benefit from each DC-measure residing in its own measure group, UX benefit from having all related ...


2

I don't have your SSAS database so I can't test the syntax, but I believe you would need to write your MDX as follows (notice the THIS= addition): SCOPE ([Measures].[Total]); THIS = SUM([Data Source].[Interpolation Method].&[3],[Measures].[Total]); END SCOPE; See the documentation for syntax details.


2

You need to turn quoted identifiers on in the T-SQL job step. SET QUOTED_IDENTIFIER ON; By default, SQL Server Agent treats quotation marks as if QUOTED_IDENTIFIER is OFF, which means items in quotes are treated as literals. Turning QUOTED_IDENTIFIER ON will allow those items to be treated as column names. See this MSDN blog entry about the issue.


2

After doing a bit more research, I found that the SOLVE_ORDER member property can get SSAS to use the format string from the MTD vs. Prev. MTD % member. The following code worked for me: MEMBER [ClosedDate].[Date].[MTD vs. Prev. MTD %] AS [ClosedDate].[Date].[MTD vs. Prev. MTD] / [ClosedDate].[Date].[MTD], FORMAT_STRING='Percent', SOLVE_ORDER=1


2

I ended up solving this using the Format function, similar to the following: MEMBER [RO Count] AS Format([Measures].[ROH Count], "General Number") I'll keep the question open for a bit, in case anyone has a cleaner answer. I would have liked to pull the format string in from the calculated members, instead of hardcoding it, but I couldn't figure out how.


2

Probably some MDX COUNT function would do the trick, but as the real fact table is somehow more complex and there is already a constant value of 1 identifying this specific rows among other related info, we simply modified the formula to create MEMBER CURRENTCUBE.[Measures].[% WT] AS [Measures].[Weight]/[Dim Truck].[Max_weight].currentmember.properties("...


2

As you say it's hard to try out without your actual cube and it depends a bit on how your dimensions are layed out, but I think you can get there using the BOTTOMCOUNT function if you create a measure on seq. BottomCount(Set_Expression, Count [,Numeric_Expression]) Your set_expression could be a crossjoin between the dimensions you care about and you ...


2

If you want to make sure the code you are running is exactly the code run when you process manually you should click the script button in SSMS and use that XMLA script. If you execute a series of steps you should script each step. This will generate the scripts as XML so you can execute in SSMS and that will 100% do the same as clicking the OK button on the ...


2

I'm somewhat surprised that your partitions are processed without you specifying "process affected objects" anywhere. Maybe you are confusing the output "partition processing operations" with the actual processing of the partitions. The logic to process partitions when updating dimensions is this. if you process update a dimension and only new members were ...


1

The Function NOW() returns a DateTime, you cannot compare that to a Member. Dependending on the definition of the Time dimension you can convert the result of NOW() to a member using MDX-Function StrToMember, but there is no lower than on Members. I use keys of the format "yyyyMMdd" for my time dimension. So that key is comparable like this: WHEN [Time].[...


Only top voted, non community-wiki answers of a minimum length are eligible