Faisal's Technical Blog

Power BI - Analyze in Excel

2016-12-10T18:16:00.004-05:00

Analyze in Excel is an interesting feature of Power BI (Power BI Service, to be specific) that lets users analyze and explore Power BI data models using Excel. In other words, the datasets that we publish to Power BI Service can be analyzed using Excel (requires Excel 2010 SP1 and later). To get started with this feature, click on the ellipsis next to your dataset, and click on the option ‘Analyze in Excel’ as shown in the screen shot below.

Power BI will download a connection to this dataset in the form of an office data connection (odc) file.

Click open to open up your dataset in an Excel PivotTable, which exposes all the data points in your data model. You can slice and dice anything by pretty much any field you have in your data model. As you are analyzing the data, the Excel PivotTable will be pulling data from the data model hosted in the cloud (Power BI Service). Please note that the PivotTable in this case won’t let you define aggregation on numeric fields other than what you've already defined in the Power BI dataset. For example, if there is a numeric field in the dataset, you won’t be able to place it under "VALUES" section of the PivotTable to aggregate it if you haven’t defined the aggregation on that field in the Power BI dataset.

Once you have your report ready, you can save it anywhere you like or share it with other users. You can also publish the Excel report back to Power BI Service, however, it will be uploaded as a static Excel file without any interactivity or ability to refresh data, etc. Below is a screenshot of a sample report created using Analyze in Excel.

I hope this is helpful, please leave a comment if you have a question.

Quick Insights In Power BI

2016-12-04T15:50:00.002-05:00

Quick Insights is a unique feature of Power BI that uses Machine Learning Algorithms to search for patterns, trends, and correlations in data, and provides a list of insights in the form of visuals. These insights (visuals) can be saved by pinning them to a dashboard. To run Quick Insights on your dataset, click on the ellipsis next to your dataset and then select View Insights as shown below.

Power BI will run Machine Learning Algorithms on your dataset to look for correlations, outliers, trends, patterns, etc. and a notification pops up at the top right-hand corner indicating that the insights are ready. This process might take some time depending on the size of the dataset.

Clicking on the View Insights button will show all the visualizations that were put together by the algorithms, as shown in the screenshot below.

If you like a particular insight, you can save the visual by pinning the visual to a (new or existing) dashboard. Another interesting thing to note here is that Power BI lets us run Quick Insights on each of these visuals individually. For example, if I click on the first visual produced by Quick Insights (the visual will open in focus mode), there is an option called Get Insights on the top right corner as shown in the screenshot below.

If I click on Get Insights, Power BI will run the Machine Learning Algorithms again, but this time, it just focuses on the fields used in this particular visual. As shown in the screen shot below, the algorithms produced a quick summary of the existing visual and a list of new insights based on the fields used in the current visual.

I can click on any of these new visuals and get new insights from them (insights from insights) and continue this process. The option to run Quick Insights is also available for individual tiles in a dashboard — just click on the “focus mode” option on the tile to open in focus mode, and you’ll see the option Get Insights on the top right corner.

I hope this helps; please use comments if you have any questions.

Power BI Content Packs

2016-11-26T17:24:00.002-05:00

What are Power BI Content Packs?
For popular SaaS applications like SalesForce, Dynamics 365, Google Analytics, etc., Microsoft has built a collection of data models, dashboards, and reports and made them available for Power BI users to consume. This pre-packaged collection of data models, dashboards, and reports is called Content Pack. Simply put, Content Packs provide out-of-the-box connectors to popular SaaS applications with a prebuilt data model, reports, and dashboards; these dashboards and reports can be customized (“personalized”) if required to fit our own requirements. In just a matter of a few minutes, you can use content packs to get immediate insights into my data. Another great thing about content packs is that you do not require Power BI Pro to be able to use them (Power BI Free can be used).

Limitations of content packs
The following are some of the limitations of content packs:

1. Generic: Content packs are generic in the sense that they only include standard entities from the SaaS applications. For example, if you are using Dynamics 365 CRM content pack, you’ll find that it only includes the standard, out-of-the-box entities from your CRM system – any custom entities/tables from your CRM system are not included.

2. Lack of extensibility: Content packs are not customizable. The reports, dashboards in the content packs can be customized but the data model can’t be modified. For example, if you are using Dynamics 365 CRM Content Pack and you want to add custom entities from your CRM, you are out of luck.

Update – Microsoft recently published an article that explains how to customize Dynamics 365 Content Packs - Customize Microsoft Dynamics 365 Power BI content packs

You can get around these limitations by creating your own content packs and sharing them with your co-workers - these are called Organizational Content Packs. The main difference between the “standard” content packs from Microsoft and the organizational content packs is that the standard content packs are built by Microsoft (soup to nuts), whereas the organizational content packs are something we’ll have to create on our own, from scratch. Organizational content packs require Power BI Pro (paid license).

Accessing/consuming content packs

The standard content packs provided by Microsoft can only be accessed using Power BI Service (they are NOT accessible using Power BI Desktop). Follow these steps to connect to/consume a content pack.

1. In Power BI Service, click on the Get Data option in the navigation bar

2. Under Content Pack Library, click on Services. Below is the screenshot for step #1 and #2.

3. The Power BI AppSource window opens up. Locate your content pack or search for one in the search box.

4. Click on the Get button on the selected content pack. The screen shot below shows a content pack for Microsoft Dynamics CRM.

5. The next dialogue box asks for the service URL as shown below. Enter you Service URL and click next.

6. In the next dialogue box, select the appropriate authentication method (for my CRM Service, I've selected OAuth2) and sign in using your account.

7. Once you sign in, Power BI will establish a connection with your service (in my case, with my CRM Online instance), and will import all the required data. In just a few minutes, all the reports, dashboards, etc. will be ready for use.

Note: The dataset from the content pack will automatically refresh once a day and you can schedule it to refresh at a particular time (click on the ellipsis next to the dataset and select 'Scheduled Refresh'). You can also refresh the data more frequently than once a day if you have Power BI Pro.

Hope this is helpful. Please leave a comment if you have any questions.

Power BI Glossary - Terms & Defintions

2016-11-24T17:12:00.000-05:00

Power BI

Power BI is a business analytics platform which is built using a collection of tools and services. Primarily, Power BI is a stack of two “companion” applications – Power BI Desktop and Power BI Service.

• Power BI Desktop is a freely downloadable, stand-alone, self-service BI application used to create data models, explore, slice and dice data, create interactive reports & visualizations, and share with other users in the organization by publishing the content to Power BI Service.

• Power BI Service is a cloud application hosted and maintained by Microsoft. Power BI Service provides a platform for business users to share, collaborate and interact with the content created using Power BI Desktop. Not only that, we can also create new reports and visitations using this service. Users would typically interact with the service using a web browser or a mobile device (there are native mobile applications available for windows, iOS, and android devices).

Dataset

A dataset is the data model that is published to the Power Service. Using Power BI Desktop, I can create a data model by extracting data from my data sources (both cloud as well as on-premises), create reports, and publish the document to Power BI Service. The data model from the document shows up under Datasets in Power BI Service. Reports will show up under the Reports section

Visual

A visual is a chart used to represent data in a graphical fashion. As of this writing, Power BI has a total of 27 visuals (and the number is expected to grow).

Tile

A tile is a snapshot of the data (it can be a single visual, or an entire page from a report) which is added (“pinned”) to a dashboard. A tile is added to a dashboard by “pinning” the tile to the dashboard (on each visual, there is a “pin” icon). Tiles are set to auto-refresh every 15 minutes but can be refreshed manually as well. On the dashboard page, if you click the ellipsis (…) on the top right-hand corner, there is an option to “refresh dashboard tiles”.

Report

A Power BI report consists of one or more pages (tabs) with each page containing visualizations, text boxes, images, etc.

Dashboard

A dashboard is created from one or more reports. Let’s suppose I have a report with a few tabs with several visuals on each tab. I can create a dashboard by selecting the visuals I like from each tab and “pinning” those visuals on the dashboard.

I can also create a dashboard by selecting visuals from different reports — this is the beauty of a Power BI dashboard — for example, if I have a report built off of my CRM dataset, and another report built off of my ERP dataset, I can pick visuals from these two reports and have them on a single dashboard. In other words, a dashboard can be created from visuals that belong to different reports and these reports can be from different datasets.

Trivia notes –
1. Visuals in the dashboards can’t be modified; those visuals need to be modified in the report and the changes would automatically reflect in the dashboard.
2. Also, dashboards don’t have drill-down capabilities (as of this writing). If you think providing drill-down capabilities in a dashboard will be helpful, please vote this idea here https://ideas.powerbi.com/forums/265200-power-bi-ideas/suggestions/7161369-drill-down-in-dashboard

Gateway

Power BI Gateway is an application that connects your on-premises data sources to Power BI Service so you can refresh your reports that are published to Power BI Service. For more information on Power BI Gateway, check out this post — Power BI Gateway

Analyze in Excel

The data sets we publish to Power BI Service can be explored and analyzed using Excel. This feature of Power BI is called Analyze in Excel. If I click on the ellipsis next to the dataset, and click on the option ‘Analyze In Excel’, Power BI will open up the dataset in an Excel pivot table, exposing all the data points in the data model. I can slice and dice by pretty much any field in the data model. Fore more information on this, check out this blog post - Power BI Analyze in Excel

Content Packs

For popular SaaS applications like SalesForce, Dynamics CRM Online, Google Analytics, etc., Microsoft has built a collection of data models, dashboards, and reports and made them available for Power BI users to consume. This pre-packaged collection of data models, dashboards, and reports is called Content Pack. Simply put, Content Packs provide out-of-the-box connectors to popular SaaS applications with prebuilt data model, reports, and dashboards; these dashboards and reports can be customized (“personalized”) if required to fit our own requirements.
For more information on content packs, check this blog post out — Power BI Content Packs

Quick Insights

Quick Insights is a unique feature of Power BI that uses Machine Learning Algorithms to search for patterns in data and provides a list of insights. These insights (visuals) can be saved by pinning them to a dashboard.
To know more about this feature, check this blog post out — Quick Insights In Power BI

Natural Language Query (Power BI Q&A)

Natural Language Query or Q&A is another unique feature of Power BI that lets users ask questions in simple language, and Power BI responds to the questions in the form of a visual. For example, I can ask a question by typing in ‘Show me sales this year vs. last year by product category’. I can also specify filters and visual type in my question. For example, I can type ‘show me sales by customer for the current quarter on a column chart’. These answers (which are visuals) can be saved to a dashboard if needed.

Of course, for this feature to work properly, we’ll need to use the terms and phrases that we have used in our data model. For example, if I ask Power BI ‘show me sales by customer’ but in my data model, customers are named/labeled as accounts, then Power BI Q&A may not provide the expected answer.

Power BI Free vs. Power BI Pro

There are two license types available in Power BI – Power BI Free and Power BI Pro.

· Power BI Free is free but it lacks some advanced Enterprise features

· Power BI Pro is $9.99 per user per month and is subscription based

For more information on Power BI pricing and licensing, check this post — Power BI Pricing and Licensing

What is Power BI Gateway

2016-11-20T15:48:00.004-05:00

Power BI Gateway is an application that connects your on-premises data sources to Power BI Service so you can refresh your reports that are published to Power BI Service.

For example, let’s suppose I created a report using Power BI Desktop by extracting data from a SQL Server database which is hosted on my company’s network (on-premises). I then publish this report to Power BI Service so I can share it with other users in the company and also access it using a browser or a mobile device. If I need to update/refresh the report with latest data, I can open up the report in Power BI Desktop, refresh the data and publish it again to Power BI Service. Of course, this method of manual data refresh is not sustainable so Power BI provides an application (gateway) to automate the process of updating/refreshing data.

There are two types of gateways in Power BI

- Personal Gateway: Usually installed by a user for individual/personal use. This gateway only services one user. If the user installing the gateway has administrator privileges on the machine, then the gateway is installed as a service, otherwise, the gateway runs an application.

- Enterprise Gateway: Usually installed on a server by an IT administrator to serve multiple users in the organization. Enterprise Gateway includes more advanced features that are not available in Personal Gateway – for example, Direct Query to SQL Server, live connection to Analysis Services, etc.

For both the types of gateways, the computer where the gateway is installed need to be turned on for the refresh to work. If the Personal Gateway was installed with administrator privileges (in which case it is running as a service), then just having the computer turned on is enough; however, if the Personal Gateway was installed using non-administrative privileges (which means it is running as an application), then user need to be logged on to the computer for the refresh to work

Here are some FAQs about Power BI Gateway:

1. Why does Power BI require a gateway to access my data sources that are on premise?

Since the data sources are in your corporate network, Power BI Service can’t access the data sources to be able to update/refresh the reports/dashboards with latest data.

2. Does Power BI require opening up firewall ports to be able to access data?

No. The important thing to note here is that the gateway is installed on one of the computers that are on the corporate network, and it initiates an outbound call to Power BI Service so no inbound ports need to be opened.

3. How does the gateway work for SaaS data sources such as Dynamics CRM Online, SalesForce, etc.?

Since SaaS applications are hosted in the cloud (externally accessible with a valid account), a gateway is not required. Moreover, data sets that use online/cloud data sets such as SalesForce, etc. are updated automatically once a day. You can also manually refresh the data set or schedule a refresh.

4. Is there a way to schedule incremental refresh in Power BI?

No, the refresh in Power BI is a full/complete refresh

5. Do I need Power BI Pro to use Personal gateway?

Yes, both Personal and Enterprise Gateway require Power BI Pro license to set up the refresh.

6. How do I determine whether I should use Personal Gateway vs. Enterprise Gateway?

If you need the gateway to serve multiple users and want IT to manage the security around which users should have access to which data sources, and need to make sure the refresh works 24/7, then Enterprise Gateway is the way to go.

Power BI - Pricing and Licensing

2016-11-19T22:47:00.000-05:00

There are two types of licenses available in Power BI - Power BI Free and Power BI Pro.

Power BI Free is free, of course, but it lacks some Enterprise/advanced features.

Power BI Pro is $9.99 per user per month and is subscription based (yearly subscription is required).

Power BI licensing is not based on the role of the user, but rather on the content the user is consuming. For example, like most licensing types, you won’t find a read-only user license or a power-user license. Let’s take a look at the primary components of Power BI and see their license types. As you know, there are main components of Power BI – Power BI Desktop and Power BI Service.

Power BI Desktop is absolutely free. All the features, capabilities of Power BI Desktop are free without any restrictions.

For Power BI Service, the general rule of thumb is that if a user is consuming “pro content”, then they require Power BI Pro else the free version will suffice. Pro content is mainly based on the following 3 factors:

1. Data Refresh - Here are some scenarios that will you help understand when a pro license is needed.

· Your data source is hosted on-premises, and you are pulling the data into Power BI. If you need to refresh the data automatically, then you'll need Power BI Pro. Please note that data can be refreshed manually, in which case, Power BI Pro is NOT required.

· Your data source is hosted on premises, and you are querying the data source live (using DirectQuery option). Querying on-premises data sources live requires Power BI Pro.

· Your data source is hosted in the cloud (for example, you are using Dynamics CRM Online, SalesForce, etc.). You can refresh data once a day using Power BI Free; however, refreshing data more frequently than once a day requires Power BI Pro.

2. Collaboration - If the report, dashboard, etc. is in a Group Workspace, users will need a pro license to access the report/dashboard. Also, creating organizational content pack requires a pro license.

3. Security - If the report, dashboard, etc. is using Row Level Security (RLS), users will need a pro license to view the report/dashboard.

More information on Power BI Pricing can be found here - https://powerbi.microsoft.com/en-us/pricing/

Note: If you are using on-premises data sources, you'll need to use Power BI Gateway to refresh the data automatically.

Power BI Desktop issue - More location data is required to create a filled map.

2016-02-17T16:16:00.000-05:00

I just ran into this issue while trying to create a 'filled map' visual in Power BI Desktop. The exact error message is:

More location data is required to create a filled map. To create a filled map, the location data should include Bing Map-supported geographic data, such as country/region, state/province, or postal code.

I tried using country, state etc. as suggested in the error message but got the same error. I also tried switching the visual to a regular map - though it didn't throw any errors, it didn't work (won't show any data points etc.) As shown in the screenshot below, I have a column called business_address_01_city in my data model and Power BI desktop doesn't seem to think that this column stores geographic data (even after adding state, country, etc.).

More location data is required to create a filled map.

So I did some troubleshooting and figured out that there are two ways to address this issue:

1. Have the column names as City, State, ZipCode, etc. This will force Power BI Desktop to think that the columns store geographic data. I acknowledge that this could be an issue if you have more than one City or State fields in the model and/or if you have certain naming conventions you'd like to stick with.

2. Assign the correct Data Category for the column in the Modeling tab as shown in the screenshot below. For example, if there is a column called business_address_01_country in the data model, select the column and change the Data Category to Country and Power BI Desktop will understand that this column has geographic data.

I hope this is helpful. If anyone has another solution they used to resolve this issue, please let me know by posting a note in the comments.

SMP versus MPP architecture in the context of Azure SQL Data Warehouse

2015-12-12T11:53:00.001-05:00

A Symmetric Multi-Processing (SMP) system is a computing architecture in which all the processors share the same operating system, memory, and disk storage and are connected via a system bus.

As shown in the image above, SMP is a single-bus system where there is a common way to access resources (such as storage, for example) – this is one of the main architectures bottlenecks of an SMP system that events it from scaling. All the traditional SQL Server suite of products including SQL server 2005, 2008, 2012 etc. use SMP architecture.

A Massively Parallel Processing (MPP) system, on the other hand, uses a "shared nothing" approach. In this architecture, each processor has its own set of resources (memory, disk storage, operating system) and each processor is fully independent and isolated from other processors. As you can guess, there is no single point of contention in this architecture and hence, it is able to scale massively.

All the nodes in the system communicate with each other via a high-speed communication system. An MPP system can be thought of as a set of SMPs lined up and programmed to perform a single task in a parallel and coordinated fashion.
Examples of SQL Server products that are able to leverage MPP architecture: Microsoft Analytics Platform System (formerly SQL Server Parallel Data Warehouse), Azure SQL Server Data Warehouse.

Now let’s try to put this concept in the context of Azure SQL Data Warehouse:

In an SMP architecture
- There is a single instance of SQL Server shared by all the resources (CPU, memory, disk storage)
- Though multiple CPUs work together to execute individual tasks concurrently (using application threading), the main bottleneck is that memory, disk storage etc. will be shared by all the CPUs.

In an MPP architecture (which Azure SQL Data Warehouse is built on)
- Each node runs its own instance of SQL Server and processes only the rows on its own disks - for example, in a 4-node MPP system, there will be 4 instances of SQL Server processing queries in parallel. If a table has 1 Million rows, considering the same 4-node example, each node will store 250,000 rows. This means, in theory, a query that takes 1 minute on an SMP system will take 15 seconds on an MPP system with 4 nodes
- When a query is executed (say a simple select statement), all the nodes in the system work in parallel to retrieve data from their respective disks and coordinate with each other to satisfy the request
In other words, the system distributes data and query processing across the nodes, thereby increasing parallelism and eliminating bottlenecks that are inherent in an SMP architecture.

Power BI Desktop vs. Power BI Service

2015-11-07T23:19:00.000-05:00

There seems to be some confusion around the difference between the Power BI Desktop and Power BI Service (aka PowerBI.com) and when to use which. Please note that though Power BI Desktop and Power BI Service are different applications meant for different purposes and are referred to as "companions applications" by Microsoft, they do share some commonalities and hence the reason for confusion. Below are some of the key differences:

- Power BI Desktop is mostly about content authoring whereas Power BI Service is mostly about consumption, collaboration, and administration. IMO, this is the main difference between the two applications.

- Though both Power BI Desktop and Power BI Service provide a freeform, drag-and-drop canvas for creating stunning visualizations, there are a few things that are more “richer” in one than the other and/or only available in one and not in the other application. Below are a few examples:

We can’t create data models (datasets) using the Power BI Service, we can only ‘consume’ the data models created using Power BI Desktop (or Excel add-ins for that matter).
Content Packs can only be created and shared using the Power BI Service
Ability to setup data-driven alerts and create annotations are only available on mobile devices and hence only possible in the Power BI Service
Quick Insights (an interesting feature of Power BI ) is only available in Power BI Service
Natural Langauge Query (also called Q&A) is only available in Power BI Service
Calculated fields can only be created in Power BI Desktop

Hope this is helpful!

Introduction To Power BI

2015-11-07T20:39:00.002-05:00

At a very high level, Power BI is a stack of two "companion" applications - Power BI Desktop and Power BI Service.

Power BI Desktop (formerly Power BI Designer): A standalone self-service BI tool/application that is freely downloadable from the Power BI site (available in 32-bit & 64-bit mode). Power BI Desktop is a data modeling and content authoring tool that unifies the power of Power Query, Power Pivot, and Power View.

Below are some of the important tasks we can do using Power BI Desktop:

Discover, connect, extract, transform data from public as well as corporate data sources (this was done using Power Query).
Create data models (this was done using Power Pivot).
Create interactive, drag-and-drop visualizations and explore/navigate geospatial data on a map in just a matter of a few clicks (this was done using Power View and Power Map).
Publish/Deploy data models and visualizations to Power BI Service (more on this below) with a single click. Power BI Service is where the published content will be available for other users in the organization.

Power BI Service: Power BI Service (aka PowerBI.com) is a cloud-based Business Analytics Service (SaaS offering) that provides a simple, interactive, and user-friendly interface to create, view, share dashboards/visualizations, interact with your data and collaborate across your organization. Users interact with the service using a web browser or a mobile application.

Below are some of the important tasks we can perform with the Power BI Service:

Connect to a variety of data sources (on premise or cloud).
Build rich, interactive reports and dashboards.
Interact with data using Natural Language Query (also called as Q&A) – This feature allows users to ask questions in plain English and Power BI will respond to the question with an interactive chart or table. Power BI will interpret the semantics of the question and display data in the form of a visual. This visual can be tweaked to fit the need or can be added to an existing dashboard.
Leverage Content Packs – For popular SaaS systems such as SalesForce, Zendesk, GitHub, Dynamics CRM etc., Microsoft Power BI team has built a set of data models, dashboards, and reports and published it to the Power BI Service so that users can consume the models/dashboards/reports. This pre-packaged collection of data models, dashboards, and reports is called Content Pack. Simply put, Content Packs provide out-of-the-box connectors to popular SaaS sources with pre-built data model, reports, and dashboards; these dashboards and reports can be customized (“personalized”) if required to fit the needs.
We can also create our own “Organizational Content Packs” and publish them to the Power BI Service. More on this here.
Stay connected from anywhere – users don’t need to be on the corporate network, all they need is a browser (or Power BI app if using a mobile device) and an internet connection.
Upload Excel files (either stored locally or on OneDrive), PBIX files (build using Power BI Desktop) and create visualizations based on the data in the files.
Share content (dashboards etc.) with other users in the organization. Users just need a web browser to access and interact with the visualizations. Users with mobile devices can access the Power BI content using a browser on the mobile device or use Power BI app (native apps available for iOS, Windows, and Android devices).
Setup data-driven alerts and create annotations on mobile devices and sher with other users.
Schedule data refresh for online and on-premises data sources. By default, datasets from content packs are automatically updated once a day, but can also be refreshed manually or based on a schedule.

If you are still not clear on what Power BI Desktop is for and what Power BI Service is for, Power BI Desktop vs. Power BI Service might help.

Extensibility: Power BI is an extensible platform and provides the following ways to extend what is available out-of-the-box.

REST API: Power BI’s REST API library makes it a fully extensible platform for its customer to customize pretty much every component of Power BI, including connecting to custom data sources, enabling real-time data streaming from data sources to Power BI, and integrating other line-of-business applications with Power BI.
Open Source Visualizations: Microsoft has released an open source visualization project on GitHub to enable Developers to start building custom visualizations. This is an excellent move towards extensibility as customers will be able to extend out-of-the-box visuals and are not limited by what’s available within Power BI.
Custom Visuals Gallery: Microsoft lets Power BI community create and deploy custom visualizations to https://app.powerbi.com/visuals/, where other community members can download the visuals from and use it, free of charge.

Hope this is helpful.
Note: Images referenced in this blog post have been taken from Jen Underwood's Power BI Technical Reference Deck
Related links:

Power BI Basic Concepts

Power BI Pricing

Power BI Licensing Revisited

BACKUP DATABASE is terminating abnormally - Process 4424 generated fatal exception c0000005 EXCEPTION_ACCESS_VIOLATION. SQL Server is terminating this process

2015-10-18T10:41:00.000-04:00

One of my clients recently encountered this error (they are using SQL Server 2014). Their database backup job would fail with the following error:

Command: BACKUP DATABASE [Databas_Name] TO DISK = N'E:\Backup\Database_Name\FULL\DB_Name_FULL_20151013_210000.bak' WITH CHECKSUM, COMPRESSIONMsg 3013, Level 16, State 1, Server SERVER-NAME, Line 1 BACKUP DATABASE is terminating abnormally.

Also, the error log, referenced a dump file

Stack Dump being sent to E:\Databases\MSSQL10.MSSQLSERVER\MSSQL\LOG\SQLDump0169.txt

I opened up the dump file and saw the below message. As you can guess, there is nothing useful on what the error message is about and how to resolve it.

This file is generated by Microsoft SQL Server version 12.0.2000.8 upon detection of fatal unexpected error. Please return this file, the query or program that produced the bugcheck, the database and the error log, and any other pertinent information with a Service Request.

SqlDumpExceptionHandler: Process 4424 generated fatal exception c0000005 EXCEPTION_ACCESS_VIOLATION. SQL Server is terminating this process.

Exception Address = 000007FEF0A68D40 Module(sqlmin+0000000002C88D40)

Exception Code = c0000005 EXCEPTION_ACCESS_VIOLATION

Access Violation occurred reading address FFFFFFFFFFFFFFFF

As you can guess, there is nothing useful on what the error message is about and how to resolve it. I found a KB article that talks about this error and it suggested installing a Cumulative Update.

Resolution: Since SQL Server 2014 SP1 was available, I was more comfortable installing SP1 instead of a Cumulative Update. We installed SP1 and the issue got resolved.

Hope this helps. If you encountered this error and used to a different method to fix the issue, please let me know (via comments)

Excel Timeline Error: We can’t create a Timeline for this report because it doesn't have a field formatted as Date.

2014-12-31T13:33:00.000-05:00

You might get the error below when trying to insert a Timeline filter in an Excel Pivot table that is built off of an SSAS (SQL Server Analysis Services) Cube. Timeline filter is a new feature of Excel 2013 that can be used as a ‘visual filter’ to filter data in pivot tables.

We can’t create a Timeline for this report because it doesn't have a field formatted as Date.

It appears that the Timeline filter requires the MemberValue property of the key column in the date dimension to be a date field (date or datetime both works). So to resolve this issue, just point the ValueColumn property of the key column of your date dimension to a date field as shown in the screen shot below.

Also, please note that Timeline filter also requires the Type property of the date dimension to be set to Time. Hope this is helpful.

Could not find a log backup file that could be applied to secondary database

2014-12-27T17:00:00.000-05:00

Error: Could not find a log backup file that could be applied to secondary database 'Database Name'.(Microsoft.SqlServer.Management.LogShipping)

Error: The file 'F:\Database_Transaction_Log\Database_20130405231501.trn' is too recent to apply to the secondary database ‘Database Name'.(Microsoft.SqlServer.Management.LogShipping)

Error: The log in this backup set begins at LSN 2045263000000026000001, which is too recent to apply to the database. An earlier log backup that includes LSN 2045263000000023900001 can be restored. Searching for an older log backup file. Secondary Database: 'Database Name'

A few of my clients encountered this issue recently and needed help. Basically, they've setup Log Shipping on one of their production databases to copy transactions to a secondary server (for reporting purposes). The setup was working fine except for the fact that Log Sipping will break sporadically with the above error messages, and they had to ‘reset’ Log shipping to resolve the issue. Just by looking at the error message, I made a guess that this must be due to transaction log backups that are being performed “outside” of Log Shipping – in other words, I thought there must be a process separate from Log Shipping that is performing transaction log backups. The reason this is going to be an issue is because when applying/restoring transaction log backups on to the secondary server, Log Shipping is not going to be able find the log backup that was taken “outside” of Log Shipping – this means that there will be a ‘break’ in the chain/sequence of transaction log backups and hence a successful restore won't be possible. Below are three different scenarios I ran into with different clients.

Client/Scenario #1: This scenario was simple and it was easy to identify and resolve the issue: this client had a maintenance Job (outside of Log Shipping) that was backing up the transaction log on a regular basis. We simply disabled the maintenance job and the issue got resolved.

Client/Scenario #2: In this scenario, there was nothing within SQL Server and outside of Log Shipping that was backing up the transaction log. After a couple of discussions, I found out that they are using Avamar for backups and recovery purposes so I obviously wanted to know if they are using Avamar for transactions log backups. The answer was “No, We just do full backups”. But I wanted to confirm so we took a closer look at some of the settings in Avamar and found one that says “Force incremental backup after full backup” under the “Options” tab in the “Edit Dataset” window (shown in screenshot below). It sounded like this option does transactions log backups but to be 100% sure, we reached out to Avamar technical support and they confirmed that the setting below DOES transactions log backups. The support also said that if you include a database in Avamar’s backup plan, by default, it will perform transaction log backups as well (if the database is in FULL recovery model).

Long story short, we deselected the option "Force incremental backup after full backup" so Avamar stopped performing transaction log backups and Log Shipping continued to work without any issues.

Client/Scenario #3: This scenario is pretty much the same as that of Scenario #2, except that this client uses Veeam for backup and recovery. As in scenario #2, we explored some of the options in Veeam and found a similar setting (though not as obvious) called “Enable application-aware image processing” as shown in the screen shot below (the one on the left). Veeam technical support did confirm that this setting performs full as well as transaction log backups but if we just need to do full backups (without transactions log backups), we need to deselect the option “Backup logs periodically” under the advanced settings shown in the screen shot below on the right.

So we left the option “Enable application-aware image processing” selected but deselected “Backup logs periodically” and that took care of the issue with Log Shipping.

Even if you are not using Log Shipping, it is not a good idea to have two different process backup the transaction log of a given database as it can cause issues and confusion at the time restoring those backups. I hope this is helpful. If you've experienced this error and the reason/resolution was different than what I posted above, please leave a comment with your solution. Thanks.

Error when opening SQL Server Maintenance Plan: Value cannot be null

2014-07-31T12:04:00.000-04:00

One of our clients just called me with an issue they were having when opening a Maintenance Plan in SQL Server Management Studio. Below is a screen shot of the error message(and the error text)

Value cannot be null. Parameter name: component (System.Design). Microsoft SQL Server Management Studio is unable to load this document: Error loading XML. No further detailed error information can be specified for this problem because no Events object was passed where detailed error information can be stored.

As soon I saw the error(and the server version), I realized that this is a SQL Server 2012 instance and the client (a Network Admin) was trying to edit the maintenance plan using SQL Server 2008 R2 (Management Studio). I had him try using SQL Server 2012 Management Studio and it worked without any issue. SQL Server Tools in general, are not forward compatible and even if they let us connect to a later version of the software, it is not a good idea to make any changes (I wouldn't even recommend opening anything). Hope this helps.

On a side note, if you try doing something similar with Analysis Services(open SSAS 20012 database in on-line mode using BIDS 2008 R2 etc), you’ll get the error message below.

Deserialization failed: The 'AllowedRowsExpression' element in the 'http://schemas.microsoft.com/analysisservices/2011/engine/300/300' namespace is unexpected.

So the key takeaway of this post is to always try using the right version of the tool when connecting to SQL Server, SSAS, or SSRS for that matter. Hope this helps.

SQL Server restore error: Specified cast is not valid. (SqlManagerUI)

2013-04-07T22:45:00.001-04:00

I just encountered this error when restoring a SQL Server FULL backup file I got from one our clients - Specified cast is not valid. (SqlManagerUI)

This less-than-helpful error wasn't good enough for me to come to a conclusion so I decided to restore the database using a T-SQL command(Here is the command I used: RESTORE DATABASE CRM FROM DISK = 'E:\CRM.bak') and I got a different error which is shown below.

The media family on device 'E:\CRM.bak' is incorrectly formed. SQL Server cannot process this media family.

The first thing that came to my mind after looking at the above error was that the backup file is corrupted. So I decided to verify using VERIFYONLY and HEADERONLY commands and below is the output.

The two observations above made me lean more towards believing that the backup file is corrupted or incomplete, BUT the issue turned out to be something else – The backup I got from our client was taken on SQL Server 2012 instance and I was trying to restore it to SQL Server 2008R2. I had no idea about the version of SQL Server our client is using because they send us a backup file every quarter and we just restore it and generate audit reports for them.

I definitely think that the error message should have been more descriptive, but had it been so, I wouldn't have written this blog post. Hope this helps others.

Installation package could not be opened

2013-02-01T18:50:00.001-05:00

I just encountered the following error when installing SQL Server Upgrade Advisor on one of our client's production servers. This installation package could not be opened. Contact the application vendor to verify that this is a valid Windows Installer Package.

I tried several things such as restarting the Windows Installer service, registering Windows Installer, deleting the Windows Installer Package and downloading it again etc but nothing worked. Somehow, I had the feeling that this could be due to a failed network connection. So I downloaded the installer package on another machine(not on the same domain as the server), copied it to the server and ran it again and bingo, it installed successfully.

I hope this helps other folks who might run into the same issue.

SSAS - Duplicate attribute key found when processing

2013-01-26T18:15:00.000-05:00

I have been seeing this error time and time again when processing cubes in Analysis Services and thought it would be a good idea to blog about it. When processing Analysis Services cubes, you might see the following error:

Errors in the OLAP storage engine: A duplicate attribute key has been found when processing: Table: 'tblCustomers', Column: ‘Customer_Name’, Value: ''. The attribute is ‘Customer Name'.

As you can see, Analysis Services is complaining that there is a duplicate “blank” value in the column Customer_Name. How can there be a duplicate blank value as Analysis Services runs a SELECT DISTINCT query on the relational table to retrieve column values when processing? Well, a few other things happen “under the hood” when Analysis Services is processing. Let’s consider a scenario where we have the following values in our Customer table:

Customer_Id	Customer_Name
1	ABC
2	XYZ
3	NULL
4

When Analysis Services processes a dimension, it will execute a SELECT DISTINCT query to retrieve a list of all the distinct values for each attribute; in our example, it will retrieve 4 DISTINCT values – ABC, XYZ, NULL, ‘’ (the last one being empty string). Now the tricky thing is that Analysis Services will then convert the NULL value to an empty string and so the list of values after conversion would be - ABC, XYZ, ‘’, ‘’ (the last two being empty strings). Obviously, the distinct list of values for Customer Name aren’t distinct anymore as there are two empty strings. In other words, the NULL value is being converted to an empty string and we already have an empty string in the distinct list. This is when Analysis Services fails to process the dimension complaining that there is a duplicate value “”(empty string). This behavior is actually controlled by an attribute property called “NullProcessing” which is set to Automatic by default. Setting it to automatic means that Analysis Services will convert NULL values to “empty string” if the attribute is of type text and to 0 if the attribute is of type numeric.

Let me give you another example as shown in the table below, and this time, let’s consider a numeric attribute.

Customer_Id	Number_of_Bikes
1	4
2	10
3	NULL
4	0

In this case, the error would be - Errors in the OLAP storage engine: A duplicate attribute key has been found when processing: Table: 'tblCustomers', Column: ‘Number_of_Bikes’, Value: '0'. The attribute is ‘Number of Bikes’.

Notice that now, Analysis Services is complaining that there is a duplicate zero value. When Analysis Services processes this dimension, it will come up with the following DISTINCT list of values - 4, 10, NULL, 0. It will then convert NULL to 0(zero) since the attribute is numeric. So the list of DISTINCT values after converting NULL to 0 would be - 4, 10, 0, 0. As you can see, the list is not distinct anymore and so Analysis Services would fail to process the dimension.

One of the easiest ways to resolve this is to convert NULL values in your data source to blank or zero if the attribute is text or numeric respectively. For example, you can use ISNULL(Customer_Name, ‘’) in your data source view to convert NULL value to an empty string(if the attribute is text) and use ISNULL(Number_of_Bikes, 0) to convert NULL value to 0( if the attribute is numeric).

Just a side note that you’ll see some other errors when this issue occurs and in most of the cases, these errors would be generic and less than helpful. I have seen the following errors when this issue occurs:

“Errors in the OLAP storage engine: An error occurred while the dimension, with the ID of 'Customer', Name of 'Customer' was being processed”

"OLE DB error: OLE DB or ODBC error: Operation canceled; HY008."

While the first error is generic, the second one could be due to various reasons including the one we discussed. Please see this post for the other possible reasons for the second error – Analysis Services Cube processing fails with error "OLE DB error: OLE DB or ODBC error: Operation canceled; HY008.

Hope this helps!

Microsoft Dynamics Great Plains - Annual Historical Usage query

2012-11-09T22:18:00.003-05:00

One of my clients recently asked me to create an Inventory report off of Microsoft Dynamics GP(Great Plains) . One of the fields he wanted to include in the report was Annual Historical Usage that shows up in the Item History window as shown below. I finished the report in a couple of hours but during testing, the Annual Historical Usage in my report was not tying with what shows up in GP in the Item History window. After running some ad-hoc queries, I was able to figure out what was going on and want to share the solution here on my blog.

Below is the query I used to pull Annual Historical Usage. You can run it for a specific item number or all item numbers. The trick here is to figure out how GP calculates this number – GP calculates it by summing up transactions starting from the first day of last year’s current month to the last day of current year's previous month. For example, if the current month is November 2012, then the range would be 2011-11-01 to 2012-10-31.

SELECT SD.ITEMNMBR,

SUM(CASE SD.SOPTYPE WHEN 3 THEN SD.QTYFULFI*QTYBSUOM

WHEN 4 THEN SD.QUANTITY*QTYBSUOM*-1

END) Annual_Historical_Usage

FROM SOP30200 SH

INNER JOIN

SOP30300 SD

ON SD.SOPNUMBE = SH.SOPNUMBE

AND SD.SOPTYPE = SH.SOPTYPE

WHERE SH.VOIDSTTS = 0

AND SH.SOPTYPE IN (3,4)

AND SH.DOCDATE BETWEEN DATEADD(MONTH, DATEDIFF(MONTH, '19010101', GETDATE()), '19000101') AND DATEADD(MONTH, DATEDIFF(MONTH, '19000101', GETDATE()), '18991231')

--AND SD.ITEMNMBR IN (@ItemNumber)

AND SH.LOCNCODE = 'AUS'

GROUP BY SD.ITEMNMBR

I hope this is helpful.

Customizing Report Manager Application Title

2012-10-08T12:35:00.000-04:00

You can customize the Report Manager application title that appears at the top of the page. By default, the title is SQL Server Reporting Services. Follow the steps below to change the title.

1. In Report Manager, click on Site Settings.

2. On the General tab, in the Name section, replace SQL Server Reporting Services with a different name.

3. Click Apply.

Please note that you need to be assigned to the System Administrator role to set properties on the Site Settings page.

Hope this helps!

OLE DB or ODBC error:Login failed for user NT Service\MSSQLServerOLAPService

2012-09-22T15:03:00.001-04:00

You might get the error: OLE DB or ODBC error: Login failed for user 'NT Service\MSSQLServerOLAPService' when processing a cube/dimension. Here is what causes this error and how you can resolve the problem:

SQL Server Analysis Services will use the service account(by default) to pull data from the data source (the database server that hosts the data). If your Analysis Services instance is running under the account 'NT Service\MSSQLServerOLAPService', then this account will be used(by default) to read data from the databases and if the account doesn't have read access, you will get the above error. To resolve this, just grant the account read access to the relational database(s) you are trying to pull data from.

Also, please note that it is recommended to setup the data sources to use a domain account and to setup Analysis Services to run under a domain account. More details on data source impersonation options can be found here - http://technet.microsoft.com/en-us/library/ms187597.aspx

PowerPivot add-in installation error

2012-07-04T15:29:00.003-04:00

You might encounter the error below when installing PowerPivot add-in for Excel.

Setup is missing prerequisites: This add-in requires Excel 2010. If you have 32-bit Excel, you must install the 32-bit version of the add-in. If not, you must install the 64-bit version. Go to http://go.microsoft.com/fwlink/?LinkId=168472

In more than 50% of the scenarios that I have encountered, the issue was that the user was not installing the right add-in. For example, you would get the error if you are installing a 64-bit version of PowerPivot on a 64-bit machine that has 32-bit version of Excel 2010. When installing PowerPivot add-in for Excel, you don’t look at whether you have a 32-bit machine or a 64-bit machine, instead, you should look at whether you have 32-bit Excel or 64-bit Excel. In other words, if you are using the 32-bit version of Excel 2010, you must use the 32-bit version of PowerPivot and if you are using the 64-bit version of Excel 2010, you must use the 64-bit version of PowerPivot.

In case the above solution doesn’t work, you can try the suggested resolution in the following KB article - http://support.microsoft.com/kb/982765 or the following on MSDN forums http://social.msdn.microsoft.com/Forums/en-US/sqlkjpowerpivotforexcel/thread/2ca95318-51de-4603-9d57-8645c164b307/

Hope this is helpful!

Start/Stop SQL Server from command line

2012-04-08T12:18:00.002-04:00

You can using the following script to start/stop SQL Server services using command line:

SQL Server Database Engine(both SQL Server service and SQL Server Agent):

NET STOP SQLSERVERAGENT

NET STOP MSSQLSERVER

NET START MSSQLSERVER

NET START SQLSERVERAGENT

SQL Server Reporting Services:

NET STOP ReportServer

NET START ReportServer

SQL Server Analysis Services:

NET STOP MSSQLServerOLAPService

NET START MSSQLServerOLAPService

PowerPivot Tutorials

2012-04-07T14:09:00.004-04:00

I have put together a list of resources that can be used to get up to speed with PowerPivot. Hope this is helpful.

BI PowerPivot "How Do I?" Videos - http://technet.microsoft.com/en-us/bi/ff966490

Description: Includes several PowerPivot videos such as Introduction to PowerPivot Relationships, Overview of Importing Data into PowerPivot, Import SQL Server Data into a PowerPivot Workbook, Add a Calculated Column to a PowerPivot Workbook, Using Slicers with PowerPivot PivotTables/Charts, PowerPivot for Excel and SharePoint and a lot more.

MSDN Virtual Lab: Creating Reports with SQL Server PowerPivot for Excel 2010 - https://cmg.vlabcenter.com/default.aspx?moduleid=82873ec9-8de5-4eed-a23f-a1ba11cc576e

Description: After completing this lab, you will be better able to create a PivotTable report based on a PowerPivot model, format the report using conditional formatting, enhance the report with Slicers, and refine the PowerPivot model.

MSDN Virtual Lab: Building and Sharing Your First SQL Server 2008 R2 PowerPivot for Excel Workbook - https://cmg.vlabcenter.com/default.aspx?moduleid=94c5a946-aab0-4dc0-a143-b507fe799504

Description: After completing this lab, you will be better able to import data into PowerPivot, create table relationships, create calculated columns, create a PivotTable report, create measures, format the PivotTable report, and publish the PowerPivot workbook to SharePoint.

MSDN Virtual Lab: PowerPivot in SQL Server 2008 R2 - https://cmg.vlabcenter.com/default.aspx?moduleid=ee755517-6658-4f86-b24d-a42ab3f9169a
Description: After completing this lab you will be better able to use various methods of loading data into PowerPivot for Excel and structuring it for analysis, create and use PivotTables against PowerPivot data, understand DAX concepts and several basic DAX functions, and publish PowerPivot workbooks to Excel Services and understand their server-based structure.

MSDN Virtual Lab: Loading and Preparing Data for Analysis in SQL Server PowerPivot for Excel 2010 - https://cmg.vlabcenter.com/default.aspx?moduleid=35f7087d-5f43-4c5e-acb1-da9679a5969c

Description: After completing this lab, you will be better able to create a PowerPivot model, load data from different data sources into PowerPivot tables, and create relationships between PowerPivot tables.

TechNet Virtual Lab: Power Pivot - https://cmg.vlabcenter.com/default.aspx?moduleid=dbe037bc-9004-4a38-883f-696d507a1b66

Description: After completing this lab, you will be better able to import SQL Server data into PowerPivot, create a sales report using PowerPivot data, and import data from a web site into PowerPivot.

Deep Dive on PowerPivot Technologies - http://channel9.msdn.com/Events/TechEd/NorthAmerica/2010/BIE401

SQL Server Upgrade Advisor: Custom extensions or assemblies were detected on Report Server

2012-04-07T11:28:00.001-04:00

You might see the following error when running SQL Server Upgrade Advisor on a SQL Server 2005/2008 instance.

Custom extensions or assemblies were detected on the Report Server.

Upgrade Advisor detected one or more custom data processing extensions on the Report Server. Upgrade can continue, but you must move the data processing extension assemblies to the new installation folder.

As the message indicates, this error simply means that you have custom extensions or assemblies on the Report Server you are upgrading. In most of the cases, you can resolve this by copying the custom extensions/assemblies from the “old” Reporting Services installation folder to the “upgraded” Reporting Services installation folder.

To give you a real example, I encountered this error when upgrading a SQL Server 2008 instance to SQL Server 2008 R2. The instance I was trying to upgrade had some Microsoft CRM Data Extension Assemblies as shown in the screen shot below. After the upgrade, I copied the DLLs to the new installation folder and everything was all set.

Here are the paths to SQL Server 2008 and SQL Server 2008 R2 installation folders for Report Server.

\Program Files\Microsoft SQL Server\MSRS10.{InstanceID}\Reporting Services\ReportServer\Bin ----- SQL Server 2008 Report Server

\Program Files\Microsoft SQL Server\MSRS10_50.{InstanceID}\Reporting Services\ReportServer\Bin ----- SQL Server 2008 R2 Report Server.

Analysis Services error: The size specified for a binding was too small

2012-03-27T12:18:00.000-04:00

Analysis Services error: Errors in the back-end database access module. The size specified for a binding was too small, resulting in one or more column values being truncated.

This error occurs when the size(number of characters) of an attribute exceeds the DataSize defined in the attribute properties of the dimension. By default, Analysis Services sets the size of an attribute based on the size defined in the relational database.

For example, let’s say you have a field called CustomerID which is defined as VARCHAR(4) in the database. If you use this field as an attribute in a dimension, Analysis Services will set the DataSize property to 4. You can see this property by expanding the NameColumn and KeyColumns property of the attribute.

Now, if you replace the dimension in Analysis Services with a named query and edit the CustomerID field with something like CASE WHEN CustomerID IS NULL THEN ‘Not Defined’ ELSE CustomerID END AS CustomerID, the cube fail to process because Analysis Services has already set the data size for CustomerID to 4 but we just violated this by using the value ‘Not Defined’ which is of length 12.

To resolve this issue, all we need to do is increase the value of the property called DataSize for the attribute under both NameColumn and KeyColumns.

Hope this helps.