Tuesday, June 19, 2012 4:04 AM
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
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
- 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
- The other approach is to break down in multiple fact table. Where I create fact table with
- 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.
- 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.
Tuesday, June 19, 2012 4:26 AM
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.
Tuesday, June 19, 2012 7:01 PMAnswerer
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?
Tuesday, June 19, 2012 7:07 PMI 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
- Edited by ns100 Tuesday, June 19, 2012 7:08 PM
Wednesday, June 20, 2012 3:16 PMAnswererI apologize, i do not understand what the issue is. Would you mind explaining that part again?