The primary factor to this approach was performance in loading data. We did have fk's on everything since my background and schooling was oltp. Loading a fact table with 25...

we use standard edition so partioning is not possible. And yes maint is easier. also we use schemas to separate all the dim data for security purposes and organization.

Yes separate databases. All the fact data lives in separate databases as well. All these fact DBs share the common dimension db. I don't worry about joins since join speed...

stevefromoz

that appears to do the trick.

can I ask how you got the minute value for 1900-01-01?

We've completely separated the dimension tables from the fact tables. They could be on separate servers if I wanted.

This was a bit nerve racking at first, I will admit. Adding...

Got it !!!

OK, apparently running under the proxy does not matter. I just changed the agent account to run under my AD account and it worked. Apparently the profile that...

the ssis job is not running under the agent account. it is running under a proxy account based on a credential i created using my AD account. It's basically doing...

I've reviewed the blog post actually used that as a reference when setting this up.

My issue is that I can run this interactively in BIDS and it works fine....

thanks for the info, we'll give it a try.

thanks for the reply. With that, is there any way to prevent an advanced user from making a direct connection to the table? For example, through Excel?

Piotr that is a good suggestion I used the Exec instance guid since it was readily available. If I did generate my own batch keys I might consider an Identity...

I've learned quite a bit about indexing through all your posts but the point I was getting at originally is when using a parameter the query took over a minute...

When this table is loaded there are about 800,000 rows that all have the same batchExecID, the next run loads about the same but with a new Batch Guid. Normally...

Again sorry for the delay, was putting out fires yesterday...

Attached is the profiler output.

I included all batches this time the event is Showplan XML Statistics Profile, but looks the same...

yes that's correct there is no primary key. The main purpose of this table is too quickly load data from the source system. Aside from the defaults there are no...