background preloader


Facebook Twitter

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.

Developing a SSRS report using a SSAS Data Source

How do you get started with designing a report based on a cube? Solution The creation of an OLAP SSAS report is similar to other SSRS report development once you get to the actual report layout. However, designing your data sources and datasets varies quite a bit from normal relational database data sources and datasets. The first step in creating a new OLAP based SSRS report is to create a New Project as noted in the below figure. First you will want to create a new Report. The New Report Wizard opens and the first step in the wizard allows for the creation of a data source.

Depending on the roles created for SSAS Cube you are connecting to, you may have to change the access details noted in the Credentials window. Acts as a method to filter the dataset at the query level. 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.

Excel – Using a Custom MDX Query in 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.

I did get the idea from Chris Webb’s Blog as a reference point: NOTE: THIs has been built using Excel 2013 In our example below we want to extract from the AdventureWorksDW2012 Analysis Services Database and select the following: · Date · Product Category · Sales Territory Country You can get a copy of this here: · I used the AdventureWorksDW2012 Data File and AdventureWorks Multidimensional Models SQL Server 2012 Creating the connection.

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.

T-SQL OR Condition 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.

Calculated Member to get distinct count on a condition in SSAS 2008 - SQL Server Q&A from the SQL Server Central community

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: WITH MEMBER [Measures]. The magic is in filtering the dimension members based on the measure value. MDX and icCube documentation. Calculated members allow you to define an MDX expression/formula that is attached to a new dimension's member.

MDX and icCube documentation

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: WITH MEMBER [Measures]. [Market Share] AS ([Measures]. 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.

Joining 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: SELECT {[Customer].

[Country]. SELECT {[Product]. 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.

Comparison of Queries Written in T-SQL and SQL Server MDX

When trying to write SQL Server MDX queries, oftentimes I would think to myself, "How would I write this query in T-SQL? " Solution The solution presented in this tip will demonstrate a T-SQL Query followed by an SQL Server MDX query that will return the same results. Seven examples will be presented in order of increasing complexity. Example 1 In T-SQL Query 1, we select the count of records in the table FactInternetSales.

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.

Faisal's Technical Blog: SSAS - Duplicate attribute key found when processing

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.