Windows 2012 Hosting - MVC 6 and SQL 2014 BLOG

Tutorial and Articles about Windows Hosting, SQL Hosting, MVC Hosting, and Silverlight Hosting

SQL 2014 Hosting - ASPHostPortal :: Make Your SSAS Works Like a Private Jet!

clock December 10, 2015 19:54 by author Jervis

SQL Server Analysis Services (SSAS) Tabular is a popular choice as an analytical engine for many customers. With its state-of-the-art compression algorithms, multi-threaded query processor and in-memory capabilities, SSAS Tabular can provide super quick access to data by reporting client applications. However, as a consultant, I have been called by many clients to resolve slow query performance when accessing data from SSAS Tabular models. My experiences have taught me most, if not all, of the performance issues can be resolved by taking care of the following five subject areas. 

Estimate Current Size and Growth Carefully

Tabular models compress data really well and on an average, you can expect to see 10x the compression rates (though it can be much more or less depending on the cardinality of your data). However, when you are estimating the size of your model as well as future growth, a rough figure like this is not going to be optimal. If you already have data sitting in a data warehouse, import a subset of that data — say a month — to find the model size for that and then extrapolate the value based on the required number of rows / years as well as the number of columns. If not, try to at least get a subset of the data from the source to find the model size. There are tools like BISM Memory Report and Vertipaq Analyzer that can further help in this process.

It is also important to record the number of users who will be accessing the system currently as well as the estimated growth for the number of users.

Select or Upgrade Hardware Appropriately

Everyone knows SSAS Tabular is a memory intensive application, and one major issue I have seen is only the RAM is considered when hardware selections are made. However, just focusing on the RAM is not enough and there are a lot of other variables. Suppose all the other variables are constant and there is an unlimited budget, these are the recommendations:

CPU Speed – The faster, the better, will help in computing results faster especially when there is a bottleneck on the single-threaded formula engine.

CPU Cores – In theory, the more the better as it helps in managing concurrent user loads. However, in reality, a rise in the number of cores usually corresponds to a decrease in the CPU speed due to management overload. So a balanced approach has to be taken when determining the CPU Cores and Speed. Also, licensing cost increases with the number of cores for many software.

CPU Sockets – The lesser, the better as SSAS Tabular is not NUMA aware till SQL 2014. However, this is expected to change in SQL 2016 where some NUMA optimization has been made. For large tabular models, it might be a challenge to go single socket as the amount of RAM that can be supported on a system will depend on the CPU sockets.

CPU Cache – The more, the better. Retrieving data from CPU caches are 10-100x faster than retrieving data from RAM.

CPU Architecture – The newer, the better due to the hardware performance optimizations. For eg, Intel Xeon processors with Haswell architecture is always going to be faster than Sandy architecture keeping all other variables constant.

Amount of RAM – Should have at least 2.5x the model size, if the model is going to be processed on the same server. The amount of RAM can be lesser in cases of certain scale out architectures where the model is processed in a separate server.

RAM Speed – The faster, the better (yes, RAMs have speed too!) This is very important for a memory-bound application like Tabular and should always go for the faster speeds, if budget allows.

Storage – Not important at all as it does not have any effect on query performance. However, if budget allows, it might not be a bad idea to get faster storage like SSDs, as that will help in maintenance related activities like backup, storage or even getting the tabular model online faster when the service is restarted. Apart from this, there are other factors also like network latency, server architecture (scale out), etc that have to be considered, but depending on the budget and specific customer requirements, a balanced approach will have to be made.

Design the Data Model Properly

Tabular is really good at performance and in the case of small models, is extremely forgiving in terms of bad design. However, when the amount of data grows, performance problems begin to show up. In theory, you will get the best performance in SSAS tabular if the entire data is flattened into a single table. However, in reality, this would translate to an extremely bad user experience as well as a lengthy and expensive ETL process. So the best practice is to have a star schema, generally. Also, it is recommended to only include the relevant columns from the source tables, as increasing the columns will result in an increase in model size which in turn will result in slower query performances. Increase in number of rows might still be ok as long as the cardinality of the columns don’t change much.

Depending on the specific customer requirements, there could be deviations from the best practices. For e.g., we built custom aggregate tables along with the detailed fact table in the case of a very large production model for a client. The resultant measure had a conditional statement to retrieve data from the aggregate table if the detailed level dimension data was not used in the report. Since the aggregate table was only 1/10 the size of the detailed fact table, the query came out 10x times faster whenever the details were not used, which was almost 90% of the times.

Optimize the DAX Calculations

In case of small models, Tabular is extremely forgiving in terms of bad DAX code also. However, just like in the case of bad design, performance takes a hit for the worse as you increase the data, add more users, or run complex queries. DAX performance tuning is the most difficult to tune from the current list, and it is important to have a strategy for maintaining and tuning the performance. A good place to start would be the Performance Tuning of Tabular models in SSAS 2012 whitepaper.

Monitor User Query Patterns and Train Users

Once your model is in production, it is important to keep monitoring the user query patterns as well as the resources to see potential bottlenecks. Through this, you can find whether the performance issues are being caused due to inefficient DAX, bad design, insufficient resources or most importantly, whether it is just because a user is using the model inefficiently. For e.g., in one of the cases, we found out the slow performance for all users was due to a single user dumping the entire 100 GB model into spreadsheets so he could perform custom calculations on top of it. This blocked the queries for all the other users and made things really slow for them. With appropriate requirement gatherings, we ensured all the required calculations for that user were there in the model and then trained the user to use the model for his analytics.

The success of any tabular project depends on the adoption by the end users and it is needless to say the adoption would be much better if the system is fast. These 5 tips will ensure you already have a jumpstart on that journey.

Looking to Use SQL Server Analysis Services Hosting?

As this is intermediate service, you can get this SQL Server Analysis Services on our Windows Dedicated server plan. You can start from our Bundled Cloud Platinum Class to get this service running on your server. This plan has included

- Windows Server 2008/2012 license
- 2 x 2.0 Ghz Core
- 4 GB RAM --> FREE Upgrade to 8 GB RAM (Use Promo Code ‘DOUBLERAM’)
- 2 x 500 GB Storage
- 20000 GB Bandwith
- 1000 Mbps Connection speed
- 4 Static IP
- Full 24 x 7 RDP Access
- Full 24/7 Firewall Protection
- Standard ver. SQL 2008/2012
- Support MySQL db
- PLESK Control Panel
- Antivirus
- Unlimited of MSSQL dbs
- Unlimited of MySQL dbs
- FREE SmarterMail service if you register this December

Get a HUGE discount for this Christmas!! For more information, please visit our official site at http://www.asphostportal.com.



ASP.NET MVC 6 Hosting - ASPHostPortal :: Introduction Tag Helpers MVC 6

clock December 3, 2015 21:31 by author Jervis

MVC6 introduces a new feature called Tag Helpers. In this post, we will explore how tag helpers can be used to improve the readability of your Razor views that generate HTML forms.

How do Tag Helpers work?

Tag Helpers are an alternative to HTML helpers for generating HTML. The easiest way to show this is with an example.

Let’s start by looking at an extremely simple example of a login view that is bound to a LoginViewModel that contains a UserName and a Password:

public class LoginViewModel
{
    public string UserName { get; set; }
    public string Password { get; set; }
}

Here is how we would generate an HTML input for the UserName property using an HTML Helper and a Tag Helper.

<!--Create an input for UserName using Html Helper-->
@Html.EditorFor(l => l.UserName)
<!--Create an input for UserName using Tag Helper-->
<input asp-for="UserName" />

With the HTML helper, we call C# code that return some HTML. With Tag Helpers we augment some HTML with special tag helper attributes. These special attributes are processed by MVC which will generate some HTML. Both of these approaches above will generate an input that looks like this:

<input name="UserName" class="text-box single-line" id="UserName" type="text" value="">

Why is this better?

At first glance, it might look like Tag Helpers are just a syntax change with no obvious benefits. The difference however, can make your Razor forms much more readable. Let’s say we wanted to do something simple like add a class to our UserName input:

<!--Create an input with additional class for UserName using Html Helper-->
@Html.EditorFor(l => l.UserName, new { htmlAttributes = new { @class = "form-control" } })
<!--Create an input with additional class for UserName using Tag Helper-->
<input asp-for="UserName" class="form-control" />

As you can see, the HTML helper approach becomes very hard to understand while the tag helper approach is very clear and concise.

Here is a full blown example of a login form using HTML helpers:

@using (Html.BeginForm("Login", "Account", new { ReturnUrl = ViewBag.ReturnUrl }, 
FormMethod.Post, new { role = "form" }))
{
    @Html.AntiForgeryToken()
    @Html.ValidationSummary(true, "", new { @class = "text-danger" })
 
    <div class="form-group">
        <div class="row">
            @Html.LabelFor(m => m.UserName, new { @class = "col-md-2 control-label" })
            <div class="col-md-10">
                @Html.TextBoxFor(m => m.UserName, new { @class = "form-control" })
                @Html.ValidationMessageFor(m => m.UserName, "", new { @class = "text-danger" })
            </div>
        </div>
    </div>
    <div class="form-group">
        <div class="row">
            @Html.LabelFor(m => m.Password, new { @class = "col-md-2 control-label" })
            <div class="col-md-10">
                @Html.PasswordFor(m => m.Password, new { @class = "form-control" })
                @Html.ValidationMessageFor(m => m.Password, "", new { @class = "text-danger" })
            </div>
        </div>
    </div>
 
    <div class="form-group">
        <div class="row">
            <div class="col-md-offset-2 col-md-2">
                <input type="submit" value="Log in" class="btn btn-primary" />
            </div>
        </div>
    </div>
}

And now the same form using tag helpers:

<form asp-controller="Account" asp-action="Login" asp-route-returnurl="@ViewBag.ReturnUrl" 
method="post" class="form-horizontal" role="form">
    <div asp-validation-summary="ValidationSummary.All" class="text-danger"></div>
    <div class="form-group">
        <label asp-for="UserName" class="col-md-2 control-label"></label>
        <div class="col-md-10">
            <input asp-for="UserName" class="form-control" />
            <span asp-validation-for="UserName" class="text-danger"></span>
        </div>
    </div>
    <div class="form-group">
        <label asp-for="Password" class="col-md-2 control-label"></label>
        <div class="col-md-10">
            <input asp-for="Password" class="form-control" />
            <span asp-validation-for="Password" class="text-danger"></span>
        </div>
    </div>
    <div class="form-group">
        <div class="col-md-offset-2 col-md-10">
            <input type="submit" value="Log in" class="btn btn-default" />
        </div>
    </div>
</form>

Overall, the tag helper version is much more readable. I especially like that we no longer need to use a using statement to generate a form element. That had always felt like a bit of a hack to me.

Another nice thing with the form tag helpers is that we don’t need to remember to explicitly add the AntiForgeryToken. The form tag helper does this automatically unless we explicitly turn it off using asp-anti-forgery=”false”

Of course, Visual Studio does a good job of highlighting the tag helper attributes so it is easy to distinguish them from regular HTML attributes

We also get full Intellisense inside the asp-for attributes.

How to enable Tag Helpers

The MVC Tag Helpers are located in the Microsoft.AspNet.Mvc.TagHelpers package so you will need to add a reference to that in your project.json file. Once you have added the reference, you can enable tag helpers in all your views by adding the following code to_GlobalImports.cshtml.

@addTagHelper "*, Microsoft.AspNet.Mvc.TagHelpers"



About ASPHostPortal.com

We’re a company that works differently to most. Value is what we output and help our customers achieve, not how much money we put in the bank. It’s not because we are altruistic. It’s based on an even simpler principle. "Do good things, and good things will come to you".

Success for us is something that is continually experienced, not something that is reached. For us it is all about the experience – more than the journey. Life is a continual experience. We see the Internet as being an incredible amplifier to the experience of life for all of us. It can help humanity come together to explode in knowledge exploration and discussion. It is continual enlightenment of new ideas, experiences, and passions


Author Link


Corporate Address (Location)

ASPHostPortal
170 W 56th Street, Suite 121
New York, NY 10019
United States

Sign in