Monthly Archives: July 2013

SQL Server Enterprise Management Data Warehouse (eMDW)

eMDW

Few years ago, I was managing 100’s of SQL servers with 100’s to 1000’s of databases in a very busy environment. My challenge was how to manage all SQL Server instances, databases and have a complete inventory of my environment. SQL Server Management Studio is a tool will allow me to view data per instance, so I have to connect to each instance and browse the properties of each instance but not from a central view. I needed a tool that I can see total number of Servers with summary that I can drill down by environment by business unit down to an object level. I want to look up a user permission across SQL Servers and level of access, I need to know how many databases, objects and users are by environment i.e.  Production, test, dev and QA, how much total storage my SQL Server are using with drill down capability and break it down by file type and display growth trending.

Long story short, I needed a centralized management view of my SQL server environments that I can have a complete inventory that can answer my previous questions.

I began building a centralized reporting system by creating a master repository database on a SQL Server instance. The repository would store data about every SQL Server instance, database, object, and user in an enterprise database environment.

I developed a custom an SSIS package that collects a plethora of SQL data with details on SQL server Instances and databases, and store the data in a central database.

After data is in the repository, you can use Reporting Services to view the data. I developed different dashboards and reports in SQL Server Reporting Services to view instance, databases, objects and users data. This solution helps me monitor the status and inventory of 100’s to 1000’s of databases and assess how big my environment is and have access to enterprise data from centralized reports.

You can download the entire solution at: GitHub

SSAS Tabular using HiveODBC connection

1_Hive-to-BISM

In my previous blog post “Import Hadoop Data into BI Semantic Model Tabular”, I mentioned that you need a SQL Server Linked Server connection to connect SSAS Tabular to a Hive table in Hadoop. That is the case with SSAS Multidimensional instance but in a Tabular instance you can connect directly to Hive table. Thanks to Lara Rubbelke (Technical Architect at Microsoft) who brought to my attention that we can connect a SSAS Tabular project to Hive via HiveODBC connection directly.

After few testing scenarios, we were able to get it to work. Here is how? Running SQL Server Analysis Services 2012 Tabular mode 64-bit on a 64-bit operating system, after creating an SSAS Tabular project using SQL Server Data Tools (SSDT), you have to create both a 32-bit and 64-bit System DSN for this to work. When you create the SSAS import task, you are doing it from SQL Server Data Tools (SSDT), which is a 32-bit process, so it can only see 32-bit DSNs. On a 64-bit operating system, the DSNs are 64-bit by default, so they don’t show up. User DSNs are automatically both 32 and 64bit, so they don’t have this problem. This is a quirk of ODBC and isn’t specific to this driver.

To create a 32-bit DSN, run c:\windows\syswow64\odbcad32.exe and create the System DSN there, and also create an identical one (same name and everything) in the regular 64-bit ODBC Data Source Administrator that is launched from the control panel. When you create the import task from SSDT, it will pick the 32-bit one and then at runtime when the import happens, it will look for the 64-bit one and use it instead. As long as they are identical this is fine.

 

Project “ChâteauKebob”

Authors: Ayad Shammout & Denny Lee

Kebob

It may sound like a rather odd name for an End-to-End Auditing Compliance project – and the roots admittedly enough are based on the authors’ prediliction toward great food in the city of Montréal – but there actually is an analogous association!

Château means manor house or palace and kebob refers to meat that is cooked over or next to flames; large or small cuts of meat, or even ground meat, it may be served on plates, or in sandwiches (mouth watering yet).  Château Kebob means house of kebob with different meat.

So why did we call our project “ChâteauKebob”? In this project Denny Lee and I used a château of different technologies in one framework or house with Kebob of mixed of data small or big data, structured and unstructured, and served on plates or in sandwiches, … sorry I meant served with multiple BI tools for reporting and analysis.

The purpose of this project is to provide a set of tools and jumpstart with scripts to implement the project involving HDInsight (Hadoop), SQL Server 2012, SQL Server Analysis Services 2012, Excel 2013 PowerPivot and Power View. You can review an overview of the project at slideshare .

The SDK is available at GitHub to download the entire project.