Temporary tables are used by every DB developer, but they're not likely to be too adventurous with their use, or exploit all their advantages. They can improve your code's performance and maintainability, but can be the source of grief to both developer and DBA if things go wrong and a process grinds away inexorably slowly. We asked Phil for advice, thinking that it would be a simple explanation.… Read more
One great result from PASS Summit, especially when we are close to a new SQL Server release, is to identify important technologies to study on the following year. PASS Summit 2018 was great, with sessions about many new technologies giving us very good guidance on where to focus our study for the new year. Let’s … Read more
With the rise of NoSQL databases that are exploiting aspects of SQL for querying, and are embracing full transactionality, is there a danger of the data-document model's hierarchical nature causing a fundamental conflict with relational theory? We asked our relational expert, Hugh Bin-Haad to expound a difficult area for database theorists.… Read more
Sometimes, in the quest for raw SQL performance, you are forced to sacrifice legibility and maintainability of your code, unless you then document your code lavishly. Phil Factor's SQL Speed Phreak challenge produced some memorable code, but can SQL features introduced since then help to produce code that performs as well and is also easy to understand? Aunty Kathi investigates.… Read more
As well as its multidimensional model, SQL Server Analysis Services (SSAS) now has a tabular model of database that either runs in-memory or in DirectQuery mode. The in-memory analytics engine allows the users of Excel or Power View to base reports on tabular model objects. Having shown how to handle date-based information using the Multi-dimensional model, Dennes now turns his attention on the in-memory tabular model.… Read more
A walk-through can be a great help to understanding SQL Server Analysis Services, especially when it solves the problem of providing an effective date dimension to a cube. Dennes takes Adventureworks, and demonstrates all the twists and turns of creating a cube that provides meaningful historical information.… Read more
You can develop a Power BI Dashboard that uses an R machine learning script as its data source and custom visuals. Here is a simple example that shows how to connect to data sources over the Internet, cleanse, transform and enrich the data through the use analytical datasets returned by the R script, design the dashboard and finally share it.… Read more
Although SQL is an obvious choice for retrieving the data for analysis, it strays outside its comfort zone when dealing with pivots and matrix manipulations. R includes a number of packages that can do these simply. By combining the two, you can prepare your data for analysis or visualisation in R more efficiently.… Read more
Whereas it is easy to provide inline documentation for a normal scripted
PowerShell cmdlet or function so as to provide comprehensive help at the command-line or IDE, the same isn't true of binary cmdlets written in C#. At last, there is an open-source utility to assist with this that is being actively
maintained and updated. At last, binary cmdlets need no longer be the poor cousins of scripted cmdlets in their documentation… Read more
SQL Server Data Tools (SSDT) provides, via the DacPac, interesting support for verifying not only those references within the database, but also those to other databases even if they are on other servers. Although it is adds an extra level of complexity to deployments, it can increase the probability that deployments will succeed without errors due to broken references or binding errors.… Read more
SignalR isn't just for web applications. It can also provide the basic real-time communications for a connected Windows Universal Application or even iOS and Android applications. Christos Matskas demonstrates how to get started with creating applications across a range of platforms that require real-time communication using SignalR… Read more
In application or database development, testing is too often given secondary importance. There are advantages to the whole delivery process if testers can get involved in the initial stages of planning and design. Testers have a role throughout the development lifecycle, as Doris Robinson explains… Read more
The Power Query Formula Language (PQFL) is a functional language that drives the Power BI transformations, and allows you to create mashup queries from scratch. Rob demonstrates how to use it in Power BI Desktop to extract data from its source, filter rows, specify the columns, clean the data, and create visualisations.… Read more
OpenStack holds a great deal of promise as a cloud platform built on open standards, and has support from the major players in cloud services. It has the potential for allowing organisations to set up their own private cloud services that are designed to inter-operate. Is it ready yet for companies that want the convenience of cloud solutions, but with more control, and without the large subscription fees? Robert Sheldon finds out.… Read more
What's the best way of providing self-service business intelligence (BI) to data that is held in on-premise SQL Server? Not, it seems, Power BI 2.0 the hosted cloud service, but Power BI 2.0 Desktop. If moving your database to Azure isn't an option, Power BI 2.0 desktop could still bring smiles to the faces of your BI hotshots.… Read more
In a bid to challenge Intel's dominance of high performance computing environments, IBM have introduced OpenPOWER, a flexible open-source framework based on their POWER processor architecture. Robert Sheldon looks at the potential of this new platform.… Read more
It seems a great idea to have your application on all the main mobile platforms as well as on the desktop. Nothing beats writing an app specially for each device, but then you've created a full-scale maintenance headache. Are technologies like PhoneGap/Cordova or Adobe Air, using a browser-based UI, the perfect compromise? If so, what frameworks do you use?… Read more
It is sometimes hard to keep up with Microsoft's direction in Business Intelligence. Over the years, we've been presented with so many ways of aggregating, processing, graphing, reporting and visualising data, that we felt it was time to take stock and present the Simple BI Timeline, and explain how BI has evolved over the years.… Read more
Although we like to think that our programming techniques are progressive and in tune with the bleeding edge of software development practices, too often they are directly influenced by restrictions faced in the post-war decades when computers
first became mainstream. As these restrictions no longer apply, is it time to relinquish such things as cursors,
'tibbling', storing display formats, using short names for symbols and primary keys?… Read more
One of the downsides of learning a new computer language is that transfer of training doesn't always work to your advantage. In fact, the habits you picked up in the past may now cause confusion. In this poster or wall-chart for long walls, Michael Sorens selects the thirty-six most common causes of confusion for anyone getting to grips with PowerShell. Forewarned is forearmed.… Read more
