Developing a SSRS report using a SSAS Data Source. Problem After designing several SSRS reports based on regular relational databases, your boss would now like several new reports to be designed and rolled out to production based on your organization's SSAS OLAP cube.
How do you get started with designing a report based on a cube? Solution. Excel – Using a Custom MDX Query in Excel Table. I had a requirement where I wanted to use my own Custom MDX Query within an Excel Table.
And from the output of the table I then wanted to Pivot this data. I do know that you can do this in Power Pivot, but the requirement was to not use Power Pivot. Below are the steps and it is a little bit of manipulation to get it working, but it works well in the end. I did not want to use any VBA or custom code to get this working. Only the standard tools and interface with Excel. T-SQL OR Condition in MDX. While attempting to answer a MDX question on the Experts Exchange technology forum, I ran into an interesting scenario that is very easy to resolve in T-SQL, but can be quite a challenge in MDX.
I will rewrite the question using the Adventure Works Database and Analysis Services Cube so you can follow along to gain a better appreciation of the subtle differences between T-SQL and MDX languages. When I execute the following MDX query it is throwing this error. Calculated Member to get distinct count on a condition in SSAS 2008 - SQL Server Q&A from the SQL Server Central community. Hi, the solution is quite easy.
Let Assume, you have a cube with mentioned fact table and Measure Group based on that fact table, containing measure [claim_paid] and also OperationCode Dimension connected to that fact table. The Dimension has an [operation_code] attribute hierarchy. In that situation you can write a simple MDX query to get distinct found of [operation_code] as below: MDX and icCube documentation. Calculated members allow you to define an MDX expression/formula that is attached to a new dimension's member.
Note that a calculated member within this document is referring either to a calculated measures (i.e. a new [Measures] being calculated) or a calculated member (i.e. a new dimension member being calculated). Calculated members are never aggregated and instead are always evaluated according to the current context. Calculated members may be defined at cube level (they are then available as regular members for every SELECT query) but also within the scope of a given SELECT expression only as follows: Joining the results of two MDX queries together. One question I get asked occasionally is whether it’s possible to join the results of two MDX queries together.
Although I seem to remember this kind of functionality is mentioned in the OLEDB for OLAP spec it certainly isn’t supported in Analysis Services MDX and I don’t expect it ever will be; therefore, as all good consultants know, when you’re faced with a request for functionality that doesn’t exist what you have to do is look closely at the requirement to see if there’s a different way of solving the problem to get the result the customer wants… What people usually want to do when they think about joining MDX queries is this: they want to create a query that shows members from two different hierarchies side-by-side on the same axis. For example, in Adventure Works you might want to see a query with Calendar Years on Rows and Countries followed by Product Categories on Columns, something like this: Having clause in MDX. Comparison of Queries Written in T-SQL and SQL Server MDX. Problem Beginning to learn and comprehend SQL Server Analysis Services (SSAS) MDX queries can be difficult after one has spent years writing queries in T-SQL.
When trying to write SQL Server MDX queries, oftentimes I would think to myself, "How would I write this query in T-SQL? " Faisal's Technical Blog: SSAS - Duplicate attribute key found when processing. I have been seeing this error time and time again when processing cubes in Analysis Services and thought it would be a good idea to blog about it.
When processing Analysis Services cubes, you might see the following error: Errors in the OLAP storage engine: A duplicate attribute key has been found when processing: Table: 'tblCustomers', Column: ‘Customer_Name’, Value: ''. The attribute is ‘Customer Name'. As you can see, Analysis Services is complaining that there is a duplicate “blank” value in the column Customer_Name. How can there be a duplicate blank value as Analysis Services runs a SELECT DISTINCT query on the relational table to retrieve column values when processing?