Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase ««12

2008r2 Cube not linking two views on key Expand / Collapse
Author
Message
Posted Tuesday, June 17, 2014 2:34 PM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Yesterday @ 11:28 AM
Points: 40, Visits: 121
Koen Verbeeck (6/17/2014)
How do you link Thomas from View 1 to the measures in View 2?
Is record key the same?



Yes sir, same key for Thomas in both views.

One thing to keep in mind though is that- view 2 will have multiple records with the same key. One record for each month.
Post #1582599
Posted Tuesday, June 17, 2014 3:02 PM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Sunday, July 20, 2014 11:20 PM
Points: 13,252, Visits: 10,133
The views being what they are, you can only create 1 dimension which holds all the attributes of view 1.
This is because your fact table (which is view 2) has only 1 surrogate key (the record key) and that key links to a single link in view 1.
It doesn't link for example to a gender, but instead to the unique combination of name, gender and automobile.

The dates dimension is created on top of view 2 (I'd rather create a dedicated time dimension, but anyway). The key is the date itself.

Now, in the data source view you need to link view 2 to view 1 with the record key.

The measure group is created on top of view 2 and selects only the measures.
This measure group is linked to the dimension of view 1 (in the dimension usage tab) using the record key.

If you want seperate dimensions, you'll need to create views on top of view 1 and assign surrogate keys. It's a bit of a mess this database unfortunately.




How to post forum questions.
Need an answer? No, you need a question.
What’s the deal with Excel & SSIS?

Member of LinkedIn. My blog at LessThanDot.

MCSA SQL Server 2012 - MCSE Business Intelligence
Post #1582608
Posted Wednesday, June 18, 2014 5:02 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Yesterday @ 11:28 AM
Points: 40, Visits: 121
Koen Verbeeck (6/17/2014)
The views being what they are, you can only create 1 dimension which holds all the attributes of view 1.



I figured as much, but how does one do this?


if it helps at all, I correctly built this cube in Analysis Services 2000 and converted them successfully, attached is what it looks like in the dimension usage tab - however I cannot recreate it. I can't seem to define a relationship between view 2 (measures) and view 1 (dimensions). I had no problem doing this in Analysis Services 2000 and it converted without creating extra views so there must be a way, however I cannot figure out how to do it manually. As I keep saying, thank you for your time.

Post #1582812
Posted Wednesday, June 18, 2014 5:26 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Sunday, July 20, 2014 11:20 PM
Points: 13,252, Visits: 10,133
Ryan1 (6/18/2014)
Koen Verbeeck (6/17/2014)
The views being what they are, you can only create 1 dimension which holds all the attributes of view 1.


I figured as much, but how does one do this?



As you would create any other dimension and measure group in SSAS.
Record key is the surrogate key for the dimension.

When you create the dimension, you simple select all fields from view 1, with record key being the key column.
When you create the measure group, you select all fields from view 2, except the date field.
In the dimension usage tab, you link the measure group to the dimension by using a regular relationship, where recordkey of view1 equals record key of view 2.




How to post forum questions.
Need an answer? No, you need a question.
What’s the deal with Excel & SSIS?

Member of LinkedIn. My blog at LessThanDot.

MCSA SQL Server 2012 - MCSE Business Intelligence
Post #1582827
Posted Wednesday, June 18, 2014 11:59 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Yesterday @ 11:28 AM
Points: 40, Visits: 121
This works you are the man! Thank you so much.


Any chance I can make this thing smaller?
Post #1583259
Posted Wednesday, June 18, 2014 1:32 PM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Sunday, July 20, 2014 11:20 PM
Points: 13,252, Visits: 10,133
Ryan1 (6/18/2014)
This works you are the man! Thank you so much.


Any chance I can make this thing smaller?


Glad you got it resolved.
What do you want to make smaller?




How to post forum questions.
Need an answer? No, you need a question.
What’s the deal with Excel & SSIS?

Member of LinkedIn. My blog at LessThanDot.

MCSA SQL Server 2012 - MCSE Business Intelligence
Post #1583365
Posted Thursday, June 19, 2014 7:06 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Yesterday @ 11:28 AM
Points: 40, Visits: 121
Koen Verbeeck (6/18/2014)
Ryan1 (6/18/2014)
This works you are the man! Thank you so much.


Any chance I can make this thing smaller?


Glad you got it resolved.
What do you want to make smaller?


The cube itself is massive. After completing your steps: it seems to increase by a few megabytes every time I hookup a new dimension. It looks like it's creating a new dimension for EVERY key + a new dimension for every dimension that should be hooked up for that key.
Post #1583789
Posted Tuesday, July 1, 2014 8:20 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Yesterday @ 11:28 AM
Points: 40, Visits: 121
Sorry to keep bringing this up guys but I've been banging this around for more than week now, took an online course, and have been working through microsofts tutorials and have found nothing. Can anyone point me in a direction of some readings that may suit the topic?

Please let me know if I should provide any more information, thank you and have a nice day.
Post #1588056
Posted Friday, July 4, 2014 12:24 PM
SSChasing Mays

SSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing Mays

Group: General Forum Members
Last Login: Today @ 6:52 AM
Points: 605, Visits: 2,108
So now the question is just size?

That is pretty tough to answer without getting into specifics (how big is too big? do you need all the data you are including? etc)

You could look at working in ROLAP mode instead of MOLAP, which would save space in the cube, but make things slower (and increase space usage someone in the dw). Other than that, your only method I know of is getting rid of things you don't need.


Post #1589439
Posted Wednesday, July 9, 2014 7:04 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Yesterday @ 11:28 AM
Points: 40, Visits: 121
Nevyn (7/4/2014)
So now the question is just size?

That is pretty tough to answer without getting into specifics (how big is too big? do you need all the data you are including? etc)

You could look at working in ROLAP mode instead of MOLAP, which would save space in the cube, but make things slower (and increase space usage someone in the dw). Other than that, your only method I know of is getting rid of things you don't need.




I will do my best to answer this fully:

The issue is size. The data is correct, and the measures can be reported along with the dimensions in the same cube.
When I convert the cubes over from Analysis Services 2000 they are but 2 or 3 megs a piece. Unfortunately, when I create the cubes from scratch using Koen Verbeeck method, stated previously in this thread, the cube can easily climb to over 80 mb while only hooking up a few dimensions and I need to hook up many more.

Also, ROLAP option is not my first choice because this scenario will not be fast enough.
Post #1590719
« Prev Topic | Next Topic »

Add to briefcase ««12

Permissions Expand / Collapse