Hot answers tagged

11 votes
Accepted

Good example of MDX vs SQL for analytical queries

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 ...
user avatar
  • 15.4k
10 votes
Accepted

8000 character limit in OPENQUERY against Analysis Server

I need to query Analysis Service with OPENQUERY, handle the query of length greater than 8000, save the results to a temporary table or select them directly. sql-serverssasmdxEdit tags Instead of ...
user avatar
7 votes

Good example of MDX vs SQL for analytical queries

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 ...
user avatar
7 votes
Accepted

What are the most relevant similarities between T-SQL and MDX?

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 ...
user avatar
  • 15.4k
5 votes
Accepted

MDX - Getting the last 6 month Dynamically

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 {[...
user avatar
  • 1,365
5 votes

How to calculate a percentage over a dimension attribute and get correct aggregates?

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 ...
user avatar
  • 15.4k
4 votes

MDX calculated member to band a measure

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 [...
user avatar
4 votes

How can I get the measures of the last month with MDX?

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 ...
user avatar
  • 15.4k
4 votes

8000 character limit in OPENQUERY against Analysis Server

You could try to avoid openquery and use something like SSIS or Azure Data Factory or some other ETL tool which doesn't have the 8K character query limit. Alternatively if you want to keep using ...
user avatar
  • 15.4k
3 votes

SSAS How to calculate Standard Deviation

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 ...
user avatar
3 votes
Accepted

SSAS How to calculate Standard Deviation

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 ...
user avatar
  • 1,365
3 votes

Altering SSAS writeback behavior?

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 ...
user avatar
  • 15.4k
3 votes
Accepted

MDX datetime comparison

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 ...
user avatar
  • 15.4k
3 votes

Helpme undestand MDX exists function

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 ...
user avatar
  • 824
3 votes
Accepted

In MDX what is the ParallelPeriod of UnknownMember?

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 '...
user avatar
  • 15.4k
3 votes
Accepted

SSAS Calculated Member Conditional Count

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: ([...
user avatar
  • 1,861
2 votes
Accepted

Get distinct count on calculated member?

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 ...
user avatar
2 votes

Get distinct count on calculated member?

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 ...
user avatar
2 votes

SSAS / MDX Query - Need row total

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]....
user avatar
  • 123
2 votes
Accepted

Prevent SQL Job Agent from interpreting a quotation mark as apostrophe

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 ...
user avatar
  • 67.2k
2 votes
Accepted

SSAS Calculated measure with SCOPE.

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]...
user avatar
  • 15.4k
2 votes
Accepted

Getting formatted MDX values using OPENQUERY

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 ...
user avatar
2 votes
Accepted

Overriding a row FORMAT_STRING with a column FORMAT_STRING in MDX

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 [...
user avatar
2 votes

How to calculate a percentage over a dimension attribute and get correct aggregates?

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 ...
user avatar
  • 161
2 votes

Equivalent of SQL Not Exists in MDX (or finding another way to accomplish same result)

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 ...
user avatar
  • 15.4k
2 votes

SSAS Cube Process Manually works but MDX query doesnt work

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 ...
user avatar
  • 15.4k
2 votes

ProcessUpdate of the dimension triggers processing of all partitions of all measure groups in the cube

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 ...
user avatar
  • 15.4k
1 vote

SSAS How to calculate Standard Deviation

If you want to use it on Raw data (that means, on the entire SQL table) it should run on the SQL level, no need to aggregate for that. So the standard deviation on code 1 works, so I would suggest: ...
user avatar
  • 570
1 vote

MDX datetime comparison

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

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