How to properly create a denormalized reporting table?

Question

So we have reports which run, that our client runs multiple times daily and some of these reports hit very important tables which I'm not a fan of.

We currently have a small reporting table which is populated when a new user signs up. The user signs up and the aspnet_membership table is populated, then another sproc is run to populate the reporting table.

I've noticed we get some deadlocks during the registration process and I'm wondering if this is the cause, though I'm not sure since it's just doing Inserts which I don't think would cause deadlocks.

Anyway, as far as creating a reporting table, would it be better to just add a trigger onto the aspnet_membership table and when a new record is inserted we insert into our reporting table, or is having two sprocs run upon initial registration fine too?

I thought about an overnight process but the data needs to be real time.

Thanks in advance!

Answer 1

Could the reports at least be near real time? Could you have a SQL job that runs every 5 minutes perhaps that populates the reporting table?