Hi and thanks for looking.

I have a DB that holds transactions, This is a transaction For example
:

First name = guy
Seconed name = bbb
phoneNumber = 5556668889999444
Email=guy (AT) hotmail (DOT) com
Address=21 jump street
Ip=192.168.0.12

I need to create a system the will give me online information on the
details of the current transaction.
E.g. When I receive a transaction I want to know how many times in the
past XX (hours/days/weeks) this email used a different phone number , a
different ip and all the other parameters (kind of distinct count).
In the same way I want to know how many times this phone number used
different ip , different email and all the other information.
Actually I want to create a matrix with all the parameters of this
transaction.

Something like this:


phoneNumber Email ip address
phoneNumber 1 2 3 1
Email 3 1 15 2
Ip 2 2 1 1
Address 1 5 13 1



I can do it without using OLAP on a small scale DB, but when it gets
big, it takes to much time to get an answer.
Is there a way to do this with OLAP? Is it a good way?
Can someone please give me idea how to do so?

I tried allot of ways to do it, but without success,
I appreciate your help and I am sorry for my lame English.

sorry, when i previewed the message it was fine :



--------------------phoneNumber---Email---ip-------address
phoneNumber-------------1-----------2-----3 ----------1
Email-------------------3-----------1-----15----------2
Ip----------------------2-----------2-----1-----------1
Address-----------------1-----------5-----13----------1

What is slow in your OLAP model, the query or the process? Or both?
The Distinct Count is a heavy operation.
You can partially solve this problem (with better use of cache and
lower processing time) using the many-to-many modelling solution I
described in my paper (see Distinct Count section):
http://www.sqlbi.eu/manytomany.aspx

However, this may be a partial solutions, because the distinct count
problem is a very difficult one when you have big fact tables (more
than 100 millions of rows in fact table).
What size do you have (in terms of rows and different
IP/Mail/Addresses) in your database?

Marco Russo
http://www.sqlbi.eu
http://www.sqljunkies.com/weblog/sqlbi

shlomis (AT) gmail (DOT) com wrote:

Thanks Marco for your answer .

time of process is fine and acceptable, the time of the query is too
long.
my fact table is about 10 million rows.
different parameters can be anything between 1 and 50.
i still cant figure out how should i design the solution .

i am going to read the articles right now .
Thanks allot.





Marco Russo wrote:

If you have 10 million rows in the fact table, the many-to-many design
could be an effective solution.
You could observe:
- faster processing time
- slower response time for the first query
- very faster response time for the second and following queries

I'm very interested to know the results of your tests.
Please write me too at marco.russo[at]sqlbi[dot]eu

Marco Russo
http://www.sqlbi.eu
http://www.sqljunkies.com/weblog/sqlbii

shlomis (AT) gmail (DOT) com wrote:

I made some test.
I think that you can create a single dimension like this:

SELECT
b.BaseKey,
b.BaseAttribute,
COALESCE( e.ExtendedAttribute, '<unknown>' ) AS ExtendedAttribute
FROM DimBase b
LEFT JOIN DimExtended e
ON e.BaseKey = b.BaseExtended

I will call the dimension DimComplete (because it has all the dimension
attributes, base and extended).
I define two measure groups, one for each fact table (FactBase and
FactExtended).

The resulting UDM has a dimension usage grid that use a regular
relationship between DimComplete and these 2 measure groups.
This model shouldn't have particular problems when the number of
records will become larger.

Let me know if it works as you expected.

Marco Russo
http://www.sqlbi.eu
http://www.sqljunkies.com/weblog/sqlbi


shlomis (AT) gmail (DOT) com wrote: