tag:blogger.com,1999:blog-41577876416490766742024-10-25T04:48:17.884-07:00 Expedition to SQL and Business Intelligence WorldAnonymoushttp://www.blogger.com/profile/02206793964922878189noreply@blogger.comBlogger64125tag:blogger.com,1999:blog-4157787641649076674.post-88565783711305968802018-11-17T10:09:00.000-08:002018-11-21T12:23:27.486-08:00

With so many exciting features that Azure offers such as Azure Stream Analytics and Azure Machine Learning, many friends and colleagues asked me about how to get started with Azure so that they can having some feelings about Azure. I highly suggest them reading "Azure Readiness Playbook", the official guidelines that came out in October 2018 to help you configure Azure environment. You may know

Anonymoushttp://www.blogger.com/profile/02206793964922878189noreply@blogger.com0tag:blogger.com,1999:blog-4157787641649076674.post-52035629191360923572018-11-04T14:01:00.000-08:002018-11-15T06:08:07.397-08:00

Power BI has three connection modes to data sources, including "Import", "DirectQuery", and "Live Connection". Depending on types of data sources, you have different available options on connection modes. Sometimes you might get confused about which mode to choose from. This article will help you understand what are advantages and limitations for each mode. What are different connection modes

Anonymoushttp://www.blogger.com/profile/02206793964922878189noreply@blogger.com0tag:blogger.com,1999:blog-4157787641649076674.post-91205835421909615602018-10-14T13:19:00.002-07:002018-10-14T16:23:17.862-07:00

As part of expressions, decision functions can be used in calculated fields, filters, formatting to dynamically control related properties. They can be used with SQL, MDX, DAX query to query data from database, cube, SSAS tabular model respectively. This article will give a brief introduction and comparison of three decision functions: IIf, Switch, Choose. Also examples of their usage

Anonymoushttp://www.blogger.com/profile/02206793964922878189noreply@blogger.com0tag:blogger.com,1999:blog-4157787641649076674.post-5945927901810828492018-10-06T09:10:00.000-07:002018-10-29T14:06:23.275-07:00

Problem: When SSIS packages failed at the step of loading files, sometimes it doesn't tell which column is the one that caused failure. How to find out the troublemaker? Solution:  If you have SQL Server 2016 Integration Service or later version, you can retrieve error column names by using the new GetIdentificationStringByID method in SSIS 2016. Below is an example showing how to

Anonymoushttp://www.blogger.com/profile/02206793964922878189noreply@blogger.com0tag:blogger.com,1999:blog-4157787641649076674.post-45408777217328846152018-09-22T10:05:00.000-07:002018-10-30T11:05:19.741-07:00

Problem: During development of SSIS packages for data transferring between Azure Storage, you want it to be fully tested on your local first before any data transfers to your Azure account. You also want to avoid unnecessary charges on your Azure account for testing. How to achieve this? Solution: Using Azure storage emulator We know that Azure storage is charged based on volume of data stored

Anonymoushttp://www.blogger.com/profile/02206793964922878189noreply@blogger.com0tag:blogger.com,1999:blog-4157787641649076674.post-3813275133624819752018-09-01T11:52:00.000-07:002018-11-06T10:35:20.527-08:00

Problem: When you would like to loop through result sets stored in an object variable, you probably will use Foreach Loop Container in SSIS. If you need to read multiple fields from object variables to use in the down stream later, how to implement it? Solution:  To specify the mapping by indexes in "Variable Mapping" of Foreach Loop (FEL) container. Here we use a dynamic parent-child

Anonymoushttp://www.blogger.com/profile/02206793964922878189noreply@blogger.com0tag:blogger.com,1999:blog-4157787641649076674.post-83773076608131373662018-02-03T10:53:00.000-08:002018-11-02T07:09:06.641-07:00

Problem: You have text files in csv or AVRO formats and you would like to upload them to Blob storage as intermediate steps of big data processing in Azure. How to upload them to Azure Blob storage? Solution:  Use "Azure Blob Upload Task" or  "Azure Blob Destination" in Data Flow Task in Integration Service in SQL Server (SSIS). Before we open new Integration Service project

Anonymoushttp://www.blogger.com/profile/02206793964922878189noreply@blogger.com0tag:blogger.com,1999:blog-4157787641649076674.post-81967453969871965642018-01-01T10:51:00.000-08:002018-10-29T14:04:36.884-07:00

Problem: After installation of Azure feature pack for SSIS, Azure related toolboxes are still grey out? Solution:  If Azure related toolboxes are grey out in SSIS, that means that the version of SQL server is different from the version of Azure feature pack. You need to change the TargetServerVersion in visual studio to the same version of Azure feature pack you installed. For

Anonymoushttp://www.blogger.com/profile/02206793964922878189noreply@blogger.com0tag:blogger.com,1999:blog-4157787641649076674.post-23373100977068479082017-11-04T09:28:00.000-07:002018-11-15T09:50:07.928-08:00

When you follow the steps in my previous post "How to: Enable PowerPivot Add-ins for Excel 2013" to add PowerPivot in excel with Office 365, you may not be able to find PowerPivot in available COM add-ins. You may wonder whether you missed any steps? That may not be the case. From "Feature availability across Office 365 plans", you will know PowerPivot feature is only available for the

Anonymoushttp://www.blogger.com/profile/02206793964922878189noreply@blogger.com0tag:blogger.com,1999:blog-4157787641649076674.post-2838360002675224422014-11-19T09:04:00.001-08:002014-11-19T12:27:48.257-08:00

Have you ever met the following scenarios: 1) when you write a very complex ad-hoc query in SQL Server Management Studio (SSMS), you need to count the total columns that have been derived. 2) You even need to refer to the line that appears in the error message. If yes, you need to enable the option of showing line numbers in SSMS. In fact it is very simple. It can be done in a minute. You just

Anonymoushttp://www.blogger.com/profile/02206793964922878189noreply@blogger.com0tag:blogger.com,1999:blog-4157787641649076674.post-65510442138779799462014-10-21T13:06:00.000-07:002014-11-19T12:25:11.206-08:00

When we work with PivotTables, sometimes we don’t want a spreadsheet to be a table any more. How to remove table format from it and convert it to a normal range? Below will show the way to achieve it in Excel 2013. Step 1: Remove any table style Select any cell in your targeted table till Design Tab appears under TABLE TOOLS. Choose the clear style as shown: Step 2: Convert to a normal range

Anonymoushttp://www.blogger.com/profile/02206793964922878189noreply@blogger.com0tag:blogger.com,1999:blog-4157787641649076674.post-54801123278391299172014-09-23T13:43:00.000-07:002014-11-19T14:04:58.211-08:00

Problem: Whenever you have similar error messages during data loading by SSIS, it indicates that you have issues between data type conversion. How to find those outliners / failed conversion out? [OLE DB Destination [2]] Error: SSIS Error Code DTS_E_OLEDBERROR.  An OLE DB error has occurred. Error code: 0x80004005.An OLE DB record is available.  Source: "Microsoft SQL Server Native

Anonymoushttp://www.blogger.com/profile/02206793964922878189noreply@blogger.com0tag:blogger.com,1999:blog-4157787641649076674.post-20708736351043827462014-06-30T19:59:00.000-07:002014-07-11T19:59:37.043-07:00

ISNULL is a T-SQL function to replace NULL values as COALESCE does. At some cases these two functions get the same results but there are some differences. Below is a summary of their differences and I hope that it will help you decide which function to use when you need to determine the one with better performance or better concurrency control. COALESCE ISNULL Standard ANSI SQL? Yes No (Only

Anonymoushttp://www.blogger.com/profile/02206793964922878189noreply@blogger.com0tag:blogger.com,1999:blog-4157787641649076674.post-60336629450677496082014-06-16T19:42:00.000-07:002014-06-21T19:44:30.806-07:00

Just like hierarchies in SSAS, hierarchies in PowerPivot for Excel 2013 provides a very convenient way to let you group data through different layers. Today we will use date dimension to demonstrate how to create hierarchies in PowerPivot. First of all, you need to be in the Diagram View of the PowerPivot Window. Click Diagram View button under Home Tab. Create Hierarchies Select the table you

Anonymoushttp://www.blogger.com/profile/02206793964922878189noreply@blogger.com0tag:blogger.com,1999:blog-4157787641649076674.post-20653327484964931362014-06-02T04:32:00.000-07:002014-06-21T05:17:51.532-07:00

Power Pivot in Microsoft Excel 2013 is a build-in add-in you can use to perform powerful data manipulation and analysis in Excel 2013. However, it is hidden somewhere and you have to enable it first before using it. Here are steps to dig it out: Go to File > Options > Add-Ins. Change In the Manage box, click COM Add-ins from the drop down list and click Go. Note that the default is

Anonymoushttp://www.blogger.com/profile/02206793964922878189noreply@blogger.com0tag:blogger.com,1999:blog-4157787641649076674.post-13873974815606100882014-05-30T12:18:00.000-07:002014-05-30T12:49:11.169-07:00

It is common practice to extract only date part from datetime format, such as for a datetime of 2014-05-30 15:31:58.860, only 2014-05-30 is returned. Today we will show three main methods to choose from. Method 1: SELECT convert(DATE, GETDATE() ) Method 2:SELECT CONVERT(VARCHAR(10),GETDATE(),126) Method 3:SELECT DATEADD(D, 0, DATEDIFF(D, 0, GETDATE())) will mask the time part as 00:00:00.000.&

Anonymoushttp://www.blogger.com/profile/02206793964922878189noreply@blogger.com0tag:blogger.com,1999:blog-4157787641649076674.post-29597693309093789272014-03-18T08:59:00.000-07:002014-03-21T06:16:05.190-07:00

Problem When you are trying Bulk insert from csv file, here is the error message you may run into: “Could not bulk insert because file 'C:csv_filename.txt' could not be opened. Operating system error code 3(The system cannot find the path specified.).” Solution First of all, confirm that you are specifying the UNC (Universal Naming Convention) path and not just drive letters. If you are trying

Anonymoushttp://www.blogger.com/profile/02206793964922878189noreply@blogger.com0tag:blogger.com,1999:blog-4157787641649076674.post-31440290729796205012014-03-16T13:05:00.000-07:002014-03-16T13:09:54.979-07:00

Problem When you tried to create a new mining structure in SSAS, you may notice a warning message appears in the window as shown below: “Unable to retrieve a list of supported data mining algorithms. Make sure you are connected to the correct Analysis Services server instance and the Analysis Services server named localhost is running and configured properly. You can continue with a default list

Anonymoushttp://www.blogger.com/profile/02206793964922878189noreply@blogger.com0tag:blogger.com,1999:blog-4157787641649076674.post-86062633092397543112014-03-14T12:44:00.000-07:002014-03-16T17:10:46.940-07:00

Today we will briefly discuss the difference between star and snowflake schemas, and what you should consider when you make decisions for a data warehouse design. What are star schemas and snowflake schemas? In dimensional data warehousing, star schemas represent a dimensional model which is composed of a central fact table and multiple “de-nomalized” dimension tables around the fact table. The

Anonymoushttp://www.blogger.com/profile/02206793964922878189noreply@blogger.com0tag:blogger.com,1999:blog-4157787641649076674.post-61870995113953051852014-02-26T11:52:00.000-08:002014-02-26T11:55:11.145-08:00

2014 Gartner magic quadrant report for business intelligence and analytics platform was released last week.  The report rates the strengths and weaknesses of 27 vendors in the market according to 17 defined capabilities across three categories: information delivery, analysis and integration.  Below is a snapshot of vendors’ performance charted on a graphical quadrant.   Who are

Anonymoushttp://www.blogger.com/profile/02206793964922878189noreply@blogger.com0tag:blogger.com,1999:blog-4157787641649076674.post-5505549649687371412014-02-24T08:32:00.000-08:002014-02-26T10:16:39.524-08:00

Using hash functions in sql server for incremental data loading has a big performance advantage when you have millions of rows to load, or have several dozens of columns to compare and make decision on whether to update, insert, or expire, as mentioned by Andy Leonard's Anatomy of an Incremental Load. Brett Flippin has introduced the way to calculate hash columns with SSIS’s Script Components in

Anonymoushttp://www.blogger.com/profile/02206793964922878189noreply@blogger.com0tag:blogger.com,1999:blog-4157787641649076674.post-86107746514464394692014-02-17T09:42:00.000-08:002014-02-20T09:44:20.240-08:00

When you carry out database migration, one of main headaches is the conversion of different data types between sources and destinations. If you are using SSIS, be careful about the conversion between strings and int. Sometimes SSIS will fail because the integer type defined is not big enough to hold the original data. It is also known as overflow problem. So here is a summary that indicates which

Anonymoushttp://www.blogger.com/profile/02206793964922878189noreply@blogger.com0tag:blogger.com,1999:blog-4157787641649076674.post-18414522195054638482014-02-10T06:05:00.000-08:002014-02-10T06:16:02.331-08:00

Have you ever heard of Triple Aim in healthcare? If not, you may notice that private and public entities around the world are investigating how to maximize the power of analytics to improve quality of care, operational efficiencies, billing reimbursements with a lower cost. United States spent about 17.9% of GDP on healthcare in 2011, according to World Health Organization’s report. With such a

Anonymoushttp://www.blogger.com/profile/02206793964922878189noreply@blogger.com0tag:blogger.com,1999:blog-4157787641649076674.post-60829374818898937382014-02-08T04:42:00.000-08:002014-02-08T04:53:16.764-08:00

If you would like to take a peek about the coming PASS Business Analyics Conference in San Jose, CA, May 7-9, 2014; If you would like to learn more about how to use Power Query, PowerPivot, Power View, and Power Map to discover and demonstrate business insights; If you would like to learn something about big data or predictive analysis/modeling; here is a valuable resource that you should not

Anonymoushttp://www.blogger.com/profile/02206793964922878189noreply@blogger.com0tag:blogger.com,1999:blog-4157787641649076674.post-36744048051047666842014-02-06T09:54:00.000-08:002014-03-19T11:31:59.655-07:00

Data analysis and cleansing are the most time consuming parts when you want to load source data, or prepare sampling data for data mining. You can write TSQL to find out distinct values or lengths of a column that are existing in the testing data. However, when you have millions of rows or more than thirty columns for analysis, TSQL scripts might take a whole day to get the answer. Since SQL

Anonymoushttp://www.blogger.com/profile/02206793964922878189noreply@blogger.com0