#sql server analysis services

sql server integration services , sql server analysis services , ssis in sql server , sql ssrs , Online IT Solutions | Piyush Agrawal

SQL Server Analysis Services is a logical information motor utilized as a part of choice help and business investigation. It gives undertaking grade semantic information models for business reports and customer applications, for example, Power BI, Excel, Reporting Services reports, and other information representation instruments.

Question: 

"If I'm using SQL Server Analysis Services, can I URL filter my published report on Tableau Server using a dimension member? How about with a hierarchy?"

Answer:

Yes, you can! However, doing so is a bit more complex than a simple ?Region=East string.

Here’s a handy-dandy guide on how to approach this task. I’m using the latest version of Microsoft’s sample cube, “AdventureWorks” as my data source.

Filtering on an Dimension Attribute Member

Task: Filter on the Business Type attribute of the Resellers dimension. We want to see only those with a Business Type of Specialty Bike Shop

Approach: In your filter statement, refer to the attribute hierarchy on one side of the filter, and the specific attribute member on the other.

After you URLencode the & symbol above, your filter will look like this:

?[Reseller].[Business Type].[Business Type]=[Reseller].[Business Type].%26[Specialty Bike Shop]

..and the URL itself:

http://s/views/w/s?[Reseller].[Business Type].[Business Type]=[Reseller].[Business Type].%26[Specialty Bike Shop]

Filtering on a Hierarchy

Filtering via a hierarchy is much more complex. The trick is that you must refer not only the level of the hierarchy you are filtering on, but every level above it, as well.

Task:  Using the Promotions hierarchy in the Promotion dimension, select Discontinued Product from the Type level: 

Approach: In order to correctly specify "Discontinued Product", refer to both the Type and Category levels of the hierarchy in the filter you build: 

// URL encoding the & symbol to %26

[Promotion].[Promotions].[Category]=[Promotion].[Promotions].[Category].%26[Reseller]

[Promotion].[Promotions].[Type]=[Promotion].[Promotions].[Type].%26[Discontinued Product]

The complete filter looks like this:

http://t/views/w/s?[Promotion].[Promotions].[Category]=[Promotion].[Promotions].[Category].%26[Reseller]&[Promotion].[Promotions].[Type]=[Promotion].[Promotions].[Type].%26[Discontinued Product]

Task: Using the Calendar  hierarchy of the Date dimension, choose Q2, CY 2006:

Approach: Refer to the Calendar Year, Calendar Semester, and Calendar Quarter levels of the hierarchy as you write your filter:

[Date].[Calendar].[Calendar Year]=[Date].[Calendar].[Calendar Year].%26[2006] // CY 2006

[Date].[Calendar].[Calendar Semester]=[Date].[Calendar].[Calendar Semester].%26[2006]%26[1] // Semester 1 of 2006

[Date].[Calendar].[Calendar Quarter]=[Date].[Calendar].[Calendar Quarter].%26[2006]%26[2] // Q2 of 2006

Your filter looks like this:

File this one under "You may very well already know this, but I didn't."

Just posting in case I can save someone a few minutes. 

Anyway, I needed to create a sample which demonstrated using Tableau User Filters against a dimension attribute coming from an SSAS cube. The problem I faced was how to actually access the dimension attribute in question, since they don't automatically show up in the Calculated Field dialog's Field list. 

Short answer: Add the field to your Level of Detail:

Don't know why I didn't think of this earlier since the LOD shelf is our friend in terms of getting a field to show up elsewhere, but whatever. 

After adding the dimension to LOD, it'll appear in the Fields list. In the screencap below, I'm adding a condition filter (Filter | Condition Tab | By Formula)  to the User Name field on LOD so that we can only see rows where the user logged into Tableau Server matches the [User Name] in the cube's [User Names] dimension: