I had a request coming in where I was asked to demo the capabilities of AlwaysOn feature using Availability Groups in SQL Server 2012 (a note on what is availability group deserves an explanation and a separate blog post, till that time you can read an introductory post which I had written here ) I will assume that you have configured a WSFC (Windows Server Failover Cluster). At time of writing this blog, I had a two node failover cluster at same site (single site) which I had created using Wind...
Read More
Case of multi-subnet Windows Failover Cluster What is Highly Available? A high-availability solution masks the effects of a hardware or software failure, outage, planned/unplanned maintenance and maintains the availability of applications so that the perceived downtime for users is minimized. For example: Database Mirroring What is Disaster Recovery? Disaster recovery (DR) is the process, policies and procedures that are related to preparing for recovery or continuation of technology infrastruct...
This is a relatively a short blog providing a general introduction to SSAS Tabular Model management using Windows Powershell. You can discover and learn more about Powershell from here , here and here . Since powershell is a scripting language, there are very useful scripts which already have been published which promise to do numerous tasks. It is also, relatively easy to write a new assembly (or Module) out of the box which can carry out the desired tasks. In this post you will see the new cmd...
In last blog which was about Microsoft SQL Server Analysis Services - Tabular Model Actions which can be found here , described how to create new Actions in the Tabular Model using BIDS Helper 2012 . Using BIDS Helper, you can not only define new actions on the tabular model but also administer any existing actions such as – View, Delete and Edit. There are two ways the developer can see the actual MDX that is being executed to see the underlying data which of course is generated at run ti...
With SQL Server 2012, SSAS has undergone a major change. There is BISM (If you are late into the party, read about BISM here and Tabular Model here ). If you ever had a chance to work with multidimensional models or had a chance to do a 1-1 comparison between SSAS multidimensional and SSAS tabular, it should not take much time to figure out that many COOL features are found missing in the tabular model. One such is ACTIONS, or you might want to know them as SSAS Actions (read here for more detai...
It has been rather long time since I had posted any content online. Perhaps I was lazy. Well this post is on Analysis Services (SSAS) and precisely here we will be discussing an issue which I observed today while randomly browsing various properties and options in the process of creating a tabular model in SSDT – SQL Server Data Tools. As one of the better practices followed, while designing a multi-dimensional OLAP is to ensure that there is a default measure already assigned. This will e...
SQL Server Integration Service – File Properties Task Hi. Let’s try to explore more Integration Services, again, and this time I am going to walk you through a custom task. The name of the task is “ File Properties Task ”. The download is free for the community and you can download the task executable from CodePlex. Often many are of the opinion that the Script Task is the ideal place where one can compose a programming logic to suit the project need, of course this is ju...
SQL Server Integration Services – Missing Package Configuration Option Hi, Let’s dig more into SQL Server Integration Service and this time it is the package deployment model. With the plethora of changes that the SSIS team has come up with in the new release of SQL Server 2012, the package deployment is the one which is significant and requires more understanding. For the first time when you open the SQL Server Business Intelligence Development Studio, you will notice many changes i...
SQL SERVER INTEGRATION SERVICES – FLAT FILE DATA LOAD, POTENTIAL ISSUES AND WORK AROUNDS Hi, ETL projects are usually classified on the basis of the complexity of designing the packages used for data loading, data transformation and finally storing the data in a manner which can be retrieved comfortable for end user reporting requirements. Majority of disparate source systems in an enterprise generate flat files (text files) which can be consumed for all data processing initiatives. Few ob...
SQL SERVER 2012 BI SEMANTIC MODEL – LIMIT AMOUNT OF DATA ON FIRST IMPORT Hello! This blog is on BI Semantic Model and I assume that you are aware what BI Semantic model is. I plan to take you through a brief introduction on data import or precisely selective data import. Please see below for the steps. This is fairly significant as this description showcases an important feature of “Editing an existing connection”. Create a new analysis services tabular project. This opens up a...
SQL Server Integration Services – Delay Validation Property Hello! ETL can be very time consuming and also complicated process. In order that the business does not stands at cross roads when any critical and time consuming ETL process counters a failure, the package is validated during design time and also during execution time. In this post let’s have a brief understanding of what Delay Validation is Integration Services is and how this property can be manipulated to favor the busin...
SQL Server 2012 Denali CTP3 SSIS – Sending files using FTP Task Hello! We at SQLServerGeeks.com are committed to spread the knowledge as we gain in the process of evaluating the newly released SQL Server 2012 CTP 3 release code named “Denali”. In the last post, we had a short walkthrough for receiving files from an SFTP server using SSIS Execute Process Task. You can find the published content here In this post, we shall send files or upload files to a remote server using FTP. ...
SQL Server 2012 Denali CTP3 SSIS – Project Connection Bug and Resolution Hello! We at SQLServerGeeks.com are committed to spread the knowledge as we gain in the process of evaluating the newly released SQL Server 2012 CTP 3 release code named “Denali”. If you have worked with SQL Server Integration Service in Denali CTP3, there is a potential bug in the code. This post will showcase a workaround to avoid this bug. I have decided to divide this post in two parts: Replicate the b...
SFTP with SSIS Execute Process Task Hello, SFTP, or secure FTP, is a program that uses SSH to transfer files. Unlike standard FTP, it encrypts both commands and data, preventing passwords and sensitive information from being transmitted in the clear over the network. It is functionally similar to FTP, but because it uses a different protocol, you can't use a standard FTP client to talk to an SFTP server, nor can you connect to an FTP server with a client that supports only SFTP ( Source: Knowled...
SQL Server 2012 Denali CTP3 Known Bug and Resolution Hello! We at SQLServerGeeks.com are committed to spread the knowledge as we gain in the process of evaluating the newly released SQL Server 2012 CTP 3 release code named “Denali”. If you have worked with tabular projects in Denali CTP3, there is a potential bug in the code. This post will showcase a workaround to avoid this bug. If you wish to see any feature like how to create a tabular structure and additional details, I suggest ...
BI Semantic Model and Reporting Part 2 Hello! We at SQLServerGeeks.com are committed to spread the knowledge as we gain in the process of evaluating the newly released SQL Server 2012 CTP 3 release code named “Denali”. In this post, let’s create couple of Roles using the Role Manager If you wish to see any feature like how to create a tabular structure and additional details, I suggest you follow this link here and also here . This should help you kick start on Tabular Models. ...
BI Semantic Model and Reporting Part 1 Hello! We at SQLServerGeeks.com are committed to spread the knowledge as we gain in the process of evaluating the newly released SQL Server 2012 CTP 3 release code named “Denali”. In this post, let’s create a simple no frills, no fancy excel report. Down the series, I shall introduce you to Perspectives, Roles and many associated features for an elegant Self Service BI experience. If you wish to see any feature like how to create a tabular...
DAX Query for BISM Tabular Model Part 2 Hello! We at SQLServerGeeks.com are committed to spread the knowledge as we gain in the process of evaluating the newly released SQL Server 2012 CTP 3 release code named “Denali”. This post comes as a supplementary addition to the earlier post which describes the DAX Query for BISM Let’s write some more DAX queries. Please note that I am using the AdventureWorksDWDenali as my database. And the outcome of the query would be Now, let’...
DAX Query Part 1 Hello! We at SQLServerGeeks.com are committed to spread the knowledge as we gain in the process of evaluating the newly released SQL Server 2012 CTP 3 release code named “Denali”. This post comes as a supplementary addition to the earlier post which describes the COUNT Function . In this post let us write out first DAX query. Fairly easy! Please note that I am using the AdventureWorksDWDenali as my database. EVALUATE() : Evaluate the context which is passed as an arg...
COUNT Function in BISM Hello! We at SQLServerGeeks.com are committed to spread the knowledge as we gain in the process of evaluating the newly released SQL Server 2012 CTP 3 release code named “Denali”. This post comes as a supplementary addition to the earlier post which describes the RELATED FUNCTION . In this post I plan to showcase examples for COUNT and related functions in DAX. We have 5 different COUNT related functions available for data manipulations. Let us have a small wal...
SSAS Tabular Project Part 2 Hello! We at SQLServerGeeks.com are committed to spread the knowledge as we gain in the process of evaluating the newly released SQL Server 2012 CTP 3 release code named “Denali”. This post comes as a supplementary addition to the earlier post which can be found here . In this post we shall have a walkthrough on how to create perspectives in a tabular project. A perspective can be literally resolved to a common term – VIEW as in the SQL terminology. ...
EOMONTH() and BOMONTH? Hello! We at SQLServerGeeks.com are committed to spread the knowledge as we gain in the process of evaluating the newly released SQL Server 2012 CTP 3 release code named “Denali”. This post comes as a supplementary addition to the earlier post which describes the RELATED FUNCTION . Consider the syntax as stated below using which one can calculate the last day of the month SQL syntax to calculate last day of month for SQL Server 2008R2 SELECT CONVERT(VARCHAR(10)...
DAX Functions for Querying BISM tabular models Hello! We at SQLServerGeeks.com are committed to spread the knowledge as we gain in the process of evaluating the newly released SQL Server 2012 CTP 3 release code named “Denali”. This post comes as a supplementary addition to the earlier post which describes how to configure SSAS Tabular Project . Consider the SQL as stated below: select DimProduct.EnglishProductName ,DimProductCategory.EnglishProductCategoryName ,DimProductSubcategory....
SSAS Tabular Project Part 1 Hello! We at SQLServerGeeks.com are committed to spread the knowledge as we gain in the process of evaluating the newly released SQL Server 2012 CTP 3 release code named “Denali”. This post comes as a supplementary addition to the earlier post which describes how to configure SSAS in Tabular Mode and create tabular model. In this post we shall have a walkthrough on how to create a tabular project which later can be used for data analysis. The first task af...
Configuring SQL Server 2012 Denali SSAS Engine in tabular mode Hello! We at SQLServerGeeks.com are committed to spread the knowledge as we gain in the process of evaluating the newly released SQL Server 2012 CTP 3 release code named “Denali”. This post comes as a supplementary addition to the earlier post which described one of the enhanced features of SSIS in Denali In this post we shall preview configuration of SSAS engine in tabular mode There already has been a lot of buzz about ...