This is provided by SQL Server Data Tools (formerly known as SQL Server Integration Services or SSIS). SQL Server Business Intelligence not only requires the underlying structure that supports these databases but also the ETL (Extract, Transform, Load) systems that allow the transfer of data between the OLTP database and the data warehouse. Putting all the jargon to the side, when you get to this stage you are able to query a read-only optimized database and return business intelligence rich results for extremely large datasets in blisteringly fast response times (when compared to similar queries on a traditional OLTP database).īeing multidimensional and read-only in nature a cube not only takes up less space on a disk but also avoids concurrency issues completely by not requiring locks. You can pick a fact (or cell in your 3D spreadsheet) and drill down to a pre-calculated value along any chosen referential dimension. In reality, this cube can have many dimensions, but without having a profound knowledge of quantum physics the human mind is not really capable of imagining such a structure. It sounds like a mouthful and behind the scenes, it is quite complex but in practice, you can think of this very basically as a spreadsheet in three dimensions (hence the word cube). This next stage involves the creation of a Multidimensional Online Analytic Processing cube (MOLAP).
#INSTALL SQL SERVER BUSINESS INTELLIGENCE DEVELOPMENT STUDIO LICENSE#
This is understandable as the less expensive license levels of SQL Server often do not include the Analysis Services tools required to move on the next stage of analytic processing. It is therefore quite common for smaller companies to stop at this point. It is also possible to start creating reports that query the data warehouse directly. The most common structure is that star-schema model which has a central fact table containing measurable or calculable data surrounded by dimension tables that contain reference data that describe the fact, or measure, in the central table.Īs mentioned previously it is possible to host both the OLTP instance and the data warehouse in the same SQL Server instance. The first step away from an OLTP system is the Data Warehouse This is generally hosted in a normal SQL Server instance as a normal database however it has a de-normalized database model instead of a relational one. Broadly, there are two types of systems with one being even more optimized than the other and therefore the endpoint in a mature BI dataflow. This is not the case further down the workflow towards business intelligence optimized data. To understand this difference it is important to realize that a good production server is generally geared towards OLTP (online transaction processing) which means it has a highly relational structure, indexes and data types that are conducive to INSERTs, UPDATEs, and DELETEs.
This practice involves transforming data that is optimized for a heavily transactional system and moving it to one that is more suited for analytic querying. The structure of SQL Server Business IntelligenceĬentral to all SQL Server Business Intelligence solutions exists a standardized method of preparing data to make it ready for consumption in the business intelligence world.
Therefore, there are also ways of delivering rich business intelligence data to the end-user in relatively straightforward ways too.
Luckily, these requirements have led to the development of a large array of very handy tools which can be used to generate different types of reports in different formats. It takes a mix of skills, tools, planning and a fair amount of time to put in place a large-scale BI solution to cover a modern company’s needs. Sounds easy enough right? Well, in reality, not really. SQL Server Business Intelligence, on the other hand, is about making sense of these numbers and letters and transforming them into presentable information that provides decision-making value to the right people at the right time.
If you’re a DBA you may be used to maximizing performance by rewriting stored procedures, creating indexes or running profiling traces. If this is the case you’re probably used to managing a lot of valuable data. If you’re reading this article you probably have prior experience with Microsoft SQL Server or at least one other relational database management system (RDBMS). You may be wondering what Business Intelligence is.
0 kommentar(er)
