Design Question loading fact table

Question

I am trying to build a dimensional model based on two table which is used as a Key value. As a charm for programming developers nightmare for BI and reporting structure.

My Primary Table looks like

UniqueId , SessionId, Event, Application, start_date , end_date

1             Guid1             Search    SearchEngine , 6/18/2012 , 6/18/2012

2           Guid1( Same as above) Documentopened,  SearchResult , 6/18/12, 6/18/2012

3           Guid1( Same as above) Documentopened,  SearchResult , 6/18/12, 6/18/2012

KeyValueTable

Id, Name , Value

1   Search Word      Test1

1  Searchorder     Date

2   Searchword    Test1

2  Searchorder    Date

2 DocumenID     1234

3 Searchword   Test1

3 Search oRder  Date

3 DocumentID  4567

             

1. I have two approach in mind  One is create one big fact table and pivot the data when loading the  data in Dimensional Table. Which is getting nightmare because  I have multiple rows of same term and I am not sure if that is the correct version. And the first record is always with search Term with Documentid
2. The other approach is to break down in multiple fact table. Where I create fact table with
   1. Search ( Which Keeps the Searchword and related metrics( For Example I can search Multiple Terms with in one session ) and create count of fact as semiadditive measure.
   2. The Other fact table would be Documents referencing DocumentID. Not all search Term will have  search Term and inorder for me tie DocumentID to searchTerm , which in a way is no different then the previous approach mentioned.

Can some one help how to deal with issue loading the fact table.

Although , I do not have any lineages I am really relying on keywords to load the data.

Thoughts would be appreciated.

Answer 1

I explicity wanted to specify that since the Main table has multiple rows for the same session key, I do tend have to repeating Name value pair multipel time with different unique Id's for same session.

One other issue i need to deal is that the Name value can be part of main table rows with 0 to more documentid's and o or more search terms in the same session, which makes it little complex then the usual Name value pair parameter table where you have one value repeating for one main row in table.

Answer 2

The second TABLE is an EAV (Entity-Attribute-Value), a common solution for scenarios with varying parameters.

Is there a specific issue you'd like to address?

Answer 3

I am looking for some ideas to solve the issue. So that my Documentid's and search Terms gets lined up properly. Also how should be Dimensional model for this should like and how should they represent in the reporting structures

Answer 4

I apologize, i do not understand what the issue is. Would you mind explaining that part again?