In the previous article, we’ve created two tables, and now we’re ready to use the SQL INSERT INTO TABLE command and populate these tables with data. In order to do so, we’ll prepare statements in Excel and then paste these statements into SQL Server and execute them. We’ll also check the contents of both tables before and after these commands using the SELECT statement. So, let’s start.Read more »
In this article, I’ll share some of the tricks and tips that I had to employ in order to successfully replicate Excel’ XY Scatter Report Chart.Read more »
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 »
There are many ways to import data from an Excel file to a SQL Server database using:
- SQL Server Integration Services (SSIS)
- the OPENROWSET and OPENDATASOURCE functions
- SQL Server linked servers
- the SQL Server Import and Export Wizard
In this article, steps for importing data from an Excel file to a SQL Server database will be explained using the SQL Server Import and Export Wizard including some of problems that can occur during the processes.Read more »
Linked servers allow SQL Server access to data from a remote data source. A remote data source can be another SQL Server instance or other data sources such MySQL, Access databases, Oracle, Excel workbooks, text files etc.Read more »
Prerequisites for using query results from any Analysis Services model database in Excel through Power Pivot add-in are SQL Server 2014 or higher with installed Analysis Services Tabular instance (Multidimensional is installed by default), deployed Multidimensional or Tabular model database (for data source) and Power Pivot add-in for Excel 2010 or higher (Power Pivot is native add-in in Excel 2016).
In this article, the custom SSAS Tabular model database will be used (“AW Tabular”) as an external data source.Read more »
A few weeks back I had been working on an interesting proof of concept for a client within the food / grocery industry. The objectives were to be able to provide the client with information on sales patterns, seasonal trends and location profitability. The client was an accountant and was therefore comfortable utilizing spreadsheets. This said, I felt that this was a super opportunity to build our proof of concept utilizing a SQL Server Tabular Solution and by exploiting the capabilities of Excel and Power Reporting for the front end.Read more »
Whilst researching for the article Report filtering: Excel slicers vs SQL Server Reporting Services (SSRS) parameters, I discovered a new Excel Slicer feature in Microsoft Office 2016 that allows users to select/deselect multiple items without having to hold down the control (Ctrl) keyboard key. For some people, this may not be a sufficient reason to upgrade to Office 2016 but for developers of business intelligence (BI) solutions, this new feature further enhances the experience of consumers of BI solutions.Read more »
This article explores the report filtering using Excel Slicer and SQL Server Reporting Services.Read more »
There are great Microsoft tools to access to SQL Server in Azure. However, as a Database Developers and Administrators we need to help the end user to access to the information using a familiar and intuitive interface.
Most of the end users have a good knowledge in MS Excel. That is why it is recommended to connect our SQL Azure database to Excel. That way, the end user will be able to easily create reports, charts and generate useful information.
In this chapter, we will show how to connect to SQL Azure using Microsoft Excel.Read more »
IntroductionA year or so ago, I was working on a project that revolved around daily data loads (from various asset management groups within an enterprise) into the main SQL Server data repository. Each group completed and published its own daily figures within their own Excel Work Books. These Excel workbooks were then placed in a common directory and then loaded into the Corporate SQL Server database. Let us have a look at how this may be achieved. In short, we are going to create one package that will process all the spreadsheets within the given directory. Read more »
One of a database designers’ worst nightmares is having to design a database for business analysts and data stewards whom insist upon creating their own reports, using Excel as a GUI. The reason that I mention this is that user created reports often open up “Pandora’s box”; with many of these folks creating their own ‘miss-information’ due to a lack of understanding of the underlying data. A few weeks back I had the ’fortune’ of working on such a project, which prompted an ‘ah-ha’ moment. I decided to design the backend SQL Server database using the Business Intelligence Semantic Model (BISM) and to employ the super set of tools provided by Microsoft Power BI, with Excel as a GUI. The end results were wildly accepted by the user community and once you see how easy this is to apply, you will be ‘chomping on the bit’ to employ the same techniques on your own user driven projects.Read more »