Generating Plots Automatically From PowerShell and SQL Server Using Gnuplot

When you are automating a number of tasks, or performing a batch of tests, you want a way of automating the production of your plots and graphs. Nothing beats a good graphical plot for giving the indications of how the process went. If you are using PowerShell and maybe also SQL Server, it pays to use a command-line plotting tool such as Gnuplot to do all the hard work. It turns out to be handy for a range of data jobs, turning PowerShell into a handy data science tool.… Read more

Using an R Package within SQL Server with Real time analysis in Power BI

We all know how easy it is to run R, together with dependent packages, from SQL Server on order to do statistical analysis, and then turn the result into a real-time graph using Power BI; but can you actually do it? Saurabh Desai gives a step-by-step hand-holding guide to turn even the most timid into a BI guru. There is nothing difficult in using R in SQL Server 2016 once you know the steps.… Read more

Using SQL SERVER and Cloud Hosted Databases with OBIEE 11g

If your organisation is committed to using Oracle Business Intelligence Enterprise Edition (OBIEE) 10g/11g /12c as their BI solution, you aren't thereby committed to using Oracle throughout your organisation. You can use a range of data sources including SQL Server, and save a great deal of money by doing so. Sadly, Oracle will only support the use of the venerable SQL Server 2008 R2. Zafar Ali demonstrates how to connect OBIEE to the world beyond Orac… Read more

Reconciling Data Across Systems Using a Reconciliation Hub

In many enterprises, where there are a number of separate systems engaged in processing data, there arises the daunting task of checking and reconciling data as it flows between systems. Discrepancies in data must be detected, tracked and corrected as quickly as possible: there is no room for error in doing this. A Reconciliation Hub can provide the answer, as Rahul Gupta explains.… Read more

Using Power BI Desktop to Visualize SQL Server Metadata

You can easily use PowerBI Desktop to show graphically how your database is growing, which tables are taking the most space, how various parts of SQL Server is consuming memory, its use of indexes and so on. Sure, you can create graphs in SSMS, but with PowerBI, you can create reports that you can then publish to others, and which allow drill-down. It is a great way to get familiar with PowerBI Desktop as well. Rob Sheldon shows how simple it is to do.… Read more

Importing Excel Data into Power BI Desktop

In any commercial setting, Excel Spreadsheets remain the preferred way of collecting and analysing data, and it makes sense that it should be easy to get the data into PowerBI so it can be made more generally available for sharing and further analysis. As well as the data, we'd probably need the other analysis components such as the Power Pivot tables, Power View visualizations, Power Query queries. It is all possible, and Robert Sheldon demonstrates how.… Read more

More Power BI to your Elbow

Power BI Desktop continues to evolve. There have been many improvements to the reporting side that together make it easier to get from the data to the visualisation as quickly as possible. You can now create line charts that let users drill down into hierarchical data. There are now ways of adding dynamic reference lines to a visualization that provide users with relevant reference points. Robert Sheldon demonstrates how to combine these features to great effect.… Read more

Power BI for Data Modelling

Power BI has a lot of uses, but one of the most interesting of them is to model and shape data to make it easier for self-service BI. Power BI is a full-stack solution that includes data loading, data modelling, metrics, reporting and distribution. It can take the source data, and perform in-process data modelling relatively easily as well a providing an easy to use and powerful user interface for analytics and reporting.… Read more

Azure SQL Data Warehouse: Explaining the Architecture Through System Views

The architecture of Azure SQL Data Warehouse isn't easy to explain briefly, but if you have some useful queries that access the management and catalog views, and diagrams that show how they relate together, you can very quickly get a feel for what is going on under the hood. By using and extending these queries that use these views, you can check on a variety waits, blocking, status, table distribution and data movement in ASDW.… Read more

Direct Query with Power BI

Power BI allows you to connect directly to the data just as if it were a “standard” connection, and modify and extend the data to support analytics. By using DirectQuery, you can build reports based on extremely large datasets where re-importing data not a practical solution. It also allows you to do data aggregations on the server. There are, of course, restrictions as well but it is nevertheless a worthwhile technique to use where appropriate. Adam Aspin explains all. … Read more

SSIS in SSDT: The Basics

SQL Server Integration Services (SSIS) is a fast and reliable way of importing and exporting data, but it can be tricky to learn, initially. Rob Sheldon provides a beginner's guide to get you started with using SSIS with Visual Studio and SQL Server Data Tools (SSDT)… Read more

Dynamic Images in SQL Server Reporting Services

It is relatively easy to provide a smart BI solution when your customer has all the resources for a new hardware and software platform, but it takes ingenuity to provide an effective simple solution requiring data-driven pictorial information on a range of template diagrams with just an existing platform of SQL Server, Reporting Services and .NET. … Read more

Predictive Analysis Basics

Statistics holds out the promise of teasing out significant relationships and to determine cause and effect. Now that so much data is easily available, does that mean that we can at last harness these techniques to predict future trends and outcomes? Feodor gives an introduction to some simple techniques of predictive analytics, and explains the 'whys', 'hows' and 'wherefores'. … Read more

Creating a Date Dimension in a Tabular Model

As well as its multidimensional model, SQL Server Analysis Services (SSAS) now has a tabular model of database that either runs in-memory or in DirectQuery mode. The in-memory analytics engine allows the users of Excel or Power View to base reports on tabular model objects. Having shown how to handle date-based information using the Multi-dimensional model, Dennes now turns his attention on the in-memory tabular model.… Read more