May 10, 2011 at 4:10 am
ok
after three projects in SSIS/AS/RS here are the things I wish I had known at the start. I'd welcome anyone else's learning curve comments, or even "here's how you solve that you dolt!"
1) despite it working and being quicker to develop... resist the urge to stick a load of sql in your SSIS package...always go for views & stored procedures. it's easier to send the DBAs some new sql for a view/sp than tinker with and redeploy the package
2) back stuff up 😀
3) config files...they are a bit fiddly. they wipe passwords, the formatting can go all over the shop. making them read only helps the wiping of passwords. you can always use package variables for stuff and assign them values in the set values of the package, negating the need for the config file
4) if you are running several long tasks in your process...make them seperate packages. it's easier to rerun them seperately once deployed
5) resist the urge to open a table and copy and paste loads (like over a 1000) records in from a spreadsheet. it's mind numbingly slow, and if there are errors, you may end up balancing a stapler on your return key for half an hour 😀
6) if you have a fact table with millions, buy a dimension of very few members (e.g. a deal table and a dimension that says "buy/sell") it will take ten minutes to process it if it runs from the fact table, but seconds if you add a table of buy/sell and join it
7) with a hierarchy...you may run the risk of not having data in there that can be normalised. e.g. the levels have more than one level above them. when this is the case, mess about with the attribute mapping to make the key field go to all fields instead of key field>level1>level2 do keyfield>level1 and keyfield>level2
8) sometimes, a strange error can be solved by closing and opening a package. no i don't get it either
9) localhost. if you use "localhost" as your server name, then any packages moved about on servers always work on that server. seems ridiculous, but it took me a while
10) when going through the rigmarole of getting an Oracle connection to work, tnsping is your friend, it tells you what version of Oracle it is using, whether it is 32/64 bit, where the directory is and whether your tnsnames.ora name works
May 11, 2011 at 2:04 am
Thanks for sharing!
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP
May 12, 2011 at 2:39 am
Almost all your comments seem geared to SSIS. Here are some from SSAS that I wish I'd known at the start.
1. Beginning in SSAS 2005, the same dimension can be reused with a different name. This is most useful for the date dimension. The same dimension called "Date" can be added repeatedly to the same cube with a different name. This makes any future changes to the dimension much easier because they only have to be made once.
2. The use of views to build dimensions on. For example, a list of technicians, some of whom are inspectors, can be solved by making a view for the inspectors, rather than have a seperate table where some of the data is repeated. This is also very useful for dates, as you can build a view for Months off a table that is for days. I'm not a big fan of views in an OLTP environment except in limited circumstances, but I find them very useful in an OLAP environment.
THere are more, and I'll edit this as they come to mind.
By the way, I don't understand your #6. Would you please clarify what you mean by it?
May 12, 2011 at 3:40 am
my point 6...
i have a fact table with ~7 million rows in it...and a field of [BuySell] that only ever has "B" or "S"
the field isn't indexed
if you create the BuySell dimension frmo the fact table...it thinks a bit as it has to do a distinct check down the rows. it's not too onerous, ten minutes max
if however you create a table with "B" and "S" in it, or go nuts and do
B | Buy
S | Sell
and create your dimension from that table...it will be a few seconds for the dimension creation
it's not a massive thing, just something i should have twigged on early doors
May 12, 2011 at 11:10 am
Here's a link to a post I made about how to fix an early problem where users were unable to access any reports in RS, despite permissions tweaks, AD group settings, and a sacrifice to Lord Silicon:
http://www.sqlservercentral.com/Forums/Topic904825-150-1.aspx
Rich
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply