March 27, 2008 at 11:54 am
Hi all,
I need some advice on BI development.
About 2 months back or so, my boss put me into a charge of developing BI solution for company. For few days, I built a excel pivot report with SQL Query. I also demostrated some simple SSRS report using SQL query, but now management wants me to build complex reports, cubes, and dashboards and having zero knowledge in TSQL, MDX, C, Stored Proceduer etc. are killing me.
So far I've looked at pretty much every Hand-On, and Step-by-Step books on SSRS, SSIS, and SSAS, and they aren't really helping...
Can you guys give me some advice on what lanague I should study first, and good tech books?
Thanks in advance!
March 27, 2008 at 1:53 pm
Read the "Data Warehouse Toolkit" by Kimball and Ross. It should give you a much better idea where to start and what kind of questions to start asking your boss, etc.
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
March 27, 2008 at 1:56 pm
Depending on your budget, there are many consulting companies that will do guide you through this or do it for you (yes, mine is one of those).
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
March 28, 2008 at 5:45 am
It is always possible to create really amazing reports using just SSRS and TSQL.
So to start off with I would recommend u Forget MDX and C.
You can always move to SSAS and MDX queries once your requirments have stabilized.
Most importantly is not as hard as you think , and msdn should be able to provide you with enough ammo to do a decent job.
Jay
April 1, 2008 at 5:41 am
If you are a complete Microsoft shop, I would also recommend (in addition to the Warehouse Toolkit RBarryYoung suggested) the Microsoft Data Warehouse Toolkit.
Also, I have a very important point regarding messaging and expectation management.
If your company wants a true "BI Solution", it is important they understand that it is a long-term, iterative process that needs to be designed, planned and built - just like any other application you may build. This is especially true due to the fact that a successful BI delivery just creates more demand and future requirements. I've been part of a BI team that was built without a long-term view in mind and it gets very ugly, very quickly.
Obviously all of these things depend on your company's willingness to accept that truth. In my experience, the earlier you start the communication about the true nature of a BI solution, the better off you will be.
Hope this helps.
Dan
April 1, 2008 at 10:43 am
I agree that you cannot just cobble this together in a few weeks.. you really need to plan it out a bit and make some careful decisions with guidance from the Tookit book or books.
Do a bit of research; a little time up front will pay off in the end.
Also, with Sql Server Reporting Services, you can do a lot with the Matrix control (pivot tables!). We use linked reports to drilldown from one level to another.
Good luck.
April 2, 2008 at 10:14 am
I found SQL Books Online was very useful - it's available through the help section in the SQL client. Go slow, and be prepared to try new things a lot.
I have a little guide I prepared for some clients who were new to SQL programming, going over some of the more-common commands & syntax. I'd be happy to send it to you - my email is rschultz@100wattsolutions.com.
Finally, a word of warning; your BI is only as good as your data. The reports you generate will likely become a nightmare really quickly if data completeness/quality is poor, and it almost certainly is poor. The earlier comment about this being an iterative process is bang-on, and a big part of that iteration is about data cleansing.
April 2, 2008 at 10:26 am
thank you all for your advice!
April 3, 2008 at 11:56 am
Another question. I am thinking of attending one of these 5 day BI boot camp class. How are these classes? Worth it?
April 4, 2008 at 6:27 am
It sounds like your data is getting into the database by some other method that you don't have to worry about and that your task is to give your boss(es) their Key Performance Indicators in a dashboard format.
I notice you did not mention MOSS 2007 (or SharePoint 2003). As long as there are other methods the data is being collected, I can build a 'mostly complete' BI Reporting KPI dashboard system. It can even have multiple dashboards to allow drill-down and multiple-departmental dashboards.
Since you apparently have training dollars available, I would suggest the time would be better spent on a class in T-SQL and another on basic SSRS and another on advanced SSRS. The 'boot camp' format so many time requires you to still spend two or three weeks pre-boot camp studying. Then when you attend the session, you are herded through like cattle to for the knowledge into your head. After that, you come back burned out and after a couple of weeks you half-remember the stuff. Perhaps there would be an offering for T-SQL/Beginning SSRS for a week. Hands-On helps as well. Then you could bring back the code and learn more when you get back to work.
My own experience is that I was thrown into T-SQL using Ingres on a VAX in the 1990's. It took about 6 months to really feel like I was 'average' in it. I was a FORTRAN programmer, so T-SQL was not my primary language. Over the years I have migrated my skillset to Oracle and since 1998, SQL Server. When SSRS came out, I jumped in headfirst because I saw how powerful this could be. That was also the time in my career that I was forced to use SharePoint -- and that turned out to be a good thing.
Last advice and I'll shut up... Start slow, pick up speed as you learn more, I try to allow 10% of my time to learning something new (4 hours/week). Use this website and other like it. Use Google (or your preferred search engine) to get help. Most really good programmers/DBAs and people in general are more than willing to help because they were in your shoes at some point.
-- Al
April 4, 2008 at 10:20 am
Finally, a word of warning; your BI is only as good as your data. The reports you generate will likely become a nightmare really quickly if data completeness/quality is poor, and it almost certainly is poor. The earlier comment about this being an iterative process is bang-on, and a big part of that iteration is about data cleansing.
You need to plan time for cleanup as part of the ETL process. That will likely take the most time. As you dig into the data, you'll have to decide if the data should be cleaned at the source or part of the cleanup.
I also want to reiterate that this is a long term project. You won't be able to put this together in just a few weeks, and the moment you field something, if it's good, it will be followed by more requests.
Good luck!
April 4, 2008 at 2:57 pm
Adding a little more advice to the heap here ...
You need to let your management know that what they are asking you to develop is a constant, never-ending cycle of loading, scrubbing, cleansing, transformed and reporting on your company data. As you continue to work with your data you will be able to reduce the time it takes (hopefully) in which you begin your process until your users can run their reports.
Your existing "live" systems are often referred to as OLTP (On-Line Transaction Processing) systems, which help keep your company in business. What you have been asked to create is a completely different perspective of the information and data that these systems create. The (most likely) company wide set of data gets "Extracted, Loaded and Transformed" (ETL) into an OLAP (On-Line Analytical Processing) system, which is what your SSRS Reports will run against.
References to get you started:
The Data Warehousing Information Center
This site should allow you to get a grasp of all these new terms and concepts.
OLTP
http://searchdatacenter.techtarget.com/sDefinition/0,,sid80_gci214138,00.html
ETL
http://www.wisegeek.com/what-is-etl.htm
OLAP
http://searchdatamanagement.techtarget.com/sDefinition/0,,sid91_gci214137,00.html
Also any Data warehousing books written or co-written by Ralph Kimball will do you well. Ralph Kimball, is an author on the subject of data warehousing and business intelligence. He is known for long-term convictions that data warehouses must be designed to be understandable and fast. His methodology, also known as dimensional modeling or the Kimball methodology, is frequently used to allow sharing of conformed dimensions.
He writes the "Data Warehouse Designer" column for Intelligent Enterprise magazine and is the author of best-selling books The Data Warehouse Lifecycle Toolkit and the Data Warehouse Toolkit published by Wiley and Sons. He is listed in the Database Hall of Fame.
http://www.kimballgroup.com/html/articles.html
The key to you having a successful go at this is that Management is aware of what they currently have versus what that have asked for. The second, and hardest part, is when they realize how much work is involved in getting there.
Keep us posted on your progress,
"Key"
MCITP: DBA, MCSE, MCTS: SQL 2005, OCP
Viewing 12 posts - 1 through 11 (of 11 total)
You must be logged in to reply to this topic. Login to reply