BI Insight

SEPTEMBER 22, 2022

In September 2014, I wrote a blog post on dynamically passing parameters from PowerPivot to a SQL Server stored procedure using VBA. Back then, VBA was a real lifesaver. It perhaps still is for many of us. But frankly, I even forgot how to write VBA. Maybe it is time to look at it again. … Continue reading Dynamically Passing Parameters to a SQL Stored Procedure in Excel 365 Using Power Query.

162

162

BI Insight

JULY 20, 2022

Slowly changing dimension (SCD) is a data warehousing concept coined by the amazing Ralph Kimball. The SCD concept deals with moving a specific set of data from one state to another. Imagine we have a human resources (HR) system; Stephen Jiang is a Sales Manager, managing 10 sales representatives in his team. The following screenshot shows the sample data: Today, Stephen … Continue reading Slowly Changing Dimension (SCD) in Power BI, Part 1, Introduction to SCD.

Business Intelligence 130

Business Intelligence Business Intelligence 130

BI Insight

JUNE 27, 2022

The previous post explained what Thin reports are, why we should care and how we can create them. This post focuses on a more specific topic, Report Level Measures. We discuss what report-level measures are, when and why we need them and how we create them. If you are not sure what Thin Report means, … Continue reading Thin Reports, Report Level Measures vs Data Model Measures.

Data Modelling 208

Data Modelling Data Visualization Visualization 208

BI Insight

APRIL 26, 2022

Shared Datasets have been around for quite a while now. In June 2019, Microsoft announced a new feature called Shared and Certified Datasets with the mindset of supporting enterprise-grade BI within the Power BI ecosystem. In essence, the shared dataset feature allows organisations to have a single source of truth across the organisation serving many … Continue reading Thin Reports, What Are They and Why Should I Care and How Can I Create Them?

Data Visualization 195

Data Visualization Visualization 195

BI Insight

MARCH 31, 2022

When I decided to write this blog post, I thought it would be a good idea to learn a bit about the history of Business Intelligence. I searched on the internet, and I found this page on Wikipedia. The term Business Intelligence as we know it today was coined by an IBM computer science researcher, … Continue reading Business Intelligence Components and How They Relate to Power BI.

Business Intelligence 208

Business Intelligence Business Intelligence IBM Support Data Warehouse 208

BI Insight

FEBRUARY 27, 2022

OData has been adopted by many software solutions and has been around for many years. Most solutions are using the OData is to serve their transactional processes. But as we know, Power BI is an analytical solution that can fetch hundreds of thousands (or millions) rows of data in a single table. So, obviously, OData … Continue reading Optimising OData Refresh Performance in Power Query for Power BI and Excel.

278

278

BI Insight

JULY 1, 2013

Reporting services 2012 integrated with SharePoint 2013 In this post I’m going to describe installing SSRS 2012 integrated mode with SharePoint 2013. As some guys are pretty new to SSRS 2012 or SharePoint or even SSRS integrated with SharePoint, I am explaining the situation from scratch. Requirements: SQL Server 2012 Enterprise (or Business Intelligence) edition … Continue reading Installing SQL Server Reporting Services (SSRS) 2012 integrated mode with SharePoint 2013.

Business Intelligence 130

Business Intelligence Business Intelligence 130

BI Insight

JULY 1, 2013

SSAS 2012 supports three different approaches for creating a BISM (Business Intelligence Semantic Model): 1. Tabular a. Uses relational modelling constructs like such as tables and relationships b. Uses xVelocity in-memory analytics engine for sorting and data calculations c. Needs to use SSDT (SQL Server Data Tools) to implement d. Can import data from relational … Continue reading Three different approaches for creating a BISM in SSAS 2012, Tabular vs.

Business Intelligence 100

Business Intelligence Business Intelligence 100

BI Insight

JULY 3, 2013

There are some cases that we need to drop all tables database wide. For example when we have a test database and we need to delete all tables from the database and recreate some other tables for testing we need to drop each table manually using the GUI. Assume we have 50 tables in the … Continue reading How to drop tables database wide using T-SQL.

100

100

BI Insight

JULY 3, 2013

In my previous post called “How to drop tables database wide using T-SQL” I’ve explained that how we could delete all tables database wide in SQL Server. But, what if there are some other schemas other than “dbo” and some tables that are created in those schemas? If we just delete “[dbo].” from the code … Continue reading Drop tables from selected schemas database wide.

100

100

BI Insight

JULY 4, 2013

SQL server 2014 Community Technology Preview 1 (CTP1) is ready to download here: [link] Key Features Project code-named “Hekaton” One of the most exciting features in this version is a project code-named “Hekaton” that provides in-memory OLTP capabilities. As Hekaton is built into core SQL Server database it will improve the performance of the database … Continue reading Microsoft SQL Server 2014 Community Technology Preview 1 (CTP1) is ready to download.

100

100

BI Insight

JULY 4, 2013

What does it actually mean that the database doesn’t have a database owner?! It seems odd, but, it is possible. In some cases that the database is created by an application it really happens that the database doesn’t have any database owner. For instance, the databases generated by CRM Deployment Manager doesn’t have database owner. … Continue reading How to define database owner SQL server wide.

100

100

BI Insight

JULY 4, 2013

After downloading the SQL Server 2014, mount the media file downloaded and run “setup.exe” In SQL Server Installation Centre click on “Installation” Click on “New SQL Server stand-alone installation or add features to an existing installation” Note that if you have any of previous versions of SQL Server installed on your machine the setup process … Continue reading Installing SQL Server 2014 CTP1.

130

130

BI Insight

JULY 8, 2013

SENSITIVE DATA! It’s an interesting topic! In this post I’m trying to explain how to hash data to increase security during ETL. Assume that we have sensitive data stored in several secured source systems. The source systems are located in different countries and different regions. As the source systems themselves are secured, how we can … Continue reading How to hash sensitive data for maximising security in SQL Server 2005 and later versions?

Business Intelligence 130

Business Intelligence Business Intelligence 130

BI Insight

JULY 10, 2013

Sometimes you might feel you need to store the database diagrams in a file basis. I’ve faced to this feeling specially when I was testing some databases which were under development. I didn’t like to create the whole database diagrams that I’ve created before in a database that a new version of it is newly … Continue reading How to store a SQL Server database diagram into a file and share it with others?

130

130

BI Insight

JULY 20, 2013

There are some cases that you do not have any documentations for the database that you are working on and you need to investigate the entity relationships and so on. We can understand the database entity relationships better if we can find the number of references to the table or the number of tables’ dependencies. … Continue reading Count tables’ references and tables’ foreign keys.

Documentation 100

Documentation 100

BI Insight

JULY 23, 2013

First of all I want to explain the process of sending SSIS Logs through email for better understanding. As you might know you can save SSIS logs in several ways for administration purposes. In this article our focus is on how to send SSIS logs to the administrator whenever an event like a package failure … Continue reading How to send SSIS logs (errors) through email.

Business Intelligence 130

Business Intelligence Business Intelligence 130

BI Insight

JULY 23, 2013

First of all you need to read my previous article called “How to send SSIS logs (errors) through email” as the processes are pretty the same. However, you need to create some changes in the Execute SQL Task (ref.: section “J” number 5 and 12 of “How to send SSIS logs (errors) through email”). As … Continue reading How to send more precise SSIS logs (errors) through email.

Business Intelligence 130

Business Intelligence Business Intelligence 130

BI Insight

AUGUST 3, 2013

The SQL Server 11.0 release (code named “Denali”) introduces a new data warehouse query acceleration feature based on a new type of index called the columnstore. Columnstore indexing is officially announced in SQL Server 2012. It is working based on xVelocity memory optimised technology and it improves data warehouse query performance significantly.

Data Warehouse 130

Data Warehouse Planning Business Intelligence Business Intelligence 130

BI Insight

AUGUST 5, 2013

In this short post I’ll show you how to create database schema using dynamic SQL. It’s easy, just take a look at the following code: declare @SchemaName varchar(max) set @SchemaName = ‘YOUR_SCHEMA_NAME’ if not exists (select 0 from sys.schemas where name=@SchemaName) exec(‘create schema [‘+@SchemaName+‘]’) go Enjoy!

100

100

BI Insight

SEPTEMBER 8, 2013

If you are working in a company that your managers are getting database documentation seriously, thumbs up! One of the ways to write useful documentation that is really effective is using SQL Server extended properties. We can add extended properties to almost all SQL Server objects by right clicking on the object-> select properties-> select … Continue reading How to query extended properties.

Documentation 130

Documentation 130

BI Insight

SEPTEMBER 9, 2013

It is really easy. You just need to add the object name as a parameter to “SYS.SP_MS_MARKSYSTEMOBJECT”. In the following code we are creating a system table named“TestSysTable”: IF OBJECT_ID(N’TestSysTable’) IS NULL begin CREATE TABLE dbo.TestSysTable ( C1 int NOT NULL, C2 int NOT NULL, C3 int PRIMARY KEY … Continue reading How to create a system object (Table, Stored Procedure, View…).

100

100

BI Insight

SEPTEMBER 9, 2013

We discussed in one of the previous articles called “How to store a SQL Server database diagram into a file and share it with others?” we can store database diagrams in files and share the files with others. In this article I’m describing very fast and easy ways to make a copy of existing … Continue reading How to copy or migrate database diagrams into another database.

Business Intelligence 130

Business Intelligence Business Intelligence 130

BI Insight

SEPTEMBER 17, 2013

In the previous article we discussed about how migrating a database diagram into another database. In this article I’ll quickly show you how to install database diagram support. As mentioned in the” How to copy or migrate database diagrams into another database” article, we need to install database diagram support to be able to see … Continue reading How to install database diagram support programmatically.

130

130

BI Insight

OCTOBER 16, 2013

Introduction We can create informative reports on our website analytics using Tableau. Tableau has the ability to connect to a Google Analytics data source. The problem is that if Tableau cannot pass the credentials to Google automatically, the following error message pops up. “An error occurred when publishing the data source. GetOAuthUsername caught exception processing … Continue reading Publishing A Google Analytics Data Source Or A Workbook Linked To A Google Analytics Data Source int

130

130

BI Insight

DECEMBER 8, 2013

In some cases we need to do a single task for lots of SQL Server instances. Assume that we have a web based programme. The programme’s database is distributed across the country and we have 10 different virtual (VM) servers to host the programme’s databases. The programme is working based on some configurations that are … Continue reading Operating on different source SQL Server instances in a single SSIS package.

Business Intelligence 130

Business Intelligence Business Intelligence 130

BI Insight

FEBRUARY 12, 2014

In this post I’m explaining how you can deploy a developed SSIS project to several different environments. It might have happened to you that there are several environments that you need to deploy the SSIS projects to. Assume that you have DEV, QA, UAT and PROD environments. Some organisations might have even more environments. Also, … Continue reading Deploying SSIS Projects to Different Environments.

Business Intelligence 130

Business Intelligence Business Intelligence 130

BI Insight

FEBRUARY 26, 2014

Problem: There is a group of users that are not intended to have direct read access to the database tables. There are some predefined database views that the users should be able to see the data through those views. In our case, the users shouldn’t be able to even see the tables in SSMS or … Continue reading How to manage the user access rights to see database views but not source tables using T-SQL.

130

130

BI Insight

APRIL 21, 2014

You are working on a BI project that involves SSAS Tabular model. As you cannot process the model manually you need to develop a solution to process the model automatically. There are two scenarios here: You have an up and running SQL Server Integration Services (SSIS) instance: In this case you can build a SSIS … Continue reading How to Automate SSAS Tabular Model Processing.

Business Intelligence 130

Business Intelligence Business Intelligence 130

BI Insight

JULY 20, 2014

You’ve changed something in your Tabular model and you’re facing an ugly “Error: Object reference not set to an instance of an object.” message, don’t rush. Just go to the table you’ve made some changes and click on “Partitions” button. Then click “Refresh Preview” button and click OK. All done! The post Resolving “Object reference not set to an instance of an object.” Error When Deploying your Tabular Model appeared first on BI Insight.

Business Intelligence 100

Business Intelligence Business Intelligence 100

BI Insight

AUGUST 28, 2014

In this post I’ll explain how to create a simple date dimension to use it in your data warehouses and your BI solutions. So, this article is for you if you need a fast and easy way to make a simple date dimension that supports the most commonly used date elements like · Integer date … Continue reading Creating a Simple Date Dimension Using Recursive Common Table Expressions (CTE).

Data Warehouse 130

Data Warehouse 130

BI Insight

AUGUST 30, 2014

Today I want to explain how you can pass parameters to a SQL Server stored procedure. I myself was looking for a way to pass parameters to a SQL Server stored proc from Power Query. I spent a lot of time to search for a good article over the internet that explains how I could … Continue reading Power Query and SQL Server Stored Procedures. The post Power Query and SQL Server Stored Procedures appeared first on BI Insight.

130

130

BI Insight

SEPTEMBER 7, 2014

In this post I want to share my experience with refreshing data in Power Query and the way it affects loaded data to an Excel worksheet. I was loading sales data from SQL Server and I noticed that there is no easy way to define a 1000 separator in Power Query. So I decided to … Continue reading Resolving Format Cells Change after Refreshing Data Sources in Power Query.

130

130

BI Insight

SEPTEMBER 21, 2014

In this post I express an easy way to refresh a PowerPivot model dynamically based on SQL Server Stored Procedures. Let’s start with SQL Server Management Studio (SSMS) 2012 and use Adventure Works 2012 database. Run the following script to see the results in SSMS: exec [dbo].[uspGetBillOfMaterials] 727, ‘2009-01-02’ First parameter: Start Product ID … Continue reading Dynamically Passing Parameters to a SQL Stored Procedure from PowerPivot Using VBA.

130

130

BI Insight

JANUARY 21, 2015

As you might know SSAS tabular models do not support composite keys so you always must have just one column to make a unique row through the whole table. This is such a pain especially when you are new to the tabular models and don’t have that much detail information about it. So when you … Continue reading How To Implement a Composite Key In SSAS Tabular Model.

Business Intelligence 130

Business Intelligence Business Intelligence 130

BI Insight

FEBRUARY 1, 2015

First of all I’d like to explain what a Role-playing dimension actually means. Then I’ll express the way you can implement it in a SSAS tabular model. When you link a dimension to a fact table several times for logically distinctive roles you’re using a role-playing dimension. The key points are: 1. You are linking … Continue reading Role-playing Dimension in SSAS Tabular Models.

Business Intelligence 130

Business Intelligence Business Intelligence 130

BI Insight

FEBRUARY 24, 2015

Batch processing is available in Analysis Services. Which means we can send multiple processing commands to the server in just a single SQL Server Job. With SSAS batch processing we can control which objects to be processed and in what order in a batch. As batch processing reduces the amount of time taken to commit changes it … Continue reading An Easy Way for SSAS Batch Processing.

130

130