The best way to implement such schema in SSAS

Sergey 21 Reputation points
2020-10-06T13:44:06.857+00:00

Hi All,
I would be grateful for your advice about the best way to implement in multidimensional cube the following situation:

I have got a standard star-schema (please see attached picture)
Fact_Actions - fact table
Dim_Agreements, Dim_Users and so on are dimensions
The problem is: dimensions DIM_Answers and DIM_Questions are linked to fact-table 16 times and in different reports only several combinations of Question_ID and Answer_ID are used, e.g.
SUM
(
CASE
WHEN
(FWORKS.ProjectQuestion_ID_1=7 AND FWORKS.ProjectAnswer_ID_1=21) OR
(FWORKS.ProjectQuestion_ID_2=7 AND FWORKS.ProjectAnswer_ID_2=21) OR
(FWORKS.ProjectQuestion_ID_3=7 AND FWORKS.ProjectAnswer_ID_3=21) OR
(FWORKS.ProjectQuestion_ID_4=7 AND FWORKS.ProjectAnswer_ID_4=21) OR
(FWORKS.ProjectQuestion_ID_5=7 AND FWORKS.ProjectAnswer_ID_5=21) OR
(FWORKS.ProjectQuestion_ID_6=7 AND FWORKS.ProjectAnswer_ID_6=21) OR
(FWORKS.ProjectQuestion_ID_7=7 AND FWORKS.ProjectAnswer_ID_7=21) OR
(FWORKS.ProjectQuestion_ID_8=7 AND FWORKS.ProjectAnswer_ID_8=21) OR
(FWORKS.ProjectQuestion_ID_9=7 AND FWORKS.ProjectAnswer_ID_9=21) OR
(FWORKS.ProjectQuestion_ID_10=7 AND FWORKS.ProjectAnswer_ID_10=21) OR
(FWORKS.ProjectQuestion_ID_11=7 AND FWORKS.ProjectAnswer_ID_11=21) OR
(FWORKS.ProjectQuestion_ID_12=7 AND FWORKS.ProjectAnswer_ID_12=21) OR
(FWORKS.ProjectQuestion_ID_13=7 AND FWORKS.ProjectAnswer_ID_13=21) OR
(FWORKS.ProjectQuestion_ID_14=7 AND FWORKS.ProjectAnswer_ID_14=21)OR
(FWORKS.ProjectQuestion_ID_15=7 AND FWORKS.ProjectAnswer_ID_15=21)OR
(FWORKS.ProjectQuestion_ID_16=7 AND FWORKS.ProjectAnswer_ID_16=21)
THEN 1
ELSE 0
END
)AS CountContacts

or

SUM
(
--PROJECTANSWER_ID=28
CASE
WHEN FWORKS.ProjectAnswer_ID_1 IN(28) OR FWORKS.ProjectAnswer_ID_2 IN (28) OR FWORKS.ProjectAnswer_ID_3 IN (28) OR FWORKS.ProjectAnswer_ID_4 IN (28) OR
FWORKS.ProjectAnswer_ID_5 IN(28) OR FWORKS.ProjectAnswer_ID_6 IN (28) OR FWORKS.ProjectAnswer_ID_7 IN (28) OR FWORKS.ProjectAnswer_ID_8 IN (28) OR
FWORKS.ProjectAnswer_ID_9 IN (28) OR FWORKS.ProjectAnswer_ID_10 IN (28) OR FWORKS.ProjectAnswer_ID_11 IN (28) OR FWORKS.ProjectAnswer_ID_12 IN(28) OR
FWORKS.ProjectAnswer_ID_13 IN (28) OR FWORKS.ProjectAnswer_ID_14 IN (28) OR FWORKS.ProjectAnswer_ID_15 IN (28) OR FWORKS.ProjectAnswer_ID_16 IN (28)
THEN 1
ELSE 0
END
)AS CountTaken

My question is what is the best way to handle this situation:

  1. create view like

SELECT F.AGREEMENT_ID,F.DATE_ID,F.USER_ID,
SUM
(
--PROJECTANSWER_ID=28
CASE
WHEN FWORKS.ProjectAnswer_ID_1 IN(28) OR FWORKS.ProjectAnswer_ID_2 IN (28) OR FWORKS.ProjectAnswer_ID_3 IN (28) OR FWORKS.ProjectAnswer_ID_4 IN (28) OR
FWORKS.ProjectAnswer_ID_5 IN(28) OR FWORKS.ProjectAnswer_ID_6 IN (28) OR FWORKS.ProjectAnswer_ID_7 IN (28) OR FWORKS.ProjectAnswer_ID_8 IN (28) OR
FWORKS.ProjectAnswer_ID_9 IN (28) OR FWORKS.ProjectAnswer_ID_10 IN (28) OR FWORKS.ProjectAnswer_ID_11 IN (28) OR FWORKS.ProjectAnswer_ID_12 IN(28) OR
FWORKS.ProjectAnswer_ID_13 IN (28) OR FWORKS.ProjectAnswer_ID_14 IN (28) OR FWORKS.ProjectAnswer_ID_15 IN (28) OR FWORKS.ProjectAnswer_ID_16 IN (28)
THEN 1
ELSE 0
END
)AS CountTaken
FROM FACT_TABLE AS F
GROUP BY F.AGREEMENT_ID,F.DATE_ID,F.USER_ID

and use this view in SSAS

  1. Create named calculations (I am not sure if it is possible)
  2. Do something else

Thank youforthe answers

SQL Server Analysis Services
SQL Server Analysis Services
A Microsoft online analytical data engine used in decision support and business analytics, providing the analytical data for business reports and client applications such as Power BI, Excel, Reporting Services reports, and other data visualization tools.
1,282 questions
0 comments
Accepted answer
  1. Lukas Yu -MSFT 5,821 Reputation points
    2020-10-07T01:48:37.88+00:00

    Hi,

    You could use directly the view in SQL Server as the data source.
    Calculating columns/named calculations will take a little extra time and resources during cube processing.

    There are already some discussion over this issue, see this thread : SSAS - should I use views or underlying tables?

    If the answer is helpful, please click "Accept Answer" and upvote it.

    Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.

    0 comments

1 additional answer

Sort by: Most helpful
Most helpful Newest Oldest
  1. David Browne - msft 3,846 Reputation points
    2020-10-06T13:59:18.187+00:00

    I have got a standard star-schema

    No you don't. Not for the purposes of answering the kind of questions you appear to be interested in, at least. It appears you want a fact table with one row per question.

    And why MultiDimensional instead of Tabular, which is recommended for most new work?

Your answer

Answers can be marked as Accepted Answers by the question author, which helps users to know the answer solved the author's problem.