SQL SERVER – What are Actions in SSAS and How to Make a Reporting Action

Actions are used for customized browsing and drilling of data for the end-user. It’s an event that a user can raise while accessing the cube data. They are used in cube browsers like excel and are triggered when a user in a client tool clicks on a particular member, level, dimension, cells or may be the cube itself.  For example a user might be able to see a reporting services report, open a web page or drill through to detailed information related to the cube data.

Analysis server supports 3 types of actions :-

  1. Report
  2. Drill-through
  3. Standard Actions

In this blog post, I will explain the Reporting  action. The objective of this action is to return a report with details of the product where the sales amount is greater than 1000 in cube browser analysis. You need to create a basic cube first with the facts and dimensions you want in the analysis. Following are the steps to create reporting action.

  1. Go to SQL server data tools and open the analysis services project. Navigate to actions and click on new reporting action.

2.) Specify the name of the action and choose target type as attribute members since we have to create the action on members for a attribute.

3.) Specify the Target object of your report action. Target object would be the dimension or attribute on which you want the report to appear. In our case it is product name.

4.) Next you have to define the condition on which you want the report link to appear. However, this is an optional feature. In this example we are specifying a condition, which will check if the sales amount is greater than 10,000. So, that the link appears only for those products where the defined condition is met.

5.) Next you have to specify the server name on which the report is present, report path  and the report format in which you want the report to appear.

6.) Additionally you can specify the parameters. As with conditional expression, the parameters should be a valid MDX expression. The parameter name should be same as the one defined in the report.

7.) Deploy your solution after you are done with specifying parameters and go to the cube browser.

8.) Click on the analyze in excel button, this will open your cube in excel

9.) Make an analysis which shows product names and their sales amount.

10.) Right click on a product where sales amount is greater than 10000 you will see the reporting action link. Click on that and you will be taken to your reporting services report.

11.) Clicking on the link will take you to the URL of the report. I created this report using report project wizard in SQL server data tools.

So, this is how we can launch reports from a cube browser. Similarly you can open web pages, run applications and a number of  other tasks. Koenig Solutions offers SSAS training which contains all Analysis Services including Reporting in great detail.

In my next blog post I will talk about drill-through actions.

Author: Namita Sharma, Senior Corporate Trainer at Koenig Solutions.

Reference: Pinal Dave (http://blog.sqlauthority.com)

SQL SERVER – What is SSAS Tabular Data model and Why to use it – Part 2

In my last article, I talked about the basics of tabular data model and why use it. Then I demonstrated step by step creation of a basic tabular model project. In this part I’m going to throw some light on how to create measures and analyses in excel.

If you look at the tabular project closely, you will notice that we have not defined any measure yet. So, in the first step we will define the measure first.  Open the solution and select the column you want to define as a measure. Then, click on the summation icon on the toolbar. You will see the aggregated results at the bottom of that column. You have also other choices as well like average, min, max, count and distinct count.

After creating the required measures, we need to analyze our data in excel. To do this, click on the excel icon in the upper left corner of the toolbar.

This will open your analysis in excel. Notice the pivot table field list here. I have highlighted the measures that we created in the earlier step. Now, we can use these measures in our analysis

Now, we have to put the required fields in their respective places as column labels, row labels, Values and Report filter for analysis. See below snapshot for details, it shows region wise sales on a yearly basis

You can even apply filters on the above analysis by placing the slicer field in report filter. In our example, we will take an English product name as a filter.

You can use the filter as depicted in the below snapshot.

Optionally, you can also use the slider to filter data more interactively.

Further to improve our analysis, we can insert pivot charts

That’s all for this time, in my next post I’m going to show in detail about how to create hierarchies, perspectives, KPI’s  and many more features.

Author: Namita Sharma, Senior Corporate Trainer at Koenig Solutions.

Reference: Pinal Dave (http://blog.SQLAuthority.com)

SQL SERVER – What is SSAS Tabular Data Model and Why to Use it

Analysis services in SQL Server 2012 can be either deployed in multi-dimensional mode or tabular mode or power pivot for SharePoint as well. Tabular mode is a new enhancement in SQL Server 2012 analysis service database structure. It is a columnar database capable of incredible performance and compression ratio. At this point, there is a lot of confusion in users on why to use the tabular model when we already have multidimensional model.So, let’s discuss these points first before creating a tabular model project.

Why to Use:

1)      Tabular model is quite easy to understand and implement, and is particularly made for empowering  information workers.

2)      The tabular model uses DAX for scripting which is similar to using excel formulas and is faster to learn.

3)      It uses Vertipaq (x-velocity) engine for in memory column storage, which gives great performance  and direct query mode for retrieving data and aggregates directly from the database which is beneficial for querying data in real time.

4)      Powerpivot models can be easily upgraded to tabular models, thus providing a path for business users and IT professionals to author models in familiar tools like MS Excel.

5)      All the client applications that support  multi-dimensional mode will also support  tabular and work natively with it. This is because tabular uses the same data provider that understands both MDX and DAX queries.

When Not to Use :

1)      When the source is based on dimensional modeling and has complex relationships with very large volume of data.

2)      No support for writing back or parent, child hierarchy

3)      When you want to do complex calculations, scoping and named sets

Getting started with the Tabular Model Project :-

Click on the new project in SQL server data tools, there will be 5 templates options under Business Intelligence à Analysis Services.  For this example, I‘m creating an Analysis service tabular project.

As soon as you click ok, you will be prompted to specify an instance of analysis services installed in tabular mode. This is the instance which will be used for creating a workspace database for the project.

Once you click ok, you will notice that, a model. bin file is generated. To  import data in the tabular model, go to model  in the toolbar menu and click Import data from source.

As you can see, you can import data from a variety of other sources. For the purpose of this example we will choose Microsoft SQL Server and click on Next >

Here you will be asked to enter credentials to connect from the data source. After entering the credentials, click Next >

Here you have to choose whether you want to import data from tables and views or either write a customized query  to specify the import data. For the purpose of this example, I’ve taken  the first option.

As I choose the first option we will get a list of tables and views from the database we selected in the earlier steps. Here you can select the tables and views that you want to import or optionally choose one table and click on the “selected related tables” button to include all related tables based on relationships. Click on Finish button

After you click Finish button import process will start, you can see the status of the import in the below screen

After the import process is finished, you can see the project, either in grid or diagram view. By default, you will see the grid view of all the tables you selected.

Click on the bottom right corner to see the same in the diagram view

Right click any table to see various options  that will enable you to create relationships, hierarchy or hide table from the client tool.

To summarize it all, we have created a tabular mode project, and imported data from a data source into this project.

Hope this post was helpful to clear all your doubts. To understand these concepts in greater detail, you can enroll for a Microsoft SQL Server 2012 course from a recognized IT training organization such as Koenig Solutions, which offers more than 1000 IT courses and certifications.

This blog post is authored by Namita Sharma, who is a senior corporate trainer at Koenig Solutions.

Reference: Pinal Dave (http://blog.SQLAuthority.com)

SQL SERVER – Learning SSAS (SQL Server Analysis Services) Online in 6 Hours – Top Down Designing and Bottom Up Designing

Those who are following me on Twitter and Facebook know that recently I am reenforcing my own concept for SQL Server Analysis Services (SSAS). Like many of us, I worked with Analysis Services in early years. In an earlier job, I got many projects for relational database performance tuning and over time, I lost touch with SSAS. This does not mean that I forgot all of the concepts but the ‘real’ hands-on experience was gathering dust. Looking back at the last five years, I realized that I have deep experience with relational performance tuning but there are a few new things which I have yet to explore and learn.

My Learning

One weekend I decided to learn SQL Server Analysis Services. I woke up early in the morning at 6 AM and by 7 AM I was sitting with coffee at my dual screen monitor computer. I had a free subscription to Pluralsight so on one screen I turned on their Analysis Services Fundamentals course. This course is well organized and I attended first six modules by 1 PM. After having a quick lunch with my family, I reviewed all the tasks and lessons given in the course. As it was the weekend and I had properly digested all the concepts, I decided to continue the remaining four modules the next day. The next day, the same routine: I followed and finished all the remaining modules along with the exercises. At the end of six hours of video learning and practicing for more than 8 hours, I felt very comfortable with the subject. I re-discovered my knowledge of SSAS which I used to practice a few years ago, before I opted for performance tuning.

Course Details

You can read the official details of the course here yourself. Here is my view on this course. This course is very well structured. I have found many books and attended SSAS training where the training starts with Bottom Up Designing methodology. I have never had any course where the instructor started from Top Down Designing methodology. This course really got my attention as it covers the all the core concepts in a very reasonable time with crisp and clear instructions. After that it jumps to very easy to understand and follow-along demos where one can practice their skills. I am personally a big fan of the AdventureWorks database as that makes our life much easier and everybody has access to that database for free. Thanks to the instructor, Stacia, for using the same database for every single demonstration and exercise.

I have seen many demonstrations where someone takes an already-built cube and imports that into the system and starts explaining various concepts of the cube. I personally do the same when I have to demonstrate some cool new feature of the SSAS in a one hour presentation. However for in-depth learning, the whole game is different and I prefer to learn from the beginning. I prefer to learn how dimensions are built, how measures are categorized and how cubes are built from nothing. One always learns properly when the lesson is explained in structured manner. In this course Stacia has done the same thing – starting from no elements on screen and very quickly building all the modules from scratch. A perfect experience for beginners and experts.

Course Content

This course covers various topics and you can read all the documentations on the official course page. However, as an outline, here are the module names. I am also attaching the image which demonstrates that I have attended every single module of the course. If you stop the course any place during the lesson, when you come back the course content page tracks your progress; this is very convenient.

  • Introduction to Analysis Services
  • Dimensional Model Development
  • Dimension Development I
  • Dimension Development 2
  • Cube Development I
  • Cube Development 2
  • Calculations 1
  • Calculations 2
  • Database Processing
  • Security

Future Expectations

The title of the course says this is an Introduction to Analysis Services. However, I thought this was quite a comprehensive course. I am assuming that there will be another version of this course. Stacia  also hinted couple of times during the course what will be in the advanced course. Well, what I expect in the advanced course is that the quality and presentation stays the same.

One Last Thing

Well, when I watched this course – I only had an annual subscription. Since then I have upgraded to the Pluralsight Annual Plus subscription, which gives me complete access to all the files, exercises and downloadable videos. I just wish I had changed before I started this course as it changes the experience.

Win A 30 Day Subscription

I have one 30 day Pluralsight OnDemand learning subscription to give away. To win you just need to leave a comment to this blog post suggesting which course you want to take and why. One person will be randomly picked as a winner for this 30 day subscription.

Reference: Pinal Dave (http://blog.SQLAuthority.com