DSV Named Query vs SSIS Data Flow

Question

DSV Named Query vs SSIS Data Flow

Brandie Tarvin

SSC Guru

Points: 173108
More actions
December 10, 2010 at 8:57 am

#242387

I have a group of tables that need to form my Fact table for my cube. I can write up a Named Query for the DSV or I can create an SSIS package and have it send all the data to a brand new table.
Are there any opinions or hard-won knowledge bases that can help me figure out which would be better for my cube processing?
Brandie Tarvin, MCITP Database AdministratorLiveJournal Blog: http://brandietarvin.livejournal.com/[/url]On LinkedIn!, Google+, and Twitter.Freelance Writer: ShadowrunLatchkeys: Nevermore, Latchkeys: The Bootleg War, and Latchkeys: Roscoes in the Night are now available on Nook and Kindle.

Viewing 9 posts - 1 through 9 (of 9 total)

You must be logged in to reply to this topic. Login to reply

Raunak Jhawar SSCoach Points: 15701 More actions · Answer 1

My two cents,

SSAS approach will be elegant and less painful provided the staged data is ready for OLAP Analysis. Additional columns can be added using named calculations/named query as deemed necessary

Raunak J

Brandie Tarvin SSC Guru Points: 173108 More actions · Answer 2

So.... You're advocating that I use SSIS to create the table first?

Brandie Tarvin, MCITP Database AdministratorLiveJournal Blog: http://brandietarvin.livejournal.com/[/url]On LinkedIn!, Google+, and Twitter.Freelance Writer: ShadowrunLatchkeys: Nevermore, Latchkeys: The Bootleg War, and Latchkeys: Roscoes in the Night are now available on Nook and Kindle.

Raunak Jhawar SSCoach Points: 15701 More actions · Answer 3

Raunak Jhawar

SSCoach

Points: 15701

December 13, 2010 at 5:12 am

#1261625

Perhaps, yes...for ease of design

Raunak J

Koen Verbeeck SSC Guru Points: 259197 More actions · Answer 4

I would use the SSIS option, but that's a personal choice. I try to avoid many calculations/custom queries in the DSV. This because they seem to be 'hidden' in your cube, whereas an SSIS package is much more visible to other developers.

Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP

Daniel Bowlin SSC-Dedicated Points: 34566 More actions · Answer 5

I too have always favored staging the data into a new table.

Brandie Tarvin SSC Guru Points: 173108 More actions · Answer 6

So it's not a performance issue so much as it is a transparency issue?

Thanks for the advice, everyone. I appreciate it.

Brandie Tarvin, MCITP Database AdministratorLiveJournal Blog: http://brandietarvin.livejournal.com/[/url]On LinkedIn!, Google+, and Twitter.Freelance Writer: ShadowrunLatchkeys: Nevermore, Latchkeys: The Bootleg War, and Latchkeys: Roscoes in the Night are now available on Nook and Kindle.

Daniel Bowlin SSC-Dedicated Points: 34566 More actions · Answer 7

I think there is one thing, unmentioned, that we are all assuming. My comments at least were based on requirements that find a roughly 1 day delay between data updates acceptable. If your requirements are more real time, you may find that you need to use the source rather than staging tables.

Brandie Tarvin SSC Guru Points: 173108 More actions · Answer 8

Well, the source database is already a day behind production. So I need to find out how often the cube will be reprocessed. Or, more accurately, how often the reports based on this will be run.

Brandie Tarvin, MCITP Database AdministratorLiveJournal Blog: http://brandietarvin.livejournal.com/[/url]On LinkedIn!, Google+, and Twitter.Freelance Writer: ShadowrunLatchkeys: Nevermore, Latchkeys: The Bootleg War, and Latchkeys: Roscoes in the Night are now available on Nook and Kindle.