BusinessObjects Board

A few million rows in the security table...-:)

building reports webintelligence
#1

Hello All,

I have used the @Variable(‘BOUSER’) / security table and applied security on universes in many implementations.

Never though I had to worry about performance due to the size of the security table…

I am now (soon) going to test out the performance with a security table which has is going to have a few (just 10 to 15-;)) millon rows and say 50,000 rows at the max for a single user, and on an average say 10 to 20,000 rows per user at the most…

The fact table in question is not going to have more than 500k rows…
I donot expect fetching any huge number of rows into the reports at any point of time…(as it is supposed to be a report and not a data dump -:wink:

Out of curiosity, I wanted to check with our friends here on what their experience has been with using security tables that might have say 10 million rows… if you have not gone up to such a big security table…then what is the maximum you have used so far.

Summary
Fact table - 500,000 rows at the most.
Security table - 10 to 15 millions rows has 2 or 3 columns and to be used along with the fact table using @Variable(‘BOUSER’)

Any report at time would not have any abnormal number of rows.

Wondering if anyone has faced a similar situation and has anything to share…

Anyone ?

Thanks in Advance !

Best Regards,

Naresh Ganatra :us: (BOB member since 2004-04-09)

#2

I’m going to go out on a limb here and say you’re doing something wrong :o or at least not in the most efficient manner. It sounds like you’re pre-aggregating it with your data perhaps?

Typically a security table is a table which houses users and thier credentials, or security rights.

I can’t imagine a table that has 10-15million rows of users unless you’re doing something for say a company the size of Amazon.com.

Can you provide a bit more info as to how this security table is layed out?

Here’s my scenario and use of a BOUser() style security implementation…
Our userbase is extremely small at the moment (Still in Pilot phase)

We have 1 row per user, per client ID. If a user is setup to access more than one Client… they have more than one row… we have only about 2000 client IDs… and no more than about 200 Users planned at the moment… Even if all Users had access to all clients, that only 400k users.
Instead of giving people who have access to ‘everything’ a specific row in the main table… I created a 2nd table which contains just a list of user names… no client ID, signifying that they have access to all clients. (This is actually done this way to mirror the system which is supplying us with the security information, and not necessarily for performance)

So my ClientSecurity Table has these columns.
UserID, ClientID, DatabaseID (Ignore this one for now)
My ClientSecurityALL Table has
UserID, DatabaseID

In our case we have users who fall into 3 categories:
Standard User - Access to 1 client ID only - Listed in ClientSecurity Table
Advanced Users - Access to multiple Client IDs - Listed in ClientSecurity Table.
SuperUsers - Access to ALL clients - Listed in ClientSecurityALL table

Then I created a view which joins which matches up the ClientSecurityTable, and ClientSecurityALL with my “Client” table to produce a resultset that effectively could have all 400k rows, but the tables themselves do not. The Tables are indexed on both UserID and ClientID.

(Note: that databaseID field actually lets me filter via a smaller sub-set of group based upon our two main data feeds, and could be expanded for items such as Region, Departments or any other sub-set if it were large enough to warrant it).

As to performance… thanks to indexes our performance degradation is almost nil, prior to using the security model (which did not filter) vs after showed no decrease in running speed, and in fact increased it since the security is then applied to each fact table via a filter on ClientKey in the form of a where statement in a derived table in Universe. (Where statement based off the ClientIDs they have access to using BOUser).

I honestly can’t think of a reason why your security table would ever be larger than your fact table unless you are simply pre-joining them into one big table for some reason (which is probably not the most efficient way).

I’m also assuming here that you have some input into the DB design schema and not having to use someone elses pre-designed ideas.

If you can provide a bit more information as to the layout and why it’s so large I’m sure a few of us could suggest some alternatives.

JPetlev (BOB member since 2006-11-01)

#3

You may wish to look at this, with regards to performance issues…

https://bobj-board.org/t/147386[/quote]

Mak 1 :uk: (BOB member since 2005-01-06)

#4

Thanks for the reply, nothing is being done wrong -:wink:

It is now on and rolling -:slight_smile: (in production) got 3+ million rows in the security table…and growing… the fact table though has only very few rows…500k or so at the most…and only 50,000 rows will be selected at the most for any given user from the secuity table.

With the right indexes we do not see any issues and do not anticipate any !!

Regards,

Naresh Ganatra :us: (BOB member since 2004-04-09)