SSAS Cube business inscrease usablitiy ideas

Question

SSAS Cube business inscrease usablitiy ideas

rinksrinks

SSC Enthusiast

Points: 133
More actions
January 3, 2017 at 9:07 am

#147829

we developed a cube for financial department based on their needs and interest. However post deliverable we aren't seeing the usage as expressed initially. The feedback we are getting is, its confusing (ex: when dragging non related facts and dim, the experience is different because data repeats or blanks), too many attributes, etc. I need to come up with ideas/thoughts on how to make the user experience better, increase usability. We have spent good amount of time/money to deliver this and just throwing away something doesn't make business sense. Any thoughts?!

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

Chris Harshman SSC-Forever Points: 42192 More actions · Answer 1

Do you have people in different business job functions accessing the cube? Sometimes if the data warehouse is designed to cover everyone's needs it can seem overwhelming to users. One possibility that could help simplify it for different people is perspectives. I've used it before to present different subsets of the whole for different purposes. Here's a couple of good articles on them:

http://blog.sqlauthority.com/2016/02/25/sql-server-boosting-user-experience-perspectives-analysis-services-notes-field-116/

https://www.mssqltips.com/sqlservertip/3213/sql-server-analysis-services-perspectives/

Perspectives can be referenced in a report or pivot table just as the cube can be directly.

rinksrinks SSC Enthusiast Points: 133 More actions · Answer 2

thanks for the reply. Yes I have looked into perspective. I wanted to get your thoughts on what kind of documentation, if any, did you provide to your power users? One issue users are having is understanding the related vs unrelated dim and fact relationship. not sure how to explain or document this? Apart from this training is another item which is vv imp as well. Any other thoughts?

Chris Harshman SSC-Forever Points: 42192 More actions · Answer 3

I've always followed the Kimball group methodology so my answer will be biased on their suggestions. One thing they talk about for documentation is what's called the Bus Matrix:

http://www.kimballgroup.com/2005/12/the-matrix-revisited/

Another consideration is obviously attribute naming, and the need for business people to understand the names:

http://www.kimballgroup.com/2014/07/design-tip-168-whats-name/

You could also write up some sort of data dictionary that puts in business terms what all the measures and attributes mean.

rinksrinks SSC Enthusiast Points: 133 More actions · Answer 4

Chris, appreciate the response. Great shares. Since we have gotten to talking let me run another question by ya for your thoughts. The biggest challenge we are having with cube is user experience with related and unrelated attribute. Let me explain.

Consider the attached image with a simple dim model:

note: The FactSalesBudget has an attribute (along with others) "BudgetDate" which isn't linked to DimDate.

When user tries to create a simple report

Filter->DimRegion

DimDate , Sales Budget, Sales Actual.

The report doesn't shows anything. However when "Sales Budget" is removed, its shows data. The reason being before DimDate isn't related to FactSaleBudget. This is what I mean user experience problem. You would probably recommend why not link the FactSalesBudget.BudgetDate to DimDate, well i could but am not sure if its gonna make sense. Because the budget date are a) monthly b) Its for usually beginning of the year ex: 1/1/2017. Plus adding this date relation add another Date Time to the already large collection of dimensions. However the FactSalesActual.DimDate is spread across multiple dates, months, year. Basically the user would like to have a nice report showing the dates with the actuals vs budget amounts. But the fact the report blanks out when Fact Sales budget is brought over, makes the user confused. And in a way I agree too. I am sure there is a way to make this done right and I might not know how. Any thoughts?

Chris Harshman SSC-Forever Points: 42192 More actions · Answer 5

rinksrinks (1/5/2017)
...note: The FactSalesBudget has an attribute (along with others) "BudgetDate" which isn't linked to DimDate.
When user tries to create a simple report
Filter->DimRegion
DimDate , Sales Budget, Sales Actual.
The report doesn't shows anything. However when "Sales Budget" is removed, its shows data. The reason being before DimDate isn't related to FactSaleBudget... Because the budget date are a) monthly b) Its for usually beginning of the year ex: 1/1/2017. Plus adding this date relation add another Date Time to the already large collection of dimensions. However the FactSalesActual.DimDate is spread across multiple dates, months, year. Basically the user would like to have a nice report showing the dates with the actuals vs budget amounts...

OK, it makes perfect sense that FactSalesActuals (1 row per sale) has a different grain than FactSalesBudget (1 row per month per region). You'd still probably want to find some way to relate the FactSalesBudget to DimDate. What attributes are in DimDate? A common thing to do is have attributes in DimDate such as YearNumber, QuarterNumber, MonthNumber, IsLastDayOfYear, IsLastDayOfQuarter, IsLastDayOfMonth, etc. Then you could always associate the FactSalesBudget record to that last day of month, and summarize the FactSalesActual data to the YearNumber,MonthNumber level so they match.

http://www.kimballgroup.com/2004/02/design-tip-51-latest-thinking-on-time-dimension-tables/