How to retrieve the number of rows in each table?

Found a nice way to retrieve a list of tables in a certain database with their actual number of rows (instead of just looking at the meta-data that’s stored in indexes).
Source: http://stackoverflow.com/questions/1443704/query-to-list-number-of-records-in-each-table-in-a-database

Replaced the #Temp-table with a @Table-var, but it’s pretty straight-forward:

DECLARE @TableCounts TABLE
(
TableName VARCHAR(500),
NrOfRows INT
)

INSERT @TableCounts
EXEC sp_msForEachTable
'SELECT PARSENAME(''?'', 1),
COUNT(*) FROM ? WITH (NOLOCK)'

SELECT TableName, NrOfRows
FROM @TableCounts
ORDER BY TableName








Microsoft® SQL Server® 2014 Feature Pack

Microsoft released the ‘Microsoft® SQL Server® 2014 Feature Pack’:

The Microsoft SQL Server 2014 Feature Pack is a collection of stand-alone packages which provide additional value for Microsoft SQL Server. It includes the latest versions of:
– Tool and components for Microsoft® SQL Server 2014.
– Add-on providers for Microsoft SQL Server 2014

Download it here:
Microsoft® SQL Server® 2014 Feature Pack

Looking for the 2012 version?:
Microsoft® SQL Server® 2012 Feature Pack

The OData Source component for SSIS 2014 is included in the feature pack.
For SSIS 2012 see below:

Microsoft OData Source for SQL Server 2012 provides the capability of consuming OData services to SQL Server 2012 Integration Services (SSIS). Users can use it in an SSIS package to extract data from an OData service and then perform further ETL processing.

The Open Data protocol (OData) lets you access a data source, such as a database, by browsing to a specially constructed URL. This allows for a simplified approach for connecting to and working with data sources that are hosted within an organization.

There are many OData sources available in enterprise and in cloud:
– SharePoint Foundation 2010
– SharePoint Server 2010
– SQL Azure
– Windows Azure Table Storage
Windows Azure Marketplace
– SQL Server Reporting Services
Microsoft Dynamics CRM 2011
– Windows Live

Microsoft® OData Source for Microsoft SQL Server® 2012



Join us at LinkedIn!
Don’t forget to join the SQL Server 2014 group on LinkedIn and stay updated!:
linkedin-sql-server-2014-group








SSAS Ragged Hierarchy (BottomUp approach)

Hi there reader!
There are a couple of methods on how to populate so called ‘ragged hierarchies’ in SSAS, a few tricks:

Naturalizing a Parent-Child Hierarchy:
http://martinmason.wordpress.com/2012/02/26/the-ssas-financial-cubepart-1ragged-hierarchies/

TopDown and hide middle levels:
http://asmdx.blogspot.nl/2008/04/parent-child-hierarchy-to-level-base.html

BottomUp approach by Chris Webb (Ragged Hierarchies, HideMemberIf and MDX Compatibility):
http://cwebbbi.wordpress.com/2009/11/11/ragged-hierarchies-hidememberif-and-mdx-compatibility/

I really liked the BottomUp approach that Chris Webb proposed. This is how a TopDown-hierarchy looks like:

SSAS-Ragged-Hierarchy-TopDown-Approach

And here’s the BottomUp-hierarchy we’re going to be using for this demonstration:

SSAS-Ragged-Hierarchy-BottomUp-Approach

First things first, here’s the cube’s structure:

SSAS-Ragged-Hierarchy-Cube

Define the relationships (nothing fancy here):
SSAS-Ragged-Hierarchy-Cube-Structure-Relationships

Here’s where the magic happens! Set the HideMemberIf-property to ‘OnlyChildWithParentName‘, so basically hide the child when it has the same name as its parent.

SSAS-Ragged-Hierarchy-Cube-Structure

And here’s the result of that hard work:

Dimension-Browse-BottomUp

Excel-Browse-BottomUp

Important note
This method works for Excel without setting the “MDX Compatibility = 2” requirement, but in SSRS and your MDX query it will only return those records which have no hidden levels. Strange issue, I suggest you make another seperate hierarchy for reporting that has the HideMemberIf property set to ‘Never’ for all levels.








MDX Cheat Sheet

I’ve Googled for MDX Cheat Sheets, found a couple, but never exactly what I was looking for.
So I made my own, hope this one explains things a lot easier for you (and also as a reference for myself) 😉

Hierarchy:
For the demo, I’m using a Year(2014)/Month(201401) hierarchy in my SSAS cube which looks like:

Dim Period Hierarchy

Code:

MDX Cheat Sheet

Results:

MDX Cheat Sheet results

Here’s a more graphical explanation on how things work:
SSAS Dimension hierarchies levels

Download it!
SSAS MDX Cheat Sheet


Here’s another MDX Cheat Sheet I posted after, all about Calculated Measures:
https://clinthuijbers.wordpress.com/2014/08/15/mdx-cheat-sheet-calculated-measures/








Magic Quadrant for Data Warehouse Database Management Systems

Thank Gartner for the info.

Entering 2014, the hype around replacing the data warehouse gives way to the more sensible strategy of augmenting it. New competitors have arisen, leveraging big data and cloud, while traditional vendors have invested — which will force improved execution from new technology companies.

For this Magic Quadrant, we define a DBMS as a complete software system that supports and manages a database or databases in some form of storage medium (which can include hard-disk drives, flash memory, and solid-state drives or even RAM). Data warehouse DBMSs are systems that can perform relational data processing and can extended to support new structures and data types, such as XML, text, documents, and access to externally managed file systems. They must support data availability to independent front-end application software, include mechanisms to isolate workload requirements (see Note 2) and control various parameters of end-user access within managed instances of the data.

A data warehouse is a solution architecture that may consist of many different technologies in combination (see Note 3). At the core, however, any vendor offering or combination of offerings must exhibit the capability of providing access to the files or tables under management by open access tools. A data warehouse is simply a warehouse of data, not a specific class or type of technology.

In 2014, this Magic Quadrant introduces non-relational data management systems for the first time. No specific rating advantage is given regarding the type of data store used (for example, DBMS, Hadoop Distributed File System [HDFS]; relational, key-value, document; row, column and so on). All vendors are expected to provide multiple solutions (although one approach is adequate for inclusion), each demonstrating maturity and customer adoption. Also, cloud solutions (such as platform as a service) are considered viable alternatives to on-premises warehouses.

A data warehouse DBMS is now expected to coordinate data virtualization strategies, and distributed file and/or processing approaches, to address changes in data management and access requirements.

Magic Quadrant for Data Warehouse Database Management Systems 2014

Microsoft

Microsoft (www.microsoft.com) markets SQL Server 2012 (Service Pack 1 has been available since November 2012), a reference architecture and the parallel data warehouse appliance. Microsoft does not report customer or license counts. Gartner estimates Microsoft’s relational DBMS revenue grew 13.6% during 2013 — faster than the overall market.

Strengths
– Microsoft offers appliances, reference architectures including a variety of hardware, prebuilt offerings built to customer selections then delivered ready to run, software licensing and managed services data warehouses.
– Customers report a low count of software issues, above-average customer experience and obvious interoperability with Excel (and Office).They also like the easy-to-understand licensing and pricing — adding to execution.
– Customers are predominantly on the current release, and almost 60% of customers report it is their data warehouse standard. Microsoft has taken steps in pursuing the LDW with HDInsight (HDP for Windows), PolyBase and Microsoft Cloud (Windows Azure Infrastructure Services can be used to deploy a data warehouse).

Cautions
– Microsoft is catching up with the other leaders, but a fast-follower market demand still drives the Microsoft road map. However, Microsoft has demonstrated its willingness to be aggressive in certain areas (such as unstructured data via SharePoint search and Azure).
– Organizations report large volumes of data but, in general, Microsoft data warehouses have a small number of users — better examples of scaling warehouses are needed. Customers want easier access to usable metadata for heterogeneous environments.
– Reference customers still report a significant cost advantage, but inquiries indicate that even small price increases do matter and Microsoft needs to maintain its price differentiation from other vendors.

More info.