Analysis services, Integration services and Reporting Services
Microsoft BI Suite
What do I get when I buy SQL Server?
Meanwhile this is the most complete package what is available. From the process of data to presenting dashboards and reports.
Data Mining Algorithms
We help you see connections that are impossible to recognize by human eyes by using data mining algorithms. For example to predict whether a customer will leave you.
New options in SQL Server 2016
SQL Server supports the use of R, Polybase and Stretch database. It also supports R and JSON and the performance of Analysis cubes is much higher.
Analysis Services Cubus
What are the benefits of working with cubes? Do you want to work with more than one dimension at the same time? We explain how cubes work.
Do you have multiple sources? Then Integration Services is the perfect solution for you to create the perfect reporting environment.
While Power BI is on the rise, SSRS remains the tool to make detailed analysis. This tool helps you to turn data into useful information to make key-decisions
The Microsoft Business Intelligence Suite is one of the most complete Packages in cleaning and processing data to presenting reports and dashboards.
The picture below illustrates all the advantages and it shows how well Microsoft performs compared to the competition.
What do you get when you buy SQL server…?
The bottom row of the image shows the various kinds of data sources your organisation may have.
With the help of the EIM layer, containing Integration Services (a powerfull ETL tool), Master Data Services, Data Quality Services and Power Query (Excel add-in that analysts use for combining data) data is cleaned and placed into an easy-to-understand data model. The data then flows into Analysis Services, in which you can make cubes to report. This layer can also be skipped in case the amount of data is limited. After this the data can be reported to users via Excel, Reporting Services, or other parts of Power BI.
SQL server database
Compared to Oracle: SQL Server has finally caught up Oracle after years. Comparing SQL to Oracle:
The performance is better
Your data is safer
The costs are much lower (Up to 12 times!)
In 2016 SQL Server offers a lot of good options, like:
SQL Server supports the use of R, the biggest open source statistical tool, within the database.
Known disadvantages of R, like limited access to the memory and no parallel calculations belong to the past.
SQL Server is already the safest database, but with this solution your data is always encrypted, until the data is displayed within an application.
Even if hackers are able to break into the database, they can’t see sensitive information like credit card numbers.
Stretch database and Polybase
The cloud is helping you with storage of the data that you don’t use too much anymore.
SQL Server automatically saves the data in the cloud for you, so that you don’t have to worry about enough disk space or slow applications.
, With the help of Polybase you can easily save data in all kinds of formats and use directly like it’s a database. Problems with the wrong kind of format are of the past.
Progress in BI
SQL Server was known to have a somewhat outdated feel, but not anymore. Thanks to the huge amount of improvements in Reporting Services, Mobile reports and Power BI, much higher performance of queries and Analysis Services cubes and support for R and JSON in the database, SQL Server has overcome the competition!
Microsoft SQL Server Analysis Services is a way to provide cubes within your organisation, so employees can make their own analyses for Pivottables in Excel. In a cube you can save readings, for example the revenue for multiple dimensions (like per product,per customer, per store etc). During the refreshing of data in the cube it calculates the measured values for each combination. This is why your employees don’t have to make difficult calculations anymore and you have the results shown right away.
The Analysis Services Cube visually explained
An Analysis Services cube is multidimensional and could have more than 3 dimensions. This may make you wonder what the result is. For the convenience we will take a cube with 3 dimensions. Just Imagine the following:
The red pane are products
The blue pane are customers
The white pane contains dates
Every block in the cube is a combination of a product, customer and date. The block could be an order, with total order value of Product A, bought by customer B on the date of 31-12-2015. The cube has loaded the value already during the refresh so it shows the result very quickly. Also other sections are already calculated.
Analysis Services Data Mining
If you have access to a cube you can also use data mining algorithms. Data mining algorithms can help you discover any relations that are impossible to recognize by the human eye because of the variations within the data or the sheer size of the data set.
Analysis Services features the following algorithms:
Association: Customers who bought product A, also bought product B
Clustering: Sort Customers in a few segments based on signals, for example ‘discount hunters’
Decision Trees: What is the most important factor for a delay in the production process?
Linear Regression: The effect of the price on customer behaviour
Logistic Regression: Resembles the Linear Regression, but the forecast is between 0 and 1 (Suitable for odds)
Naive Bayes: Predict whether a customer will leave (Reliable when you have a lot of data on the customer)
Neural Network: Stock Market Predictions, also recognize objects in pictures
Sequence Clustering: If order is important, for example the click behaviour on your website, logs etc
Time Series: forecasting
If these algorithms are not precisely what you are looking for, you could consider Azure ML (Machine Learning in the cloud). This application facilitates more resource-intensive calculations and has even more algorithms to choose from.
SQL Server Integration Services (SSIS) gives you the opportunity to collect data from multiple sources and to combine them into an easy reporting environment. This is why we use SSIS for setting up datawarehouses for our customers. Besides, it is the perfect tool for data migration because it’s easy to ‘map’ the fields from the old system on to the new system and the migration process can be repeated very easily.
More and more datasources
The world around us has been evolving quickly the last years. Many more data sources are getting available. Companies want to combine all data sources within their organisation in order to make the deepest and most accurate analyses possible. This includes not only data from ERP systems, but also sources like social media, Google Analytics, sensors, applications in the cloud, etc.
The image above shows a number of relatively new data sources, such as sensor data. This number will continue to increase for the years to come. SSIS adapts to these by developing specific connectors to these data sources, which makes it easier to use and combine these sources.
Visual display of your dataflows
Compare left and right in the following picture:
The left shows a stored procedure: SQL scripts stored as text. The right shows a data flow in SSIS, visually represented: Retrieving data from a source and inserting it into a table (destination). In practice we see a lot of stored procedures with hundreds of code lines, it’s hard to understand what happens over there.
In lots of these cases documentation is missing as well. In a SSIS-package (combination of a few SSIS-parts) it becomes clear what happens in these steps in a visual way. In practice, we have noticed that EDF-Auditors also appreciate these visual representations when conducting datawarehouse audits, which may have them give permission faster for the chosen system.
Automate the generation of SSIS packages
Writing manual SQL scrips or clicking and dragging parts in SSIS costs a lot of time. In the past, this made the development of datawarehouses very costly and time-consuming. Nowadays it is possible to automatically generate SSIS-packages with the help of BIML. This reduces the costs and time by a sizeable amount.
Does your server also suffer from a huge pile of SQL scripts, and would you like to replace these scripts with structured SSIS packages? Would you like to know more about populating a datawarehouse? Contact us, and we will decide together if and how we can help you take your data analysis to the next level.
For simple and complex reports for small or big datasets we have many years of experience with SQL Server Reporting Services (SSRS). Reporting services offers great functionality for reporting data.
Although Power Bi is on the rise, SSRS remains the best tool to make detailed analyses or reports with complex business logic. Dynamic Info actively thinks along with you to present big amounts of data on the most efficient way. Sometimes we will suggest other ways to present data, or instead recommend an existing report be changed in order to achieve the same targets or goals.
This is how we turn data into information for your organisation to use in making informed decisions.
Besides reports we also build specialised documents from various environments like Microsoft Dynamics AX. Reports and documents made in SSRS can be exported easily to other platforms like Excel or PDF. Furthermore, reports can be automatically sent to employees or customers on a regular basis.
Reporting Services 2016
The 2016 version is a big improvement compared to the earlier versions of Reporting Services. Where earlier versions were only functional, 2016 is also easy on the eyes. The webportal got a full redesign and looks more contemporary. Furthermore, it is possible to redesign the portal to your corporate colors. Another possibility is to adjust the parameter panel according to your own taste.
With the introduction of prominent KPIs and visual Mobile Reports there is made distinction on the report page between these two and the detailed ‘Paginated Reports’.Microsoft reacts on the launch of Power BI and makes sure that it is easy to import mobile reports on the dashboards in Power BI. Just like Power BI dashboards, reports on the reporting server are also available to watch on a mobile phone. Do you need reports on Microsoft Dynamics, the new management reports or maintenance on existing reports? Then contact us.