BI Path?
-
I am wanting to redirect my current career path into Business Intelligence (BI) and am looking for feed back from the community on recommended steps to take to get started. If you would so kindly in your responses give where you would start and what the next few steps would be in becoming a BI expert, along with any personal advice you wouldn't mind giving, that would be fantastic. To tell you where I am currently at with SQL, I have experience with SQL (can install, create a database, simple queries, etc.)
Thank you in advance.
-
bsmith 63193 (6/3/2015)
I am wanting to redirect my current career path into Business Intelligence (BI) and am looking for feed back from the community on recommended steps to take to get started. If you would so kindly in your responses give where you would start and what the next few steps would be in becoming a BI expert, along with any personal advice you wouldn't mind giving, that would be fantastic. To tell you where I am currently at with SQL, I have experience with SQL (can install, create a database, simple queries, etc.)Thank you in advance.
I moved into BI in 2010 and focus on SSIS, Datawarehousing and SSIS. It's a good profession and there is a lot of demand.
Regarding "commended steps to take to get started":
Keep learning SQL Server - any and all SQL Server experience you get (installing SQL, writing queries, etc) will help you be a better BI professional. Read up on SSRS, SSIS and SSAS. Learn what a fact table and dimension table are. Start learning about Normal Form, normalization and denormalization. Download and play around with AdventureworksDW. Build a data warehouse at home if you have access to a copy of SQL Server 2008R2/2012/2014 Enterprise install and play around with it. You can buy Developer Edition for like $50.
There's so much to know about BI but I can break it down into 3 groups: Reporting, ETL and Analysis. If you want to go the reporting route learn SSRS and the BI/reporting features of Excel (Excel is becoming more and more of a BI Tool). Play around with PowerPivot and PowerBI. If you are going the ETL route learn about SSIS, keep building your querying skills and take a look at MDS and DQS. If you want to go the Analysis router learn SSAS (tablular [SSAS 2012+] and multi-dimensional) and start playing around with MDX and DAX.
Regarding "becoming a BI expert":
As far as I'm concerned the BI book to read, study and keep studying until you can recite it is The Microsoft Data Warehouse Toolkit: With SQL Server 2008 R2. Even thought they, unfortunately, don't have a newer version of the book this is still the best book out there for building a DW/BI system using the Microsoft BI stack. Get involved with the SQL community; e.g. SQL PASS, SQL Saturday and your local BI User Group (if there is one, otherwise start one).
Either way:
SQL Server Central is a goldmine. Check out the BI forums, the Stairways (e.g. Stairway to Reporting Services, Integration Services and MDX). Lastly, Big Data and BI are becoming one in the same - try to stay current with what's going on in the world of Big Data.
"I cant stress enough the importance of switching from a sequential files mindset to set-based thinking. After you make the switch, you can spend your time tuning and optimizing your queries instead of maintaining lengthy, poor-performing code."-- Itzik Ben-Gan 2001
-
There's so much to know about BI but I can break it down into 3 groups: Reporting, ETL and Analysis. If you want to go the reporting route learn SSRS and the BI/reporting features of Excel (Excel is becoming more and more of a BI Tool). Play around with PowerPivot and PowerBI. If you are going the ETL route learn about SSIS, keep building your querying skills and take a look at MDS and DQS. If you want to go the Analysis router learn SSAS (tablular [SSAS 2012+] and multi-dimensional) and start playing around with MDX and DAX.
First of all, thank you for your feedback. It is greatly appreciated. Could explain the main differences between the 3 groups you mentioned above: Reporting, ETL, and Analysis, and what each entails? What would one do in each of those groups? It would help me to decide which route I might go down.
-
bsmith 63193 (6/3/2015)
There's so much to know about BI but I can break it down into 3 groups: Reporting, ETL and Analysis. If you want to go the reporting route learn SSRS and the BI/reporting features of Excel (Excel is becoming more and more of a BI Tool). Play around with PowerPivot and PowerBI. If you are going the ETL route learn about SSIS, keep building your querying skills and take a look at MDS and DQS. If you want to go the Analysis router learn SSAS (tablular [SSAS 2012+] and multi-dimensional) and start playing around with MDX and DAX.
First of all, thank you for your feedback. It is greatly appreciated. Could explain the main differences between the 3 groups you mentioned above: Reporting, ETL, and Analysis, and what each entails? What would one do in each of those groups? It would help me to decide which route I might go down.
The image below represents a BI system using Microsoft 2012 products and the 5 layers of a BI/DW system:
A company wants to build a system that sucks in all the data relevant to their business and format it in a way that helps them make high-level business decisions. To build such a system you first identify where the required data lives (The Data Source Layer). That usually consists of database servers, some access databases and/or Excel files and flat files/xml files used by one or more proprietary systems to name a few. This data is volatile meaning it is unpredictable and constantly changing.
In a typical BI system there is a Data Transformation layer where data is cleansed, deduplicated, and transformed into a format that is not volatile and better for reporting. The data warehouse is the business's "Single version of truth". It's populated on a regular basis and the data is formatted in a way that it can be easily reported against or turned into an OLAP cube. Sometimes subsets of that data are made available via data marts (think of as a mini-data warehouse). This area is known as the Data Storage and Retrieval area.
Most BI developers will tell you that the "real BI" happens in the Analytic Layer. That's where OLAP cubes and (beginning in SQL Server 2012 power pivot workbooks) live. A cube is a mutli-dimensional database. Instead of rows and columns you think in terms of measures and dimensions. Cubes are for "slicing and dicing" data. Microsoft's tool for Cubes and Analytics is SSAS.
Then, finally you have the place where this data is consumed. This is known as the Presentation Layer. This is where SSRS, Sharepoint, Excel and various "Power BI" tools live. In my experience there are reports that come from a cube and others from the data warehouse(s)/data mart(s) as well as OLTP data bases (though reporting against transnational OLTP data is baaaaaad.) Sometimes the business will get a third party tool that sits on top SSAS and get all their reports from there. As I said before, more and more people are using Excel for reporting too these days.
SSIS is the main tool for the ETL, SSAS is the main tool for the Analysis and SSRS and Excel are the main tool for the Reporting.
Now note this image:
If you work in the ETL realm (titles such as ETL Developer/architect, Data warehouse Developer or SQL Developer) you will write SSIS packages, do some T-SQL development). You may also deal with MDS and DQS. You will live in SSMS and Visual Studio. If you are in the Reporting Realm (titles include Report Developer or SQL Developer) you will write SSRS reports and develop SQL and/or MDX queries. If you work in the Analysis realm you will be designing cubes. You will work with the ETL developer on what you need. You will work with the Report Developers to determine what they need.
The job title BI developer means different things and varies from business to business. Again, Reporting is the easiest and lowest paying of the three (though it can still be quite lucrative). ETL is the in the middle and true BI Architects that develop cubes are probably the highest paid.
Most of this is discussed, more or less, in the aforementioned Kimbal book.
Lastly, you should take the time to learn and understand XML. I have done all of the above and knowing XML has made my job easier and given me a huge advantage. The better you know SQL, the better off you will be as well.
"I cant stress enough the importance of switching from a sequential files mindset to set-based thinking. After you make the switch, you can spend your time tuning and optimizing your queries instead of maintaining lengthy, poor-performing code."-- Itzik Ben-Gan 2001
-
Thanks. Nice Information obtained.:-)
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply