Sunday, November 15, 2015

Single Cube vs Multiple Cubes in SSAS

Multiple Cubes

  • Having multiple, smaller cubes may result in faster query performance than one large cube in some cases, especially if the fact tables have very different dimensionality.
  • While it is possible to apply dimension security to the Measures dimension, it is much easier to allow or deny access to a cube with the multiple cube approach than it is to apply security to all the measures in a measure group using the single cube approach.
  • Having multiple, simpler cubes can be much more user friendly than one monster cube with loads of dimensions and measure groups. If you have Enterprise Edition you can of course use Perspectives to counter this, but if you are using Standard Edition then Perspectives aren’t available.
  • Maintenance can be easier and less disruptive with multiple cubes: if you need to make changes to a cube while users are querying it, you might end up invalidating users’ connections and dropping caches. With one cube the chances of this disruption affecting more users increases.
  • It’s easier to scale out with multiple cubes: if you find your server is maxing out, you can simply buy another server and distribute your cubes equally between the two. With a single cube approach you end up having to look at (admittedly not that much) more complex scale-out scenarios like network load balancing.

Single Cube

  • If you ever need to work with data from two fact tables in the same query or calculation, or if you think you might ever need to in the future, you should go with the single cube approach. The two options for cross-cube querying, linked measure groups and the LookUpCube MDX function, should be avoided. Linked measure groups are a pain to manage, carry a slight query performance overhead, and can result in the same MDX calculations being duplicated across the original cube and the cube containing the linked measure group (which means maintenance becomes more difficult). The LookUpCube function is probably the worst MDX function to use in a calculation from a performance point of view and should be avoided at all costs. So a single cube is the only feasible option.
  • Even if your users tell you they will not ever need to analyse data from two fact tables in the same query, be prepared for them to change their minds. In my experience, SSAS projects have a tendency to grow in complexity over time, and cubes that start out simple in a first release often grow lots of new functionality as time goes on – and the more successful the project, the quicker things get complicated. As soon as your users see what’s possible with SSAS they will start to have new, more ambitious ideas about the kind of analysis they want to do with their data, and it’s very likely that they will realise they do need to do cross-measure-group queries and calculations. If you started out on the multiple cube approach and then this happens you will have no choice but to use linked measure groups, and as I said this can make maintenance difficult; using the single-cube approach from the start means you won’t have this problem.

Thanks Chris Webb.

Technorati Tags: ,

Saturday, November 14, 2015

DAX Editor

DAX Editor is an extension for Visual Studio that makes easier to work with DAX.

It supports the following features

  • DAX syntax highlighting
  • DAX formatting. Support for increasing and decreasing format depth using Edit->Advanced->Increase Format Depth or Decrease Format Depth
  • IntelliSense support for keywords, functions, columns, and measures. Press Ctrl+Space to show IntelliSense.
  • Autocomplete for keywords, functions, tables, columns and measures. Press Ctrl+Space to autocomplete.
  • Syntax error reporting using squiggles/Error List
  • Brace matching
  • QuickInfo support in AutoComplete list that gives description of functions
  • F1 help for functions & keywords

Download URL

Technorati Tags: ,

BISM Normalizer

BISM Normalizer manages Analysis Services Tabular models for Enterprise Implementations.

  • Database Compare – Allows to compare different versions of the tabular models.
  • PowerPivot Model Migration – When migrating a Tabular model from PowerPivot to enterprise-level in Analysis Services, the “Import from PowerPivot” can be used. But this will create separate PowerPivot database for each migration. BISM Normalizer allows to merge models migrated from PowerPivot model into appropriate Analysis Services database.
  • Allows to maintain a master model that can be extended by different implementations within the Enterprise.
  • Enables Partial Deployments by scripting selected differences from the model.
  • Reports differences between models in an easily distributed Excel format.
  • Full Integration with Visual Studio allowing tight integration with TFS and enables Code Merging.
  • Reuse definitions between tabular models for alignment with common business definitions in an enterprise organisation.

Sunday, October 18, 2015

String or Binary data would be truncated when using MERGE in SSIS

Normally this error occurs when the column in target table doesn’t have the same column length of the source table. To locate the particular column, try the following

SET ANSI_WARNINGS OFF

[Insert your MERGE Statement here]

SET ANSI_WARNINGS ON

Technorati Tags:

Saturday, September 12, 2015

Snippet Designer

The Snippet Designer is a plugin which enhances the Visual Studio IDE to allow a richer and more productive code snippet experience

A Snippet editor integrated inside of the IDE which supports C#, Visual Basic, JavaScript, HTML, XML and SQL.

Integrates with VS 2013, VS 2012 and VS 2010

Click Once files installation folder

Click Once installs the files on the Users Desktop but it is hard to find the location of this folder

Once the application is installed through Click Once Deployment, Run the application and navigate to Task Manager and Right-Click on the Executable Name and Select "Open File Location".

It will completely an obscure path but this is where the installation files are stored to be accessed whether online or offline.

Sunday, May 24, 2015

SSAS Connector and Power BI Preview

Key things about SSAS connector and Power BI Preview

  • Only SQL Server Analysis Services Tabular models are supported for Power BI Preview
  • The connector has to continue running and the computer where it is installed has to remain on.
  • When you use the SSAS connector, your data remains on-premises.  The reports you create based on that data are saved in the cloud.
  • Q&A natural language querying is not currently available for SSAS on-premises tabular data.  Only cloud-based datasets are supported for Q&A.

Pre-requisites SSAS connector and Power BI Preview

  • NET Framework 4.5.1 or later must be installed on the computer.
  • Installing the Power BI Analysis Services Connector and the Data Management Gateway on the same computer is not supported. If you already have the Data Management Gateway installed, uninstall it before installing the Power BI Analysis Services Connector, or install the Connector on different computer.
  • The Analysis Services server is domain joined.
  • The Analysis Services connector & Analysis Services server are installed on computers in the same domain.
  • If you use a .onmicrosoft.com email address, you'll need to sync your Active Directory to Azure Active Directory using Azure Active Directory Sync (DirSync).

Technorati Tags: ,

Sunday, May 17, 2015

WebAPI IHttpActionResult vs HttpResponseMessage

The WebAPI Controller can return either HttpResponseMessage or IHttpActionResult. HttpResposeMessage converts it directly to an HTTP response message. Call ExecuteAsync to create an HttpResponseMessage then convert to an HTTP response message.

ApiController Action Result Methods by HTTP Status Code

Status Code Meaning Method
200 Operation successful Ok()
Ok(data)
302 Temporary Redirection Redirect(target)
RedirectToRoute(name, props)
400 Bad Request BadRequest()
BadRequest(message)
BadRequest(model)
404 Not Found NotFound()
409 Conflict Conflict()
500 Internal Server Error InternalServerError()
InternalServerError(Exception)
Technorati Tags:

Wednesday, May 06, 2015

[rsRuntimeErrorInExpression] The value expression for the textrun ‘Textbox1.Paragraphs[0].TextRuns[0]’ contains an error: Input string was in a correct format.

In this scenario, the expression for the textbox was =”Total Value: “ + Fields!TextBox1.Value

Changing that to =”Total Value: “ + CStr(Fields!TextBox1.Value) will fix the issue.

Technorati Tags:

Monday, April 27, 2015

Cross Origin Resource Sharing (CORS)

Cross Origin Resource Sharing (CORS) is a W3C standard that allows a server to relax the same-origin policy. Using CORS, a server can explicitly allow some cross-origin requests while rejecting others. CORS is safer and more flexible than earlier techniques such as JSONP.

he general mechanics of CORS are such that when JavaScript is attempting to make a cross-origin AJAX call the browser will “ask” the server if this is allowed by sending headers in the HTTP request (for example, Origin). The server indicates what’s allowed by returning HTTP headers in the response (for example, Access-Control-Allow-Origin). This permission check is done for each distinct URL the client invokes, which means different URLs can have different permissions.

In addition to the origin, CORS lets a server indicate which HTTP methods are allowed, which HTTP request headers a client can send, which HTTP response headers a client can read, and if the browser is allowed to automatically send or receive credentials (cookies or authorization headers). Additional request and response headers indicate which of these features are allowed.

Permission/Feature Request Header Response Header
Origin Origin Access-Control-Allow-Origin
Http Method Access-Control-Request-Method Access-Control-Allow-Method
Request Headers
Response Headers
Access-Control-Request-Headers Access-Control-Allow-Headers
Access-Control-Expose-Headers
Credentials   Access-Control-Allow-Credentials
Cache preflight Response   Access-Control-Max-Age

Technorati Tags: ,

Sunday, April 26, 2015

Display Modes

Display modes use a convention based approach to allow selecting different views based on the browser making the request.

For example, if a desktop browser requests the Home page, the application might use the Views\Home\Index.cshtml template. If a mobile browser requests the Home page, the application might return the Views\Home\Index.mobile.cshtml template.

There are two ways to create a custom display mode. The first way is to instantiate the DefaultDisplayMode class and set the ContextCondition property. The second way is to create a class that inherits from the DefaultDisplayMode class and set the ContextCondition property in the constructor.

Technorati Tags: ,

Microsoft Fakes

A mocking framework enables you to provide a fake implementation of a type or object, along with logic that verifies how calls were made to the mocked object. There are several good mocking frameworks currently available in the community, including Moq, Rhino, and NMock. Although these tools have strong followings and a good reputation, there was still a need to provide a mocking framework to customers who may be unable to utilize open source or third-party tools. Hence, the Microsoft Fakes framework in Visual Studio 2013.

The Fakes framework is derived from the Moles project by Microsoft Research. The Fakes framework is not backward-compatible with Moles, but the migration is straightforward.

Stubs are concrete implementations of interfaces and abstract classes that can be passed into the system being tested. A developer provides method implementations via .NET delegates or lambdas. A stub is realized by a distinct type that is generated by the Fakes framework. As such, all stubs are strongly typed. You cannot use stubs for static or non-overridable methods. Instead, you should use shims in those instances.

Shims are runtime method interceptors. They enable you to provide your own implementation for almost any method available to your code in .NET, including types and methods from the .NET base class libraries.

Task Stub/Shim Reason
Performance Stub The runtime code-rewriting used by shims introduces some performance issues at runtime. Stubs do not do this.
Static Methods Shim Stub can only influence overridable methods. They cannot be used for static, non-virtual, and sealed virtual methods.
Internal Types Stub/Shim Both stubs and shims can be used with internal types made accessible through the InternalsVisibleToAttribute attribute.
Private Methods Shim Shim types can replace private methods if all the types on the method signature are visible.
Interfaces/Abstract Methods Stub Stubs implement interfaces and abstract methods that can be used for testing. Shims can't do this because they don't have method bodies.
Technorati Tags: ,,

Accept Header and Content Types

The Accept header tells the server what your client wants in the response.

The Content-Type header tells the server what the client sends in the request.

httpClient.DefaultRequestHeaders.Add("Accept", "application/json");
httpClient.DefaultRequestHeaders.Add("Content-Type", "application/json");

Technorati Tags: ,

How to display errors during development with MVC?

Gets a value indicating whether custom errors are enabled for the current HTTP request.

protected override void OnException(ExceptionContext filterContext)
{
WriteLog(Settings.LogErrorFile, filterContext.Exception.ToString());

//custom error page
if (filterContext.HttpContext.IsCustomErrorEnabled)
{
filterContext.ExceptionHandled = true;
this.View("Error").ExecuteResult(this.ControllerContext);
}
}

This flag is controlled by the customErrors section in web.config. If the customErrors element’s mode attribute is set to On, IsCustomErrorEnabled returns True. If the CustomErrors element’s mode attribute is set to False or RemoteOnly, this flag is set to false.


Technorati Tags: ,

Saturday, April 25, 2015

Browser Link

Browser Link is a new feature in Visual Studio 2013 that creates a communication channel between the development environment and one or more web browsers. With Browser Link to refresh your web application in several browsers at once, which is useful for cross-browser testing.









For performance reasons, remove this setting when you publish your project.


Technorati Tags: ,,

Bundling

Bundling is all about compressing several JavaScript or stylesheets files without any formatting (also referred as minified) into a single file for saving bandwidth and number of requests to load a page.

bundles.Add(New ScriptBundle("~/bundles/jqueryBundle").Include(
            "~/Resources/Core/Javascripts/jquery-1.7.1.min.js",
            "~/Resources/Core/Javascripts/jquery-ui-1.8.16.min.js",
            "~/Resources/Core/Javascripts/jquery.validate.min.js"))

and can be rendered

@Scripts.Render("~/bundles/jquerybundle");

For stylesheets StyleBundle and @Styles.Render() need to be used.

Technorati Tags:

Resgen.exe

The Resource File Generator (Resgen.exe) converts text (.txt or .restext) files and XML-based resource format (.resx) files to common language runtime binary (.resources) files that can be embedded in a runtime binary executable or satellite assembly.

Resgen.exe is a general-purpose resource conversion utility that performs the following tasks:

  • Converts .txt or .restext files to .resources or .resx files. (The format of .restext files is identical to the format of .txt files. However, the .restext extension helps you identify text files that contain resource definitions more easily.)
  • Converts .resources files to text or .resx files.
  • Converts .resx files to text or .resources files.
  • Extracts the string resources from an assembly into a .resw file that is suitable for use in a Windows Store app.
  • Creates a strongly typed class that provides access to individual named resources and to the ResourceManager instance.
Technorati Tags: ,

AppFabric Service Bus Bindings

NetOnewayRelayBinding

NetOnewayRelayBinding supports one-way messages from client to the server. The method signatures for one-way methods in the service contract must not return any values. One-way methods are optimized for one-way TCP communications between the senders to the relay service and then to the receivers. The default size of the message is set to 65,536 bytes. The receiver using the NetOnewayRelayBinding opens a bidirectional TCP connection on outbound TCP port 828 for an SSL connection and TCP port 808 for a non-SSL connection. If the TCP outbound ports are unavailable due to environmental policies or port conflicts, you can configure the AppFabric Service Bus to use the HTTP protocol instead. The HTTP protocol polls the relay service through outbound ports 443 for SSL and 80 for non-SSL communications.

NetEventRelayBinding

NetEventRelayBinding extends the netOnewayRelayBinding by providing multicast messaging between multiple subscribers and publishers listening on the same rendezvous service endpoint. The netEventRelayBinding class inherits from netOnewayRelayBinding. This is the only binding that supports multiple receivers on the same service URI.

NetTCPRelayBinding

netTcpRelayBinding is the recommended and most frequently used AppFabric Service Bus binding. It uses TCP as the relay transport and is based on the WCF netTcpBinding. It performs better than the HTTP bindings, because it uses TCP for message delivery and the messages are encoded in binary format. NetTcpRelayBinding supports WS-ReliableMessaging, which is turned off by default. You can turn it on by setting reliableSessionEnabled to true. In WCF, you typically use netTcpBinding to create service endpoints reachable within the intranet, but with netTcpRelayBinding you can create service endpoints reachable over the Internet. This makes communication over the Internet faster than with HTTP bindings. Similar to netOnewayRelayBinding, netTcpRelayBinding establishes an SSL-protected control channel using outbound TCP port 828 and a non-SSL data channel using outbound TCP port 818.

HTTPRelayBinding

AppFabric Service Bus supports the following HTTP relay bindings:

  • BasicHttpRelayBinding
  • WebHttpRelayBinding
  • WSHttpRelayBinding
  • WS2007HttpRelayBinding
Technorati Tags: ,

Wednesday, April 22, 2015

first-of-type and last-of-type

The :first-of-type selector matches every element that is the first child, of a particular type, of its parent.

p:first-of-type {
background: #ff0000;
}

Specifies the background color for the first <p> element of its parent.

The :last-of-type selector matches every element that is the last child, of a particular type, of its parent.

p:last-of-type {
background: #ff0000;
}

Specifies the background color for the last <p> element of its parent.

Technorati Tags:

Action Filters in ASP.NET MVC

An action filter is an attribute that you can apply to a controller action -- or an entire controller -- that modifies the way in which the action is executed. The ASP.NET MVC framework includes several action filters:

  • OutputCache – This action filter caches the output of a controller action for a specified amount of time.
  • HandleError – This action filter handles errors raised when a controller action executes.
  • Authorize – This action filter enables you to restrict access to a particular user or role.

You also can create your own custom action filters. For example, you might want to create a custom action filter in order to implement a custom authentication system.

using System;
using System.Web.Mvc;

namespace MvcApplication1.Controllers
{
public class DataController : Controller
{
[OutputCache(Duration=10)]
public string Index()
{
return DateTime.Now.ToString("T");

}
}
}

In the above example, action is decorated with the OutputCache action filter.


The Different Types of Filters

The ASP.NET MVC framework supports four different types of filters:


  1. Authorization filters – Implements the IAuthorizationFilter attribute.
  2. Action filters – Implements the IActionFilter attribute.
  3. Result filters – Implements the IResultFilter attribute.
  4. Exception filters – Implements the IExceptionFilter attribute.

Filters are executed in the order listed above. For example, authorization filters are always executed before action filters and exception filters are always executed after every other type of filter.

Authorization filters are used to implement authentication and authorization for controller actions. For example, the Authorize filter is an example of an Authorization filter.

Action filters contain logic that is executed before and after a controller action executes. You can use an action filter, for instance, to modify the view data that a controller action returns.

Result filters contain logic that is executed before and after a view result is executed. For example, you might want to modify a view result right before the view is rendered to the browser.

Exception filters are the last type of filter to run. You can use an exception filter to handle errors raised by either your controller actions or controller action results. You also can use exception filters to log errors.

The base class for all action filters is the System.Web.Mvc.FilterAttribute class. If you want to implement a particular type of filter, then you need to create a class that inherits from the base Filter class and implements one or more of the IAuthorizationFilter, IActionFilter, IResultFilter, or ExceptionFilter interfaces.

The base ActionFilterAttribute class has the following methods that you can override:


  • OnActionExecuting – This method is called before a controller action is executed.
  • OnActionExecuted – This method is called after a controller action is executed.
  • OnResultExecuting – This method is called before a controller action result is executed.
  • OnResultExecuted – This method is called after a controller action result is executed.

Technorati Tags: ,

Responsive Meta Tag

<meta name="viewport" content="width=device-width, initial-scale=1">

This means that the browser will (probably) render the width of the page at the width of its own screen. So if that screen is 320px wide, the browser window will be 320px wide, rather than way zoomed out and showing 960px (or whatever that device does by default, in lieu of a responsive meta tag). It's generally recommended that you don't prevent scaling, as that's annoying and potentially an accessibility problem

Technorati Tags: ,

Strong names to Assembly using sn.exe

To protect assemblies from unauthorised changes they need to be signed with a strong name key. Strong name is valuable in guaranteeing the uniqueness of the assembly. A signed assembly can also be registered in the GAC.

How to sign an assembly?

To sign an assembly first generate a public/private key pair with the sn.exe utility:

sn.exe –k KeyPair.snk

Then you use Visual Studio project properties screen to sign the assembly with the snk file generated above.

image

Technorati Tags: ,

The configuration section ‘”connectionStrings”’ not found. Failed!

To fix this error make sure that you are not surrounding the connectionStrings with double quotes.

e.g. aspnet_regiis.exe -pef connectionStrings C:\inetpub\wwwroot\EncryptConnectionString

where C:\inetpub\wwwroot\EncryptConnectionString is the physical folder path where Web.config is located.

Technorati Tags: ,,

Encrypt connectionstring in web.config file

Suppose you have connectionstring as below.













To encrypt the connection string:


Open the command prompt and type the following to encrypt the connection string.


aspnet_regiis.exe -pef connectionStrings C:\inetpub\wwwroot\EncryptConnectionstring


where C:\inetpub\wwwroot\EncryptConnectionstring is the physical folder path where Web.config is located.


To decrypt the connectionstring type the following


aspnet_regiis.exe -pdf connectionStrings C:\inetpub\wwwroot\EncryptConnectionstring


Technorati Tags: ,

Tuesday, April 21, 2015

Cross site Request Forgery (CSRF)

Use Html.AntiForgeryToken() to protect ASP.NET MVC forms and also to validate the incoming post request add the [ValidateAntiForgeryToken] filter to the Target action method.

Adding a salt

If you want to protect multiple forms in your website independent of each other then use a salt value whenever you call Html.AntiForgeryToken() e.g.

<%= Html.AntiForgeryToken(“SomeStringHere”) %>

add the salt to the [ValidateAntiForgeryToken] e.g.

[ValidateAntiForgeryToken(Salt="SomeStringHere")]
public ViewResult SubmitMethod()
{
// code here
}

Note: AntiForgery Helpers work only with POST requests and not GET requests and the visitors must accept cookies.


Technorati Tags: ,

Monday, April 20, 2015

Code Contracts

The term ‘code contract’ was coined by Bertrand Meyer while he was developing the Eiffel programming language around 1986.

Code contracts and unit tests are not replacements for each other. They both have different purpose and different nature. It does not matter if you are using code contracts or not – you still have to write tests for your code.

The contracts take the form of pre-conditions, post-conditions, and object invariants. Contracts act as checked documentation of your external and internal APIs. The contracts are used to improve testing via runtime checking, enable static contract verification, and documentation generation. Code Contracts bring the advantages of design-by-contract programming to all .NET programming languages.

Automatic testing tools, such as Pex, take advantage of contracts to generate more meaningful unit tests by filtering out meaningless test arguments that don't satisfy the pre-conditions. Code contracts can be found in the System.Diagnostics.Contracts namespace.

using System;
using System.Diagnostics.CodeContracts;
namespace Products.BusinessLayer{
public class ProductName{
private string name;

public ProductName(string name)
{
Contract.Requires(
!string.IsNullOrWhiteSpace(name)
);
Contract.Ensures(!string.IsNullOrWhiteSpace(this.name));
this.name = ChangeName(name);
}
}
}

Technorati Tags: ,,

Sunday, April 19, 2015

Absolute Expiration vs Sliding Expiration

Absolute Expiration: Absolute expiration means It will expire cache after some time period set at the time of activating cache. This will be absolute expiration whether cache will be used or not It will expire the cache. This type of expiration used to cache data which are not frequently changing.

Cache.Insert("ABC", ds, null, DateTime.Now.AddMinutes(1), Cache.NoSlidingExpiration);

Sliding Expiration: Sliding expiration means It will expire cache after time period at the time of activating cache if any request is not made during this time period. This type of expiration is useful when there are so many data to cache. So It will put those items in the cache which are frequently used in the application. So it will not going to use unnecessary memory.

Cache.Insert("ABC", ds, null, Cache.NoAbsoluteExpiration, TimeSpan.FromMinutes(1));

Technorati Tags: ,

How to remove a project from Git Repository

Remove the .git folder and .gitattributes and .gitignore files and it will remove the git bindings from the Project.

Technorati Tags: ,,

Saturday, April 18, 2015

OWIN and Katana

Open Web Interface for .NET (OWIN) defines an abstraction between .NET web servers and web applications. By decoupling the web server from the application, OWIN makes it easier to create middleware for .NET web development. OWIN itself does not have any tools, libraries or anything else. It is just a specification.

Two design goals of OWIN are

  • New components could be more easily developed and consumed.
  • Applications could be more easily ported between hosts and potentially entire platforms/operating systems.

    Prior to OWIN, when you are building ASP.NET application, you are inheritably bound to IIS due to the heavy dependency on System.Web assembly.

    System.Web is something that exist ever since ASP (non .NET version) and internally contains many things that you might not even need (such as Web Forms or

    URL Authorization), which by the default run on every request, thus consuming the resources and making ASP.NET applications in general lot slower than it's counterparts at i.e. Node.js.

    Katana on the other hand, is fully developed framework made to make a bridge between current ASP.NET frameworks and OWIN specification developed by Microsoft still open source.These components include both infrastructure components, such as hosts and servers, as well as functional components, such as authentication components and bindings to frameworks such as SignalR and Asp.Net Web API.

    The project has the following three high level goals:

    • Portable – Components should be able to be easily substituted for new components as they become available. This includes all types of components, from the framework to the server and host. The implication of this goal is that third party frameworks can seamlessly run on Microsoft servers while Microsoft frameworks can potentially run on third party servers and hosts.
    • Modular/flexible – Unlike many frameworks which include a myriad of features that are turned on by default, Katana project components should be small and focused, giving control over to the application developer in determining which components to use in her application.
    • Lightweight/performant/scalable – By breaking the traditional notion of a framework into a set of small, focused components which are added explicitly by the application developer, a resulting Katana application can consume fewer computing resources, and as a result, handle more load, than with other types of servers and frameworks. As the requirements of the application demand more features from the underlying infrastructure, those can be added to the OWIN pipeline, but that should be an explicit decision on the part of the application developer. Additionally, the substitutability of lower level components means that as they become available, new high performance servers can seamlessly be introduced to improve the performance of OWIN applications without breaking those applications.
    Technorati Tags: ,

  • How to avoid broken views in SQL Server?

    SQL Views will break if you change the schema of the underlying table.
    • Create a table as shown below
    CREATE TABLE [dbo].[Employee](
     [EmployeeID] [int] IDENTITY(1,1) NOT NULL,
     [EmployeeFirstName] [varchar](50) NOT NULL,
     [EmployeeLastName] [varchar](50) NOT NULL,
     [EmployeeDOB] [date] NOT NULL,
     [CreatedDateTime] [datetime] NOT NULL,
     [ModifiedDateTime] [datetime] NOT NULL,
     CONSTRAINT [PK_Employee] PRIMARY KEY CLUSTERED 
    (
     [EmployeeID] ASC
    )
    GO
    • Populate the table with some records

    • Create a View as below
    CREATE VIEW [dbo].[EmployeeView]
    AS
    SELECT        EmployeeID, EmployeeFirstName, EmployeeLastName, EmployeeDOB, CreatedDateTime, ModifiedDateTime
    FROM            dbo.Employee
    
    GO
    • Do a select from the view.
    SELECT * FROM [dbo].[EmployeeView]
    image
    • Everything looks fine now.
    • Include a EmployeeMiddleName column in the Employee table and do the select from the view again.
    image
    • Can you see the difference? EmployeeMiddleName column doesn’t appear but the EmployeeMiddleName data appears under the EmployeeLastName column and EmployeeDOB has the EmployeeLastName data and so son.
    What happened here? SQL Server doesn’t bind the schema to the view and therefore when the schema changes the view is not affected. To mitigate this issue
    a) Don’t use * instead denote the column names in the view.

    b) Use Schema binding when creating a view but it will keep the schema of table and view intact and cannot change the underlying table schema until the view definition is modified or view is dropped otherwise the DB engine will throw an error. Schema Binding will improve stability and performance but also increases developers work.

    Technorati Tags: ,

    RenderFormat.IsInteractive and RenderFormat.Name

    Sometimes reports require branding not to be printed when exported to Excel. Since SQL Server 2008 R2 and above this can be done using the RenderFormat.IsInteractive property.

    Globals!RenderFormat.IsInteractive will return true when viewing the report in preview mode or using ReportManager tool and will return false for the export formats.

    It will really good to set the property if you want to

  • Use complex formatting and interactivity for on-screen display, but optimize for Excel export and post-processing with a more data oriented view.
  • Print: add/omit text disclaimers, logos, background images, etc.
  • PDF/Excel/Word: dynamically disable hyperlinks when report is exported
  • TIFF/Print: define a particular color chart palette for all rendering formats, but use an optimized grayscale palette when using TIFF for fax, or printing
  • If in your particular situation you have to make decisions based on the actual type of rendering format, you have to write your expression using the exact, case-sensitive name of the rendering extension as registered in RSReportServer.config / RSReportDesigner.config using the property Globals!RenderFormat.Name.

    Reporting Services built-in rendering extension names include: "WORD", "EXCEL", "PDF", "MHTML", "IMAGE" (includes TIFF, Print). Note that interactive viewing in preview or report manager uses the "RPL" renderer.

    Technorati Tags: ,

    Friday, April 17, 2015

    [Flat File Source [2]] Error: Data conversion failed. The data conversion for column "Name" returned status value 4 and status text "Text was truncated or one or more characters had no match in the target code page.".

    The issue may be because of the column having length greater than 50. By default all columns from the flat file get assigned the length of 50 and by increasing it the issue can be fixed.

    a) Right click on Flat File Source > Show Advanced Editor

    SNAGHTML540a213

    b) Expand Flat file Source Output > Output Columns > Select the column (you can see that from the error message) Increase the length to 255

    Technorati Tags:

    Thursday, April 16, 2015

    CurrentCulture vs CurrentUICulture

    They are both properties of a Thread object, which you can access via Thread.CurrentThread.

    • CurrentCulture – Tells you the user’s current locale, as set in the Region applet.  i.e. Where is the user located? The CurrentCulture is used for Dates, Currency, etc.

    image

    • CurrentUICulture – Tells you the native language of the version of Windows that is installed.  i.e. What language does the user speak?

    image

      The user normally can’t change CurrentUICulture without re-installing Windows. It’s used to know what language to use when displaying text in your application.  (i.e. Which resource files to load).

      To get the default values set the following in the Web.Config.






      Technorati Tags: ,,

      Wednesday, April 15, 2015

      Microsoft acquires Datazen

      Microsoft have acquired Datazen Software, an industry leader in mobile business intelligence and data visualization on Windows, iOS and Android devices.

      In particular, SQL Server customers love Datazen, because it is optimized for SQL Server Analysis Services and the overall Microsoft platform, enabling rich, interactive data visualization and KPIs on all major mobile platforms: Windows, iOS and Android.

      As of 14/04/2015, SQL Server Enterprise Edition customers with version 2008 or later and Software Assurance are entitled to download the Datazen Server software at no additional cost.

      Download Datazen Server (only Enterprise Edition customers with active Software assurance coverage)

      Technorati Tags: ,,

      Data Compression in SQL Server

      By taking advantage of SQL Server's data compression feature, you can improve SQL Server performance without changing any application code. Compressing data reduces database storage, which leads to fewer I/O reads and writes. However, SQL Server consumes more CPU resources when it compresses and decompresses data. Data compression is currently supported in the Enterprise, Developer, and Evaluation editions of SQL Server 2008 and later.

      Row compression doesn’t work on XML, BLOB and MAX data types instead you can create CLR functions to gzip them to reduce the storage space.

      Monday, April 13, 2015

      Using Display Folders in Tabular Model

      Better way of organising the measures and attributes in a tabular model is by using Display Folders.

      In Multi Dimensional display folders can be used to organize measures hierarchically. In Tabular, VS doesn’t provide it in a direct way instead by using BIDS Helper you can leverage the feature without using XMLA.

      image

      Technorati Tags: ,

      Wednesday, April 08, 2015

      Power BI Dashboard on Power BI Preview Site

      I recently built a dashboard using the Google Analytics data of my blog using the Power BI designer Preview.

      The Designer generated a .pbix file and now I wanted to publish it to my Power BI Preview site.

      Log on to the Power BI Site.

      Created a Dashboard folder named “CodeSpot” my blog name.

      image

      Click on “Get Data” and select “Power BI Designer File” and click “Connect”

      image

      Select the .pbix file from the File System and Click Connect and the file will be uploaded to the Power BI Preview Site.

      image

      And Here is the uploaded .pbix file.

      image

      Technorati Tags: ,

      Sunday, April 05, 2015

      Node.js

      Node.js is a platform built on Chrome's JavaScript runtime for easily building fast, scalable network applications. Node.js uses an event-driven, non-blocking I/O model that makes it lightweight and efficient, perfect for data-intensive real-time applications that run across distributed devices.

      Node.js Downloads

      NTVS (Node.js Tools for Visual Studio) is a free, open source plugin that turns Visual Studio into a Node.js IDE.

      NTVS supports Editing, Intellisense, Profiling, npm, TypeScript, Debugging locally and remotely (Windows/MacOS/Linux), as well Azure Web Sites and Cloud Service.

      Technorati Tags: ,,

      DAX Studio

      imageDAX Studio is a tool to write, execute, and analyze DAX queries in Power BI Designer, Power Pivot for Excel, and Analysis Services Tabular. It includes an Object Browser, query editing and execution, formula and measure editing, syntax highlighting and formatting, integrated tracing and query execution breakdowns.
      Technorati Tags: ,,


      AngularAgility

      Set of useful Angular.js extensions implementing common UX patterns to improve productivity. AngularAgility is a open source framework.

      Use AngularAgility Form Extensions to generate Angular.js form fields, labels and validation messages using Domain Specific Language (DSL)

      The benefits of using a DSL can be quite simply way less code has to be written to solve a particular coding problem.

      Cool examples on using AngularAgility- http://angularagility.herokuapp.com/#/home. Thanks JohnCulviner.

      Technorati Tags: ,,

      Saturday, April 04, 2015

      WebStorm

      WebStorm is a lightweight yet powerful IDE, perfectly equipped for complex client-side development and server-side development with Node.js. Note: It’s a licensed commercial product from JetBrains.

      It also provides integration with the AngularJS framework.

    • Download the AngularJS framework at http://angularjs.org/.
    • Configure AngularJS as a WebStorm JavaScript library, to let WebStorm recognize AngularJS-specific structures and provide full coding assistance:
      1. Open the Settings dialog box, and click JavaScript Libraries.
      2. In the Libraries area, click the Add button.
      3. In the New Library dialog box that opens, specify the name of the library.
      4. Click the Add button add next to the list of library files and choose Attach Files or Attach Directory on the context menu, depending of whether you need separate files or an entire folder.
      5. Select the Angular.js or Angular.min.js, or an entire directory in the dialog box that opens. WebStorm returns to the New Library dialog box where the Name read-only field shows the name of the selected files or folder.
      6. In the Type field, specify which version you have downloaded and are going to add.
        • If you added Angular.js, choose Debug. This version is helpful in the development environment, especially for debugging.
        • If you added the minified Angular.min.js, choose Release. This version is helpful in the production environment because the file size is significantly smaller.

      Technorati Tags: ,,

    • AngularJS

      AngularJS is a MVC framework developed by Google for creating dynamic views efficiently in a web application using HTML and JavaScript. Other JavaScript frameworks that are available are
      • KnockOut
      • JQuery
      • Backbone
      • Ember
      AngularJS is a open source complete application framework, complete replacement for JQuery.
      • It is fully dynamic MVVM with POJOs
      • It does low level DOM manipulation using directives and templates
      • AJAX/REST API interaction (Resource Module)
      • Code Organization, Dependency Injection, testability
      • Single Page Application Support with Routing (AngularUI Router)

      AngularJS vs JQuery

      AngularJS JQuery
      Complete application framework DOM manipulation, animations and an AJAX Wrapper
      Organized Code, easy to maintain Verbose code, hard to maintain
      Best choice for Dynamic UIs Horrible choice of Dynamic UIs
      Out-of the box 2 way binding Need to code a lot for 2 way binding

      AngularJS vs Backbone

      AngularJS Backbone
      Out of the box MVVM support Cannot provide MVVM support without add-ons
      Dependency Injection, Testability No DI and not easily as testable
      Full-featured framework Light weight framework (not fully featured)
      Actively Developed Not Actively Developed

      AngularJS vs KnockOut

      AngularJS KnockOut
      Complete Application framework Not an complete Application framework
      POJO is supported No POJO
      Code Organization, Testability No Code Organization and not easily as testable
      No Code Duplication Code Duplication
      Technorati Tags: ,,,

      Friday, April 03, 2015

      OWASP – Open Web Application Security Project

      The Open Web Application Security Project (OWASP) is an open community dedicated to enabling organizations to develop, purchase, and maintain applications that can be trusted.

      The 2013 OWASP Top 10 most Critical Web Application Security Risks are:

      • A1: Injection
      • A2: Broken Authentication and Session Management
      • A3: Cross Site Scripting (XSS)
      • A4: Insecure Direct Object References
      • A5: Security Misconfiguration
      • A6: Sensitive Data Exposure
      • A7: Missing Function Level Access Control
      • A8: Cross-Site Request Forgery
      • A9: Using components using known vulnerability
      • A10: Unvalidated Redirects and Forwards

      STRIDE Threat Model

      • Spoofing identity. An example of identity spoofing is illegally accessing and then using another user's authentication information, such as username and password.
      • Tampering with data. Data tampering involves the malicious modification of data. Examples include unauthorized changes made to persistent data, such as that held in a database, and the alteration of data as it flows between two computers over an open network, such as the Internet.
      • Repudiation. Repudiation threats are associated with users who deny performing an action without other parties having any way to prove otherwise—for example, a user performs an illegal operation in a system that lacks the ability to trace the prohibited operations. Nonrepudiation refers to the ability of a system to counter repudiation threats. For example, a user who purchases an item might have to sign for the item upon receipt. The vendor can then use the signed receipt as evidence that the user did receive the package.
      • Information disclosure. Information disclosure threats involve the exposure of information to individuals who are not supposed to have access to it—for example, the ability of users to read a file that they were not granted access to, or the ability of an intruder to read data in transit between two computers.
      • Denial of service. Denial of service (DoS) attacks deny service to valid users—for example, by making a Web server temporarily unavailable or unusable. You must protect against certain types of DoS threats simply to improve system availability and reliability.
      • Elevation of privilege. In this type of threat, an unprivileged user gains privileged access and thereby has sufficient access to compromise or destroy the entire system. Elevation of privilege threats include those situations in which an attacker has effectively penetrated all system defenses and become part of the trusted system itself, a dangerous situation indeed.

      Tools

      Technorati Tags: ,,,

      Wednesday, April 01, 2015

      Imported Project C:\Temp\xxxxx\nuget.Targets was not found. Confirm that the path in the <Import> declaration is correct and that the file exists on disk

      Make sure NuGet is already installed. Right-click on the solution and click “Enable NuGet Package Restore”.

      Click Ok and re-open the solution or re-load the project to fix the error.

      Technorati Tags: ,

      Tuesday, March 31, 2015

      Power BI Designer Preview - Google Analytics Connector

      Today I tried using the Google Analytics Connector released recently as part of Power BI Designer Preview to get some insights about my blog. It was relatively easy to get going.

      Step 1:Open Power BI Designer Preview and click Get Data

      Step 2:Select Google Analytics from the Get Data screen

      Step 3:Select the site name for getting the Analytics data

      Step 4:List of subject areas, measures and dimensions

      Step 5:Select the subject areas and measures from the site

      Step 6:Final Output

      Saturday, March 28, 2015

      SQL Server Analysis Services Tabular Model

      Tabular models are in-memory databases in Analysis Services. They use the xVelocity in-memory analytics engine (VertiPaq) to deliver fast access of data from client tools (Excel, PowerView)

      Tabular models support data access through two modes: Cached mode and DirectQuery mode. For cached mode use the In-Memory query mode and for passthrough use the DirectQuery query mode

      The In-Memory query mode requires processing to update data while the DirectQuery mode retrieves results from the data source

      Limitations of DirectQuery mode

      • Model can be queried only by DAX Queries
      • Permissions must be defined in the relational database
      • Calculated columns are not supported
      • DirectQuery models do support the use of DAX formulas for use in measures, which are converted to set-based operations against the relational data store. All measures that you create by using implicit measures are supported.
      • Can only be based on Single SQL Server relational database

      Tabular model in SSAS offers 4 deployment options

      • DirectQuery
      • DirectQuery with In-Memory
      • In-Memory
      • In-Memory with DirectQuery

      In-Memory: This is the default uses the xVelocity engine. This in-memory columnar storage engine has been optimized for high performance analysis and exploration of data. It provides fast query times for aggregation queries

      DirectQuery: This mode uses relational data that is stored in a SQL Server database (it is similar to the ROLAP mode in a multidimensional model). It lets users retrieve data directly from a SQL Server data source in real-time.

      In-Memory with DirectQuery: This is a hybrid mode. By default, queries should be answered by using the In-Memory mode, however, the connection string from the client can instead choose to use the DirectQuery mode.

      DirectQuery with In-Memory: This is a hybrid mode. By default, queries should be answered by using the DirectQuery mode, however, the connection string from the client can instead choose to use the In-Memory mode.

      Sunday, March 08, 2015

      Http Status Codes

      Subset of available HTTP Status Codes

      Status codeAPI meaning
      200All is good; response will include applicable resource information, as well
      201Resource created; will include the Location header specifying a URI to the newly created resource
      202Same as 200, but used for async; in other words, all is good, but we need to poll the service to find out when completed
      301The resource was moved; should include URI to new location
      400Bad request; caller should reformat the request
      401Unauthorized; should respond with an authentication challenge, to let the caller resubmit with appropriate credentials
      402Reserved for future use, but many times used to indicate a failure in a business rule or validation check
      403Access denied; user successfully authenticated, but is not allowed to access the requested resource
      404Resource not found, or caller not allowed to access the resource, and we don't want to reveal the reason
      409Conflict; used as a response to a PUT request when another caller has dirtied the resource
      500Server error; something bad happened, and server might include some indication of the underlying problem

      Friday, March 06, 2015

      SQL Server Always On Availability Groups and MultiSubnetFailover and OLEDB connections

      Having problems with failover not working with SQL Server Always On Availability Groups?

      Check whether you are using OLEDB Connections as OLEDB in SQL Native Client doesn't support MultiSubnetFailover keyword and ignores it even if available. Instead ADO.NET or ODBC need to be used.