Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
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.

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, 403 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

0 comments, 144 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

2 comments, 158 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

1 comments, 294 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

1 comments, 173 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, 185 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

0 comments, 235 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

1 comments, 323 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

3 comments, 1,810 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

0 comments, 346 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

0 comments, 121 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

0 comments, 134 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, 94 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

0 comments, 119 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, 104 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, 67 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

0 comments, 139 reads

Posted in Terry's Business Intelligence on 5 June 2013

SQL Logical Query Processing Order

This probably applies more to beginners or people who have been using SQL for a while but have been wondering why sometimes the query works in a certain way and sometimes it didn’t. Before I read the book for the exam 70-461, I found it relatively easy to just plonk…

Read more

0 comments, 85 reads

Posted in Terry's Business Intelligence on 3 June 2013

SQL and Relational Theory

Do you know the foundation of SQL? According to the book Querying Microsoft SQL Server 2012 Training Kit on the Microsoft label, T-SQL is a Microsoft flavour of the more standard ANSI and ISO version of SQL which is based on a branch of mathematics called Relational Model. Relational Model…

Read more

0 comments, 86 reads

Posted in Terry's Business Intelligence on 31 May 2013

The right terminology for the right audience

For those who has the privilege of being in the technology consulting industry for some time and started out in the technical side of things, remember those books or courses that teach about methodology or best practices? For example, for project management, you need to specify a requirements gathering stage,…

Read more

0 comments, 74 reads

Posted in Terry's Business Intelligence on 17 January 2013

Older posts