Business Intelligence

Esat Erkec

How to integrate Power BI to the Facebook Graph API

May 4, 2018 by

The challenge

With the popularity of social networking and sharing sites and their widespread availability, internet marketing techniques have also been rapidly influenced. Sharing sites with high visitor traffic offers great potential for advertisers, so much so that it is almost unheard of now that brands or products do not have a profile or group on a social networking site. Markets have the opportunity to communicate directly with consumers by creating profiles or groups in sharing sites. Thanks to the comments and forums on the pages, they are able to follow both positive and negative attitudes of the target groups about themselves and thus have more precise ideas about what to do next.

Read more »
Sifiso W. Ndlovu

How to configure OData SSIS Connection for SharePoint Online

April 13, 2018 by

As data warehouse developers, we often have to extract data from a myriad of source systems. Thus, whilst some source systems readily integrate with our ETL tools there are instances whereby we need to install additional drivers and software addons in order to successfully connect and extract data from other source systems. Microsoft SharePoint Online is one such source system that I recently had to extract data from and its connectors are by default not part of the standard SQL Server Integration Services (SSIS) package template. As SSIS developers we often don’t have solid background on environments such as SharePoint, thus figuring out which version of SharePoint Software Development Kit to install in order to enable successful connection from SSIS can sometimes be a frustrating experience. In this article, I try to alleviate some of that frustration by sharing some of my recent experiences relating to getting data out of a SharePoint list using SSIS.

Read more »
Minette Steynberg

The end is nigh! (For SQL Server 2008 and SQL Server 2008 R2)

April 4, 2018 by

Introduction

As with everything else, all good things must come to an end. After hanging around for a good decade SQL Server 2008 and SQL Server 2008 R2 has started the countdown to its End of Life.

D-day for SQL Server 2008 and SQL Server 2008 R2 is set to the 19th of July 2019. Which is approximately a year from now, give or take a couple of months.

If you are still running SQL Server 2008 or SQL Server 2008 R2, now is definitely the time to upgrade. In this article; I am going to tell you why you would want to upgrade to a newer version of SQL Server (2016 or 2017) or Azure SQL Database. I will also discuss some of the tools that you might use to make the upgrade as painless as possible.

Read more »
Esat Erkec

SQL Server Reporting Service: how to handle common end-user requirements with Report Builder

March 26, 2018 by

In this article, we will discuss the SQL Server Reporting Service Report Builder and look at how to handle common client, end user requests using Report Builder. Report Builder is a very powerful tool to create a report for the SQL Server Reporting Service.

Advantages of the Report Builder is that it:

  • exists as a Stand-alone installation
  • can use different data from a lot of data sources
  • can easily deploy reports to SQL Server Reporting Service
  • supports full capabilities of SQL Server Reporting Service
  • provides a productive report-authoring environment
Read more »
Sifiso W. Ndlovu

How to migrate SQL Server 2017 Master Data Services Models into another server

March 23, 2018 by

Often as consultants, we don’t get to work onsite alongside our clients instead we are given copies of clients’ production environment and work on proposed solutions back at our offices. Once development has been completed, we then deploy and integrate our solution back to the client’s production environment. I’ve recently had to adopt a similar offsite development approach whilst working on a project that included development and configuration of master data services. In this article, I will demonstrate how a SQL Server 2017 Master Data Services (MDS) model can be exported from one environment (i.e. MDS Dev) and deployed into another environment (i.e. MDS Prod).

Read more »
Thomas LeBlanc

Time Intelligence in Analysis Services (SSAS) Tabular Models

March 20, 2018 by

In the analytical world, time is an important slicer. The ability to view data over time helps analyze measures in actionable steps. An Analysis Service (SSAS) database can help accomplish this important step. Business users are going to want the ability to see measures a like year to date as well as the period over period comparisons. SSAS enables Time Intelligence with 2 features: the date table and DAX functions.

Read more »
Divya Agrawal

Automate documentation of SQL Server Analysis Server Tabular Model

March 13, 2018 by

There has been an ever-growing discomfort on documenting things especially when it’s very dynamic in nature and I was at one time undergoing the same. We have been developing SQL Server Analysis Tabular Model’s, which were quite a many in numbers and documenting 50 or 60 models manually is a big effort. In addition, development always demands change or enhancements that incurs changes on the documentation that is a continuous process. Adding the changes on the documentation is a time consuming process and sometimes loses track or remains inconsistent with the code built which is a critical issue when the system is in production for years.

I decided to automate the documentation of the SSAS model, to ease the manual effort here as well as save time. In addition, help the end users to see and review the latest change on the models quickly without even bothering the development team.

Read more »
Sifiso W. Ndlovu

Replace bridge tables in a Data Warehouse with SQL Server 2017 graph database

March 8, 2018 by

Just like in Santa’s Bag of Goodies, every release of SQL Server often has something for everyone – be it enhancements to DMVs for the DBAs, new functions for T-SQL developers or new SSIS control tasks for ETL developers. Likewise, the ability to effectively support many-to-many relationships type in SQL Graph has ensured that there is indeed something in it for the data warehouse developers in SQL Server 2017. In this article, we take you through the challenges of modelling many-to-many relationships in relational data warehouse environments and later demonstrate how data warehouse teams can take advantage of the many-to-many relationship feature in SQL Server 2017 Graph Database to effectively model and support their data warehouse solutions.

Read more »
Daniel Calbimonte

How to upload multiple images to SQL Server

March 6, 2018 by

Introduction

Sometimes we need to store information including photos in our database. For example, the photo of the product, the photo of the team members. But how can we store images in SQL Server?

We could create an application in .NET or Java, but if we do not have experience in those programming languages, we could use SQL Server tools to do it.

In this new article, we will learn the following tips that will help us to work with images including how to:

  • insert one image into SQL Server
  • store multiple files into a table
  • verify that the images were inserted
Read more »
Esat Erkec

How to create advanced analytics using Power BI and R scripts

February 27, 2018 by

In this article, we will discover how to find the associations and hidden patterns in a dataset. The tool we will use for this is Power BI and not use any statistical functions or calculation.

Explanation

Power BI is one of the most powerful business intelligence solutions in the market. A cloud-based, user-friendly interface and basic development abilities provide this solution to be more convenient for people to use.

As you may know, Microsoft now supports R script in SQL Server and Power BI. R script support opens magical gates of statistics world to Power BI. Custom Visual offers very useful and interesting visual components for Power BI. In this store, many useful visualizations have been created by Microsoft or the community itself. Some of these visuals use R script infrastructure and this feature allows us to create advanced visual analytic in Power BI. Only a Power BI account is enough to connect and download theses visuals. We will use these visuals in our examples.

Read more »
Craig Porteous

Top 10 things you must document in SQL Server Reporting Services (SSRS)

February 26, 2018 by

Documentation is never fun. I curse having to do it and I curse the person who didn’t do it. It’s a no-win situation.

Luckily I don’t want to tell you to write long documents for setting up or maintaining Reporting Services or creating reports etc. This is more about the main aspects of SSRS that you should be keeping a backup of or maintaining to make your life (and other people’s lives) easier in future. I’ll keep your typing to a minimum.

Read more »
Thomas LeBlanc

How to create Intermediate Measures in Analysis Services (SSAS)

February 19, 2018 by

The whole premise of Analysis Services (SSAS) is to place business logic into a central repository (a database). This central repository should be easy to understand from the average reporting user. Simplicity for SSAS databases starts with the Dimension and Fact tables in a data mart or data warehouse. These tables need to have as little additional computed columns as necessary. With this design, adding additional Measures to the SSAS database, both Multidimensional Cubes, and Tabular Models, can help with performance monitoring of the company’s indicators.

Read more »
Craig Porteous

Migrating SSRS content with PowerShell

January 29, 2018 by

With a distinct lack of up-to-date, fully featured or built-in options to get Reporting Services content cleanly from A to B, it can often be a challenging task maintaining proper Development and QA environments or even moving reports from a SharePoint integrated installation to a native mode one, and vice versa.

I want to explore the two most efficient methods of bulk-migrating Reporting Services content & also explore other options I’ve used over the years and those that have come and gone.

Read more »
Ed Pollack

How to quickly generate a large number of dimension tables for reporting applications

January 19, 2018 by

Description

When building reporting structures, we typically have the need to build fact and dimension tables to support the apps that will consume this data. Sometimes we need to generate large numbers of dimension tables to support application needs, such as in Tableau, Entity Framework, or Power BI.

Creating this schema by hand is time-consuming and error-prone. Automating it can be a way to improve predictability, maintainability, and save a ton of time in the process!

Read more »
Daniel Calbimonte

How to create Excel reports based on SSAS information

December 18, 2017 by

Introduction

In SSAS, when I offer Power BI, Reporting Services, PowerPivot or SharePoint to connect to SSAS, the business analysts look scared. On the other hand, if I talk about MS Excel, everybody seems so happy and comfortable with it.

Excel is still the most popular spreadsheet in the world even when there are a lot of free spreadsheets like OpenOffice and LibreOffice to download, in the BI world, Excel is still the most popular.

Read more »
Daniel Calbimonte

How to build a cube from scratch using SQL Server Analysis Services (SSAS)

December 11, 2017 by

Introduction

I am a DBA consultant and several times, in the past, I have been asked for a good tool to generate fast reports to get information about the company like total sales, sales per year, month, week etc

SSAS is a Business Intelligence (BI) technology that Microsoft bought from Panorama Software in 1996. After the acquisition, the technology was upgraded and evolved and now it is part of the tools that come with SQL Server. This tool is an extremely powerful tool to create analytical reports fast. It works with cubes that are designed to generate reports with optimal performance using aggregated information stored in the cube.

Read more »
Craig Porteous

How to manage Power BI dataset refresh failures

November 30, 2017 by

As I covered in a previous post How to connect to (and query) Power BI and Azure using PowerShell, Power BI can be difficult to manage and administer, unlike on-premises BI solutions. One such concern that will often require quick action is the failure of a dataset refresh.

If your reports and dashboards all rely on live connection or DirectQuery data sources like Azure SQL Database, Azure SQL Data Warehouse or SQL Server Analysis Services (on-premises or in Azure) then you won’t have to worry about dataset refreshes and this post will just be some interesting reading.

Read more »
Esat Erkec

R script word-cloud in SQL Server Report Builder

November 28, 2017 by

What is R

R is a very popular data programing language. R is especially used in data analysis, statistics calculations, predictions, data mining and machine learning. R is used by data scientist, economist, genetic scientists, and statisticians. R has very wide usage in real life. Healthcare, finance, marketing, and manufacturing are some of them.

Read more »
Thomas LeBlanc

Managing A Slowly Changing Dimension in SQL Server Integration Services

October 10, 2017 by

A data warehouse has to be historically correct. This becomes an issue when data like the Product List Price for a previous year needs to be saved historically. Dimensional Modeling methodologies provide a solution for the situation. The Slowly Changing method integrated with components from SQL Server Integration Services solves the issue. This article will look at updating a product dimension table using the Slowly Changing Type 2 Dimension while maintaining the Type 1 columns.

Read more »
Sifiso W. Ndlovu

How to replace hardcoded lookups using SQL Server Master Data Services

September 14, 2017 by

Introduction

A big part of the technical debt in my organization’s data warehouse (DW) and business intelligence (BI) environments relates to hardcoded lookup data. This is data required by the business to make sense of transactional data but was never planned for in the underlying source system and consequently get injected into DW and BI solutions. Inevitably, it is only a matter of time before DW and BI team lose track of the places wherein the hardcoded data reside thus making it difficult to maintain. Furthermore, due to lack of documentation or staff retention, anyone who subsequently takes over these DW/BI solutions can unknowingly create duplicate lookup data. In this article, I explain how we reduced such technical debt in my organization by moving most of the hardcoded lookups into SQL Server Master Data Services (MDS).

Read more »
Page 1 of 712345...Last »