Anyone that has tried to learn MDX will know that, when you make a mistake somewhere in your code, the error messages that Analysis Services gives you are pretty unhelpful. It was suggested to me recently while I was teaching an MDX course that I should blog about common error messages and what they actually mean; so here’s a list of a few example queries using Adventure Works that return confusing errors, the error messages themselves, and details on how to solve the problems. I’ve deliberately concentrated on query-related errors rather than calculation-related errors (that can be a future blog post); if you can think of any more errors that I should cover please leave a comment.
1) Query causing error:
SELECT
{[Measures].[Internet Sales Amount]} ON COLUMNS
[Date].[Calendar Year].MEMBERS ON ROWS
FROM [Adventure Works]
Error message: Query (3, 1) Parser: The syntax for ‘[Date]’ is incorrect.
The first step to solving this fairly simple syntax error is understanding the values in brackets in the error message. (3,1) indicates that the error is at character 1 on the third line of the query, where we have the expression [Date].[Calendar Year].MEMBERS; we should also see a red squiggly underneath this text in SQL Management Studio. There’s nothing wrong with this expression though, apart from the fact that it’s in the wrong place: what has happened is that we’ve forgotten to include a comma after COLUMNS immediately beforehand. If we put one in, the query runs.
Solution:
SELECT
{[Measures].[Internet Sales Amount]} ON COLUMNS,
[Date].[Calendar Year].MEMBERS ON ROWS
FROM [Adventure Works]
2) Query causing error:
SELECT
{[Measures].[Internet Sales Amount]} ON COLUMNS,
[Date].[Calendar].[Calendar Year].MEMBERS.CHILDREN ON ROWS
FROM [Adventure Works]
Error message: Query (3, 1) The CHILDREN function expects a member expression for the 1 argument. A tuple set expression was used.
This is a very common error that people encounter while learning MDX, and it all comes down to understanding the difference between sets, tuples and members. In a lot of situations Analysis Services is very forgiving: if it expects a set and you give it a single member, then it will cast that member into a set with one item in it for example. It can’t do this for you all the time, though, and you do need to understand what kind of object each function returns and/or expects for a parameter. In this case, the problem is that the .CHILDREN function needs to be passed a member and the .MEMBERS function returns a set (strictly speaking, as the error says, it’s a set of tuples); therefore we can’t use the two functions together. If we want to find all of the children of all years, we can use the DESCENDANTS function instead, which can accept a set as its first parameter.
Solution:
SELECT
{[Measures].[Internet Sales Amount]} ON COLUMNS,
DESCENDANTS(
[Date].[Calendar].[Calendar Year].MEMBERS
, [Date].[Calendar].[Calendar Semester])
ON ROWS
FROM [Adventure Works]
3) Query causing error:
SELECT
[Measures].[Internet Sales Amount], [Measures].[Internet Tax Amount] ON COLUMNS,
[Date].[Calendar].[Calendar Year].MEMBERS
ON ROWS
FROM [Adventure Works]
Error message: Parser: The statement dialect could not be resolved due to ambiguity.
Analysis Services supports no less than three query languages: MDX, DMX and a very limited subset of SQL. As a result, when you run a query it needs to work out what query language you’re using and can easily get confused if you make a mistake. In the query above we’ve given a list of the two measures we want to see on the columns axis, but we’ve forgotten to surround this list in braces to turn it into a set – and it’s a set that is required for the axis definition. This is an error that is commonly made by people with a background in SQL, and indeed the problem here is that the error has made the query look a bit too much like SQL or DMX. Putting in braces where they’re needed fixes the problem and removes the ambiguity.
Solution:
SELECT
{[Measures].[Internet Sales Amount], [Measures].[Internet Tax Amount]} ON COLUMNS,
[Date].[Calendar].[Calendar Year].MEMBERS
ON ROWS
FROM [Adventure Works]
4) Query causing error:
SELECT
{[Measures].[Internet Sales Amount1], [Measures].[Internet Tax Amount]} ON COLUMNS,
[Date].[Calendar].[Calendar Year].MEMBERS
ON ROWS
FROM [Adventure Works]
Error message: Query (2, 2) The member ‘[Internet Sales Amount1]’ was not found in the cube when the string, [Measures].[Internet Sales Amount1], was parsed.
A fairly straightforward error this: we’ve tried to reference a member that doesn’t exist in our query – it’s the extra 1 on the end of the name that’s the problem. The way to avoid this is to always let Analysis Services generate unique names for you, and you can do this by dragging the member (or any other object) from the metadata pane in SQL Management Studio into the MDX query pane when you’re writing queries. Here, using the correct member unique name solves the problem.
Solution:
SELECT
{[Measures].[Internet Sales Amount], [Measures].[Internet Tax Amount]} ON COLUMNS,
[Date].[Calendar].[Calendar Year].MEMBERS
ON ROWS
FROM [Adventure Works]
Note that for dimensions other than the Measures dimension, what happens in this scenario depends on how you’ve set the MDXMissingMemberMode property. By default if you write something that looks like it could be an MDX unique name, but which isn’t actually the unique name of a member on a hierarchy, Analysis Services will simply ignore it. So the following query returns nothing on rows because the year 2909 doesn’t exist in our Calendar hierarchy:
SELECT
{[Measures].[Internet Sales Amount], [Measures].[Internet Tax Amount]} ON COLUMNS,
{[Date].[Calendar].[Calendar Year].&[2909]}
ON ROWS
FROM [Adventure Works]
And worse, the in this query a genuine syntax error is completely ignored too:
SELECT
{[Measures].[Internet Sales Amount], [Measures].[Internet Tax Amount]} ON COLUMNS,
[Date].[Calendar].[Calendar Year].MAMBERS
ON ROWS
FROM [Adventure Works]
Hi Chris, my MDX is as follows:
Sum
(PeriodsToDate
([Date].[Financial].[Financial Year],[Date].[Financial].CurrentMember),
[Measures].[Budget]
)
And I have got err as follows:
“The Form View cannot be displayed because the MDX script has the following syntax error: ‘Parser: The end of the input was reached. [Line:0; Column:0]’ To correct this error, click Script View on the toolbar, and then edit the MDX script to correct the syntax.”
Do you have any idea what that would be? Thank you in advance for your help, Chris.
Cheers, Shirley
Hi Shirley, it sounds like you’re missing a comma, a bracket or a semi-colon somewhere in your code.
Thank you! Query / solution #3 fixed my problem – that’s a gotcha that I won’t forget. Appreciated.
Hi Chris,
I am trying to filter on a date range and getting the following error.
Filter:
SET [FilterDate] as
filter ([Claims Due Date].[Date].[Date].Members,[Claims Due Date].[Date].MemberValue>=cDate(“04/01/2014”) : [Claims Due Date].[Date].MemberValue<=cDate("11/20/2014")
)
Error:
The Range function expects a member expression for the 1 argument. A string or numeric expression was used.
Any ideas on what I am doing wrong.
Regards, Janet
Hi Janet,
I think you need an AND condition here, not a range. Try
SET [FilterDate] as
filter ([Claims Due Date].[Date].[Date].Members,[Claims Due Date].[Date].MemberValue>=cDate(“04/01/2014″) and [Claims Due Date].[Date].MemberValue<=cDate("11/20/2014")
)
The range operator : takes two members on either side, for example something like this
[Claims Due Date].&[Date].[01042014] : [Claims Due Date].&[Date].[20112014]
… which is likely to be a more efficient way of solving the problem than using the filter() function as in the previous example.
Thank you for your quick reply, Chris.
When I use the AND I receive no results yet, when I just use the following I get results that would fall between those two dates.
SET [FilterDate] as
filter ([Claims Due Date].[Date].[Date].Members,[Claims Due Date].[Date].MemberValue>=cDate(“04/01/2014”) )
Your suggestion above is to use the range but as a where clause instead of a function?
I tried using this [Claims Due Date].&[Date]>=[20140401] : [Claims Due Date].&[Date]<=[20141120] and received an error " The dimension '[20140401]' was not found in the cube when the string, [20140401], was parsed.
This however does receive results:
[Claims Due Date].[Date].&[20140401]
as does this:
[Claims Due Date].[Date].&[20141120]
It seems determined to not allow me to use a range.
I think you’re getting confused here between what’s part of the unique name of a member and what’s a value. Can you try something for me? Open a new MDX query window in SQL Server Management Studio, expand the Date hierarchy on the Claims Due Date dimension and drag the member for 1st January 2014 into the query window. Does it display [Claims Due Date].[Date].&[20140401] ? If so, then try
SET [FilterDate] as [Claims Due Date].[Date].&[20140401] : [Claims Due Date].[Date].&[20141120]
In this example [Claims Due Date].[Date].&[20140401] is the unique name of the member 1st January 2014, and when you are using : you can only put member unique names on either side. The : gets a set of members from the member on the left hand side to the member on the right hand side. You can’t use operators like > or = with member unique names.
I’m sorry. I have been trying so many different scenario’s I wound up giving you some bad information. The actual format is [Claims Due Date].[Date].&[20140401]. One thing I had tried was this SET [FilterDate] as { [Claims Due Date].[Date].&[2014-04-11] : [Claims Due Date].[Date].&[2014-11-20] }, which I see my error from what you sent, I had { }. Removing those brackets works.
The reason I initially sent this format “04/01/2014” is because I am using parameters that are to be set by the date picker. Now that I have the range piece working, I will work on converting the dates.
I thank you very much for your assistance and patience. I am VERY new to this and have had no training.
Regards and thank you, Janet
Hi Chris,
I am a beginner in MDX ..
select {([Date].[Calendar].currentmember.name)}on 0
from [Adventure Works]
Above query gives me error “Query (1, 8) The function expects a tuple set expression for the 1 argument. A string or numeric expression was used.”
I can see that it is fixed when I re-frame it as below:
with member A
as [Date].[Calendar].currentmember.name
select A on 0
from [Adventure Works]
But I am not able to grasp the difference! If it lets me select the round-about way, why doesn’t it let me select the direct way?
Hi Rosemary,
The expression [Date].[Calendar].currentmember.name returns text, and you can’t put text inside a set. A set can only contain members or tuples (which are objects), and it’s a set that you put on rows or columns in a query. The MDX basic concepts video here might help you understand this better: http://blog.crossjoin.co.uk/2012/10/28/free-mdx-training-video-now-live/
Chris
Thanks for that lightning-fast response!
That made me try out “select {([Date].[Calendar].currentmember)}on 0 from [Adventure Works] ” which worked 🙂
Hi Chris,
In database, there are many hierarchies we removed one hierarchy and when processed the cube getting the below error in MDX calculations.
MdxScript ( Data Base Name ) The [ Level] plane was ‘ [ Dimension ] . [Hierarchy ] . [ Level] ‘ not found while parsing the string you see in the cube .
The END SCOPE statement does not match the opening SCOPE statement .
MdxScript ( Data Base Name ) END SCOPE statement does not match the opening SCOPE statement match .
The MDX script contains at least one error
Is it because of data issue?
No, it sounds like you have an MDX calculation (probably a SCOPE statement) that references the hierarchy you have deleted. You either need to put the hierarchy back or rewrite the MDX expression that references it.
Thank you Chris.
We will rewrite the MDX expression.
Dear Chris,
We are currently facing low performance when using the below MDX Query.
I tried to rewrite the query by removing the FILTER functions and used respective member for ‘E6’ from attribute hierarchy but still no improvements.
Could you suggest to improve the performance for the below queries.
In Attribute Hierarchy, there are two different ways we get the dimension member.
[Measure Item].[Measure Item].MEMBERS and [Measure Item].[Measure Item].[Measure Item].MEMBERS which one will give better performance
SCOPE([Date Dynamic].[Date Dynamic].&[Q]);
SCOPE(FILTER([Measure Item].[Measure Item].MEMBERS,
[Measure Item].[Measure Item].Properties(“Measure Item Characteristic”) = “E6”));
SCOPE([Date Of Posting].[Quarter].members);
THIS=([Date Dynamic].[Date Dynamic].&[C],
[Date Of Posting].[Quarter].Currentmember)-
([Measures].[Value],[Date Dynamic].[Date Dynamic].&[C],
[Date Of Posting].[Quarter].currentmember.prevmember);
END SCOPE;
END SCOPE;
END SCOPE;
Thanks in advance
This isn’t a query – it’s a scope statement! Anyway, there’s nothing much wrong with this scope statement so the problem must be elsewhere, maybe in the query, more likely in the cube design or in another calculation.
Regarding your question, [Measure Item].[Measure Item].MEMBERS and [Measure Item].[Measure Item].[Measure Item].MEMBERS are different sets so they will result in the calculation being applied in slightly different places, possibly giving you different results, possibly also giving different performance. [Measure Item].[Measure Item].MEMBERS will return all members from the Measure Item hierarchy, including the All Member; [Measure Item].[Measure Item].[Measure Item].MEMBERS will just return the members from the Measure Item level of the Measure Item hierarchy.
Thank you Chris.
Could you please suggest what are all the things we need to check in cube design to increase the performance.
Thanks in advance
The two articles referenced here: http://blog.crossjoin.co.uk/2010/03/23/query-performance-tuning-chapter-from-%E2%80%9Cexpert-cube-development%E2%80%9D-available-online/ are a good place to start
Hi Chris,
For quarterly calculations currently we are facing low performance while using the below MDX Query.
Tried replacing Filter functions with the attribute member itself for example rather than “Measure Item Characteristic”) = “E6” replaced with &[E6].
Could you please suggest me improve the performance in the below query.
And also which attribute hierarchy will have high performance.
[Date Of Posting].[Quarter].currentmember or [Date Of Posting].[Quarter].[Quarter]currentmember
SCOPE([Date Dynamic].[Date Dynamic].&[Q]);
SCOPE(FILTER([Measure Item].[Measure Item].MEMBERS,
[Measure Item].[Measure Item].Properties(“Measure Item Characteristic”) = “E6”));
SCOPE([Date Of Posting].[Quarter].members);
THIS=([Date Dynamic].[Date Dynamic].&[C],
[Date Of Posting].[Quarter].Currentmember)-
([Measures].[Value],[Date Dynamic].[Date Dynamic].&[C],
[Date Of Posting].[Quarter].currentmember.prevmember);
END SCOPE;
END SCOPE;
END SCOPE;
with member A
as [Date].[Calendar].currentmember.name
select A on 0
,[Measures].[Gross Profit Margin] on 1
from [Adventure Works]
when I am executing above mdx query ,I got below error. why this is happening
The Measures hierarchy already appears in the Axis0 axis.
Your calculated member definition doesn’t specify a hierarchy, so by default A becomes a calculated measure – and you can’t put members from the same hierarchy on two different axes. The solution is to put A on the Calendar hierarchy of the Date dimension, something like this:
with member [Date].[Calendar].A
as [Date].[Calendar].currentmember.name
select {[Date].[Calendar].A} on 0,
{[Measures].[Gross Profit Margin]} on 1
from [Adventure Works]
Hi Chris
Today I read some information about you on net.
so, I am requesting to you,Kindly provide solution of below problem.
I am confused because,don’t know difference between Members,All and AllMembers.
with example.
Hi Narendra, the AllMembers function returns every member from a hierarchy including any calculated member. Members.All doesn’t look like real MDX to me – are you talking about the All Member? The All Member is the artificial member that is created at the top of every hierarchy by default.
Hi Chris
Can you explain difference between Conformed dimension and Role playing dimension and both dimension can be same.? and what is use of Degenerate dimension in real environment.?
also Bridge dimension ?
I’m not sure what these questions have to do with this post…? These are general dimensional modelling questions, and I recommend you read books and articles by people like Ralph Kimball (for example see http://www.kimballgroup.com/data-warehouse-business-intelligence-resources/kimball-techniques/dimensional-modeling-techniques/conformed-dimension/) to understand these terms.
Hello, I have an Excel issue that no one I know is able to solve. I just upgraded to using Office 365 with which my Excel got upgraded to Excel 2016. A power pivot report that used to run fine before, suddenly has error in 2 columns of the power pivot in the back end. It has an extra bracket. Earlier, I would just erase the extra bracket, press enter and whole column would clean up but now the error ‘The end of column was reached’ appears followed by text telling me that the other column has an error. After which I can’t even click on any other column and yet it doesn’t let me correct, remove or improve its own formula either. I don’t know what’s wrong. Any help will be much appreciated.
The formula in the cell of the Key column is [date]&”_”&[country]&”_”&[os_name]&”_”&[network]&”_”&[group_network]
If it used to work fine and now doesn’t, that sounds like a bug in Excel to me. I suggest you open a support case with Microsoft.
Hi Chris,
We are trying to execute the MDX query BUT we are getting the below error:
Query (44, 33) A table of multiple values was supplied where a single value was expected.
BUT once we remove one of the dimension it is successfully executing. What does that mean?
And also if i seperately execute the dimension i see no error.
Regards,
Nitin
That looks like an error message coming from a measure you’re using in the query. Try to add/remove measures from the query to find out which measure is causing the problem, then post what the definition of the measure is.
WITH
MEMBER [Account].[Calculation_581527319921311744] AS
‘([Account].[Sales Margin] / [Sales Margin].[Sales])’,
SOLVE_ORDER = 127
SET [Tableau Set 0] AS
‘[Total Major Customer].Generations(4).Members’
MEMBER [Total Major Customer].[Tableau Slice Member 1] AS
‘Sum([Tableau Set 0])’,
SOLVE_ORDER = 127
SELECT
{[Sales Margin].[Sales]} DIMENSION PROPERTIES [MEMNOR],[ANCESTOR_NAMES] ON COLUMNS, NON EMPTY Generate( [Entity].Generations(3).Members, Descendants([Entity].CurrentMember, [Gen5,Entity], LEAVES) ) DIMENSION PROPERTIES [MEMNOR], [ANCESTOR_NAMES] ON ROWS FROM [MMSales] WHERE ([Total Major Customer].[Tableau Slice Member 1])
when I run this MDX I receive an error saying syntax error near WHERE. I need help. I don’t know what is wrong with the query.
As far as I can see, this is a problem with a circular reference: you have a calculated member that references a named set and this measure is used in the Where clause – but the named set has to be referenced in the context of the Where clause. I don’t get a syntax error when I do something similar to this though, I get an infinite recursion error, so there could be some other problem here too but the query as it stands will never work. Is this MDX generated by Tableau? If so, they they will need to fix it.
Hi Chris
I download a AdvantureWorks Database from online and now I am doing process cube but measures are not having values and after this a checked in cube solution window and find below error at measure grorup tab:
Error loading metadata:the cube collection has been updated on the server
how to solve this error.please help.
Hi Chris
Let me clear Difference between Named Sets and Calculated Members?
Hi Chris
I am learning MDX.so I am getting below query and related error
WITH MEMBER [Measures].[specialDiscount]
As [Measures].[Internet Standard Product Cost] *.0001,
FORMAT_STRING = ‘Percent’
MEMBER [Measures].[Test]
As [Measures].[ActualCost]*100
MEMBER [Measures].[ActualCost]
As [Measures].[Internet Standard Product Cost]-[Measures].[specialDiscount]
MEMBER [Organization].[Label] AS [Organization].[Currency Code].CURRENTMEMBER.MEMBER_CAPTION
SELECT {[Measures].[Internet Sales Amount],[Measures].[Internet Gross Profit],[Measures].[Internet Tax Amount]
,[Measures].[Internet Standard Product Cost],[Measures].[specialDiscount],[Measures].[ActualCost]
,[Measures].[Test]} ON 0,
{[Organization].[Label]} ON 1
–{([Product].[Category].[Category],[Product].[Class].members,[Date].[Calendar Year].members)} ON 1
FROM [Adventure Works]
Error:-
Query (8, 2) The ‘Organization’ dimension contains more than one hierarchy, therefore the hierarchy must be explicitly specified.
Execution complete
Hi Narendra, the problem here is that when you are defining the name for your calculated member on the Organization dimension, you need to specify the dimension name, a hierarchy name and the name of the new member – a three part name. [Organization].[Label] only has two parts. In any case, I think I can see what you want to do here and your approach isn’t going to work. Try something like this:
WITH MEMBER [Measures].[specialDiscount]
As [Measures].[Internet Standard Product Cost] *.0001,
FORMAT_STRING = ‘Percent’
MEMBER [Measures].[Test]
As [Measures].[ActualCost]*100
MEMBER [Measures].[ActualCost]
As [Measures].[Internet Standard Product Cost]-[Measures].[specialDiscount]
MEMBER [Measures].[Label] AS [Organization].[Currency Code].CURRENTMEMBER.MEMBER_CAPTION
SELECT {[Measures].[Label],[Measures].[Internet Sales Amount],[Measures].[Internet Gross Profit],[Measures].[Internet Tax Amount]
,[Measures].[Internet Standard Product Cost],[Measures].[specialDiscount],[Measures].[ActualCost]
,[Measures].[Test]} ON 0,
{[Organization].[Currency Code].members} ON 1
–{([Product].[Category].[Category],[Product].[Class].members,[Date].[Calendar Year].members)} ON 1
FROM [Adventure Works]
Hi Chris,
I have a requirement to create a calculated member ‘Sales Invoice as % of Revenue’. I have a ‘document-types’ dimension for which the DocTypeIDs 16,19 and 20 indicate a ‘Sales Invoice’.
The catch is that the DimDocumentTypes may not have all of the DocTypeIDs always.
So in case , for eg, the FactSalesRevenue has a revenueAmount of 100 against DocTypeID 16 and 200 against DocTypeID 19 and in case the DimDocumentTypes does not have the member with DocTypeID 20, my calculated member ‘Sales Invoice as % of Revenue’ just shows 0 instead of 300/(total revenue amount.)
I have been playing around with EXISTING, IIF(VB.ISERROR ..etc
What would be the ideal MDX to create the calculated member ‘Sales Invoice as % of Revenue’ ?
Hi Chris,
Below is the MDX that I framed.
CREATE MEMBER CURRENTCUBE.[Measures].[Sales Invoice as % of Revenue]
AS IIF(VBA!ISERROR(DIVIDE(
(sum ( EXISTING {
([RSL – Sales Document Detail I].[Interpreted Document Type Client ID].&[23], IIF(VBA!ISERROR([Measures].[Sales Revenue]),0,[Measures].[Sales Revenue]))
,
([RSL – Sales Document Detail I].[Interpreted Document Type Client ID].&[5], IIF(VBA!ISERROR([Measures].[Sales Revenue]),0,[Measures].[Sales Revenue]))
}
)
)
,
(sum (IIF(VBA!ISERROR([Measures].[Sales Revenue]),0,[Measures].[Sales Revenue])))
)
)
,0,(DIVIDE(
(sum ( EXISTING {
([RSL – Sales Document Detail I].[Interpreted Document Type Client ID].&[23], IIF(VBA!ISERROR([Measures].[Sales Revenue]),0,[Measures].[Sales Revenue]))
,
([RSL – Sales Document Detail I].[Interpreted Document Type Client ID].&[5], IIF(VBA!ISERROR([Measures].[Sales Revenue]),0,[Measures].[Sales Revenue]))
}
)
)
,
(sum (IIF(VBA!ISERROR([Measures].[Sales Revenue]),0,[Measures].[Sales Revenue])))
)
)
)
,
FORMAT_STRING = “Percent”,
NON_EMPTY_BEHAVIOR = { [Sales Revenue] },
VISIBLE = 1 , DISPLAY_FOLDER = ‘Revenue Analysis’ , ASSOCIATED_MEASURE_GROUP = ‘Revenue SL Tree’ ;
Hi Rosemary, using VBA!ISERROR in this way will be very bad for query performance. In fact, this is a problem that should not be solved in MDX – your DimDocumentTypes dimension should always have the required values in it, regardless of whether they are present in the fact table. If they don’t then you need to fix the data.
Hi Chris,
I want to execute a list of MDX queries in loop by connecting to Microsoft analysis server remotely through Powershell. The MDX queries are got from a database table and stored in a dataset. Following is the powershell script I have come up with but the MDX queries do not execute and throws the following error– Description=”Invalid query syntax. A valid MDX or DAX query was expected.” Source=”Microsoft SQL Server 2016 Analysis Services”
Script:
$SqlConnection.ConnectionString = “Server=$servername;Database=$Dbname;Integrated Security=True”
$SqlCmd = New-Object System.Data.SqlClient.SqlCommand
$SqlCmd.CommandText = (“SELECT MDXQuery FROM [Tablename] where dbTemplateName = ‘_SSAS_G0001.abf’ and MDXQuery ‘REFRESH CUBE [GLACubeAVw]’ “)
$SqlCmd.Connection = $SqlConnection
$SqlConnection = New-Object System.Data.SqlClient.SqlConnection
$SqlAdapter = New-Object System.Data.SqlClient.SqlDataAdapter
$SqlAdapter.SelectCommand = $SqlCmd
$DataSet = New-Object System.Data.DataSet
$SqlAdapter.Fill($DataSet)
$SqlConnection.Close()
#$query = $DataSet.Tables[0]
[System.Reflection.Assembly]::LoadWithPartialName(“Microsoft.AnalysisServices”)
$server = New-Object Microsoft.AnalysisServices.Server
$server.connect(“$serverName”)
$DBID = “$Dbname”
foreach($mdxquery in $DataSet.Tables[0])
{
try
{
Invoke-ASCmd -Server $serverNmae -Database “$dbname” -Query $mdxquery -Verbose
}
catch
{
write-host $Error
}
}
Hope you can help
Are you sure there isn’t a syntax error in one of your MDX queries? That’s what the error message suggests.
Hello Chris, Thanks for your knowledge. I am trying to write MDX query where situation is similar to your query error type 4 where one item is not present in the table.
Instead of correcting it with the one present in the table , is there any way where we can provide the check or something so that it displays report with remaining values and run the MDX query successfully without throwing an exception
Do you need to set the MDXMissingMemberMode connection string property? http://dugaputiashok.blogspot.com/2009/08/i-just-wanted-to-write-about-important.html