CODEWITHSUNDEEP
sql-serverssasdimensions
user3735855
user3735855

Reputation: 144

SSAS Using DimDate best practise

using VS2015 on SQL Server 2016 Standard edition.

We are building a DataWarehouse for the whole business, so there will be a number of different areas of the business each querying the cube, so also we will have many (10 or so) fact tables in there. Each of the fact tables will use DimDate (obviously) and DimTime.

Each of the Fact tables have many references, createdate, modifieddate, enddate, eventdate etc etc. I have created the relationships on the DSV, but on the DimensionUsage tab under Dimensions there are now 60 or so dimensions, each one for a different "XXX..dimdate".

 Dimension:           Measure Group:
                     FactSR          FactCommunication    FactSRAction
SRCreateDate         SurrogateKey
SRModifiedDate       SurrogateKey
CallDate                               SurrogateKey
CallEndDate                            SurrogateKey
ActionDate                                                 SurrogateKey
ActionFollowupDate                                         SurrogateKey

Above is a simplified version of my dimension usage tab, does this look correct to you? As there is no overlap between any of the dimdates, even though on 17/1/2017 (for example) there will be many SR's raised, calls coming in, and SRActions taking place, but my cube only shows each of the events in their own stream, and no crossover between Dims.

Any suggestions please? Or is this how it should look. Thanks.

Upvotes: 0

Views: 90

Answers (1)

Alexey Varentsov
Alexey Varentsov

Reputation: 386

You can create one Date dimension and connect it to measures many times with different names. Please view example project.

SSAS example

AW Multidimensional Models SQL Server 2014.zip https://msftdbprodsamples.codeplex.com/releases/view/125550

Upvotes: 1

Related Questions