-->
SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 

Terry's Business Intelligence

Terry is a BI Developer in London, focusing on DataWarehouse design and Reporting using SQL Server SSIS/SSAS/SSRS.

Dynamics 365 AX – Creating Data Entities for BYOD Azure Database including those without a natural key

We, BI professionals, sometimes take for granted that we can access the database of an ERP system quite easily, especially one that is made by Microsoft. However, the live Dynamics (365) AX database cannot be assessed directly currently. The diagram above shows that the Primary AX DB is copied into…

Read more

0 comments, 294 reads

Posted in Terry's Business Intelligence on 29 July 2017

When not to use Management Reporter

Management Reporter is easy to use to create Management Reports and it does not require a developer to do it. But there are times when Management Reporter is not the right tool for the job. Here are some reasons why you should not use Management Reporter: 1. The data you…

Read more

0 comments, 162 reads

Posted in Terry's Business Intelligence on 16 July 2017

Using Organization Hierarchies as Reporting Unit filters with Management Reporter

Well, hello. It has been more than three years since my last blog, since then I have been really busy at work and have a baby in the meantime. That being said, I wonder how much joy you guys have with playing with MR. I have been using Dynamics AX…

Read more

0 comments, 249 reads

Posted in Terry's Business Intelligence on 30 June 2017

Using PIVOT function without a CTE

There are some cases when you need to use a PIVOT function in SQL but you cannot use a CTE. One such case is when you create a named query in a SSAS cube. To do so, place the first part of the CTE query as a subquery of the…

Read more

0 comments, 1,017 reads

Posted in Terry's Business Intelligence on 4 June 2014

Microsoft Dynamics AX 2012 – Multiple Rows In Demand Forecast Table [FORECASTSALES] for each item

Why are there multiple rows for each item in the Demand Forecast Table in Microsoft Dynamics AX 2012? This is helpful to know for BI for cubes that aggregate the data in the [FORECASTSALES] table. Basically the overview line and each exploded line of the same item is stored in…

Read more

1 comments, 548 reads

Posted in Terry's Business Intelligence on 28 May 2014

Management Reporter Security

Are you confused by how user roles in AX 2012 are integrated with Management Reporter roles? Have problems adding or deleting users in Management Reporter? Get your answers here. In the last couple of weeks I have been asked by AX developers about Management Reporter Security. Mainly, they were stumped…

Read more

8 comments, 677 reads

Posted in Terry's Business Intelligence on 23 May 2014

Zap Business Intelligence Installation

This walkthrough helps you to install Zap Business Intelligence. Some of you may need help knowing where to download the files. This is where I can help you. Firstly, go to www.zapbi.com Go to the download section and download the installation file. Run setup: Setup the connection details: Setup IIS…

Read more

4 comments, 755 reads

Posted in Terry's Business Intelligence on 26 March 2014

Conditional Coloured Bar Charts in Excel with Upper and Lower Targets

I was just trying to gauge my BI knowledge level the other day and decided to chart it for visibility. It should be able to tell me to focus on certain topics if 1) I know 20% or less of the topic and 2) if it reaches 100% – which…

Read more

3 comments, 450 reads

Posted in Terry's Business Intelligence on 26 March 2014

What is Microsoft Management Reporter for Dynamics AX?

It is a collaborative and interactive reporting solution from Microsoft for finance data. View more here: Management Reporter 2012 Overview How is it different from standard SSRS? It is designed to be collaborative. How often do you need discuss a report with other people? This software actually allows you to…

Read more

0 comments, 612 reads

Posted in Terry's Business Intelligence on 25 March 2014

Introducing Microsoft Dynamics AX 2012 with SSRS Reports

Hi all, I’ve started a new job with eBECS which is a Microsoft Gold Partner in the ERP Business. Since I have been there, I have been learning how Dynamics AX is integrated with Microsoft BI Stack since 1) they are from the same company and 2) I need to…

Read more

3 comments, 642 reads

Posted in Terry's Business Intelligence on 25 March 2014

First take on PowerPivot

What is PowerPivot for Excel? PowerPivot brings the power of Data Modelling into your PC. Traditionally, the DataWarehouse is built on a database server (SQL) and a data model is built on top of it (SSAS). Then, a front end is used to present the data to users. Now, data…

Read more

2 comments, 1,353 reads

Posted in Terry's Business Intelligence on 30 October 2013

Understanding the PIVOT function

The PIVOT function is very useful but not easily understood at first. I thank Itzik Ben-Gan, Dejan Sarka and Ron Talmage and their study guide Exam 70-461: Querying Microsoft SQL Server 2012 for the brilliant explanation. Here’s their example replicated here. Remember there are 3 columns used in PIVOT. The…

Read more

6 comments, 2,770 reads

Posted in Terry's Business Intelligence on 25 October 2013

Get list of dates using recursive Common Table Expression (CTE)

Many times we need a list of consecutive dates or time at runtime mainly for a recursive ETL process. This code provides a list almost instantaneously with only the start and end dates being the only input. The output is in Date format but is easily extended to a DateTime…

Read more

1 comments, 911 reads

Posted in Terry's Business Intelligence on 23 October 2013

Top 10 Tips when synchronising your database and Mailchimp

Okay, sooner or later someone is going to need to synchronise all those people who registered on your company website with an email service provider to do a bit of marketing. In my case, the latest email provider to come my way is MailChimp. MailChimp is a very popular email…

Read more

2 comments, 475 reads

Posted in Terry's Business Intelligence on 17 October 2013

MailChimp Database Synchronisation Process

After giving you the Top 10 Tips when synchronising your database with MailChimp, I thought why not write up the process as well to help you along. Step 1: Download MailChimp Lists Step 2: Create the Master Comparison Table Step 3: Know the email update process Step 4: Run the…

Read more

1 comments, 930 reads

Posted in Terry's Business Intelligence on 17 October 2013

Top 11 Qualities of a DataWarehouse Implementer

Top 11th Quality: Curious to explore different designs There are many ways to achieve the same thing. Before actually getting your hands dirty, think and do some high level designs, then choose the best one. They need not be perfect, and the beauty of it is the more experience you…

Read more

0 comments, 403 reads

Posted in Terry's Business Intelligence on 14 October 2013

Building a simple forecast solution on SQL Server

Pricing Forecast is a marketing function that requires a lot of data crunching. Normally there are some factors that the Marketing team wants to adjust to find out just the optimum pricing for their products. Accordingly, the forecast solution needs to be fairly flexible but still be powerful enough to…

Read more

8 comments, 943 reads

Posted in Terry's Business Intelligence on 30 August 2013

Normalisation

The purpose of normalisation is to remove redundancy and prevent conflict. 1st Normal Form (1NF) – Every field can have only one value. No repeating value and no repeating groups. From wikipedia: A relation is in first normal form if the domain of each attribute contains only atomic values, and…

Read more

0 comments, 394 reads

Posted in Terry's Business Intelligence on 7 August 2013

Client Pressures and Code Quality

In my experience, code quality always trumps urgency because when code has been released that has questionable quality, it is inevitable that bugs appear and the project takes longer than it should. In my case, because the code belongs to a 3rd Party vendor, this frustrates the developer, business users…

Read more

0 comments, 340 reads

Posted in Terry's Business Intelligence on 9 June 2013

SQL Query Result Order

Every time an SQL query is run without the ORDER BY clause, the sort order of the results returned is not guaranteed. SQL Server decides which is the best order to return the query. This is based on factors like whether indexes are used or data fragmentation on the physical…

Read more

1 comments, 515 reads

Posted in Terry's Business Intelligence on 5 June 2013

Older posts