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.