Microsoft SQL Server 2014 Business Intelligence

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.

Sounds easy enough right? Well, in reality, not really. 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.

Therefore, there are also ways of delivering rich business intelligence data to the end-user in relatively straightforward ways too. 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. This is not the case further down the workflow towards business intelligence optimized data. 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.

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. 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.

It is also possible to start creating reports that query the data warehouse directly. It is therefore quite common for smaller companies to stop at this point. 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 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. 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. In any case, the result is the same.

You can pick a fact or cell in your 3D spreadsheet and drill down to a pre-calculated value along any chosen referential dimension. 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.

Being 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. This tool can be used for any operation that requires loading, transferring, enriching and sending data. This process entails mapping the data warehouse star-schemas to a cube structure by fact and dimension tables.

This allows for the generation of an XMLA scripts which can construct the cube structure and process the transfer of data from the data warehouse to the cube. Presentation Once the underlying structure is created and the flow of data between the different levels is scheduled the business intelligence developer must now find a way to present this data to the end-user.

There are a number of ways to do this in SQL Server business intelligence. This is a whole branch of SQL Server dedicated to building, hosting and presenting reports based on business intelligence data.

Developers can create reports in Visual Studio and deploy them to the report server. With SQL Server we saw a greater integration with SharePoint and through Power View and Performance Point it is possible for end-users to create their own rich and dynamic reports through a web interface. One could also connection Excel spreadsheets directly to BI data sources of course.

This is quite practical for the more adventurous end-user. There are patterns to follow and tools to use to create BI systems, transfer the data between them and there are other tools to create report hosting servers and the reports themselves.

OEM Project Professional 2019 | Pinnacle Studio 19 Ultimate

TAGS:System Center, Nero 11, Visio 2019

Copyright © 2019 All right reserved