After a long pause I’m back again to discuss on Power BI.
In my previous Power BI Series first part [link] I discussed about the first component of Power BI, i.e. Power Query and how to use it to discover and gather data.
Power Pivot lets you:
1. Create your own Data Model from various Data Sources, Modeled and Structured to fit your business needs.
2. Refresh from its Original sources as often as you want.
3. Format and filter your Data, create Calculated fields, define Key Performance Indicators (KPIs) to use in PivotTables and create User-Defined hierarchies to use throughout a workbook.
And here in second part I will discuss about few of these features.
–> The benefit of creating Data Model in Power Pivot is that Power Pivot Models run in-memory so that users can analyze 100’s of millions of rows of data with lightning fast performance.
All you need is Microsoft Excel 2013 to create your Data Model. Check this [link] to troubleshoot if you don’t see POWERPIVOT option in Excel ribbon.
–> Creating Data Model:
To create a Data Model you need a Data Source, so we will use SQL Server as a Data Source and I’ve setup AdventureWorksDW2012 Database for our hands-on. Click [here] to download AdventureWorksDW2012 DB from CodePlex.
2. Now on this new window, click on From Database icon and select From SQL Server from the dropdown, this will open a Table Import Wizard Popup window.
–> Now as your Data Model is ready, you can create Pivot Reports in Excel, let’s see how:
2. The control moves to the Excel sheet, select Existing Worksheet on the Popup.
3. Now select following columns form the PivotTable Fields list:
This would give you Total sales across Regions in the Worksheet
4. Let’s add some Slicers to this:
4.a. Click on PIVOTTABLE TOOLS – ANALYZE, here click on Insert Slicer. On ALL tab, select DimDate.FiscalYear column. This will add Year slicer to the report.
4.b. Now again click on the PivotTable Report, you will see the PIVOTTABLE TOOLS on the ribbon bar again. Select Insert Slicer again and select DimProductCategory.EnglighProductCategoryName column.
This way you can add Graphs, Charts and create very impressive Reports UI as per your requirements.
This Power Pivot – Data Model can also be used to create Power View Reports, which we will cover in next part of this series.
Importing records from an Excel is a very simple task. Load your data in Excel with appropriate headers and Run the Import/Export Wizard, your records are transfered from Excel sheet to a MS SQL table.
But what if a single Sheet contains multiple record sets with variable headers. Like First 100 rows of Customer data with 10 headers. Then just below 50 rows of Order data with less than 10 or more than 10 headers.
Seems a bit difficult but not impossible. Its tricky though, lets see how:
Lets us suppose your Excel file is in following format shown in image below (Fig-1):
1. Contact recordset &
2. Sales Order recordset
Now select the Contact recordset including headers as shown in Fig-2 and right-click and select “Name a Range…” option.
Fig-3 shows a pop-up box where you can apply and provide a name for that range selection.
Similarly repeat this for Sales Order recordset as shown in Fig-4.
Now you can see 2 named ranges Contacts & Sales in the dropdown in Fig-5.
Now we are ready to Import the data. As shown in Fig-6 the Import/Export wizard detects the named ranges and explicitly shows them as Source among other 3 sheets of your Excel file. Simply select both of them and make the required changes as you do for Sheets and click the Next button.
Fig-7 Shows both the tables created & data loaded in SQL Server.
Now check the records and match them with the Excel sheet, as shown in Fig-8
Wow!!! that was simple.
I was asked this question in an SQL interview and I didn’t knew the answer, obviously you don’t need to know everything… but you should. Discussed this question on MSDN TSQL forum and got the suggestion, thus the blog post. Link: http://social.msdn.microsoft.com/Forums/en-US/transactsql/thread/cf849418-d18f-4b7a-99eb-dbfed6269603/#778c0e99-368a-40f2-b9d4-b747c1754853
You have an important lead set or any other orders/financial recordset in excel and have to import the data in SQL Server. This is not a one time effort but ongoing and in future you have to deals with lot such records/data files. To import valid data in your SQL tables you want to ensure and validate at the frst step instead of correcting the data by firing SQL queries. Also to avoid this manual work to identify the incorrect/invalid data out of thousands of records automated approach would be quite helpful.
Excel not only store data but also have a power to run VBA macro code against and manipulate the data. Lets take a simple example to validate a set of Customer numbers & their insert date, shown below:
-- Test Data which is correct and expected to import in SQL table. InsertDate CustomerNo 11/16/2009 91878552 11/16/2009 101899768 11/16/2009 101768884 11/16/2009 123456789123 11/16/2009 101768800
Columns InsertDate should be a valid date & CustomerNo should be numeric & less than 12 characters or max length equal to 12.
-- Lets tweak the test data put some invalid values under both the columns, shown below: InsertDate CustomerNo 11/16/2009 91878552 51/96/2009 101899768 11/16/2009 1017abc84 11/16/2009 123456789123 11/16/2009 101768800
If you are on MS Office 2007 create a Macro enabled excel file with extension *.xlsm, OR if you are on 2003 and lower version *.xls file would be fine. Copy the above data in Sheet1 with incorrect values. Now press open up the VBA macro editor or press ALT+F11 keys. Now double click on Sheet1 under “Project -VBAProjects” explorer.
I wish to run the validation while someone tries to save the macro enabled excel file. So the event selected is “BeforeSave”. And the VBA macro code goes below:
Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean) Dim errorCode As Integer Dim errorMsg As String Dim numOfRecs As Integer Dim counter As Integer '--------------------- 'Start - Validate Date '--------------------- Sheet1.Activate Range("A2").Select errorCode = 0 numOfRecs = 0 Do Until ActiveCell.Value = vbNullString ' Validate Date If IsDate(ActiveCell.Value) <> True Then errorCode = 1 ActiveCell.Interior.ColorIndex = 3 'Red Else ActiveCell.Interior.ColorIndex = 2 'White End If numOfRecs = numOfRecs + 1 ActiveCell.Offset(1, 0).Select Loop If errorCode = 1 Then errorMsg = errorMsg + vbCrLf & "- Invalid Insert Date" End If '------------------- 'End - Validate Date '------------------- '-------------------------- 'Start - Validate Customer# '-------------------------- Sheet1.Activate Range("B2").Select errorCode = 0 Do Until ActiveCell.Value = vbNullString ' Check for a valid Customer Number If IsNumeric(ActiveCell.Value) <> True Or Len(ActiveCell.Value) > 12 Then errorCode = 1 ActiveCell.Interior.ColorIndex = 3 'Red Else ActiveCell.Interior.ColorIndex = 2 'White End If ActiveCell.Offset(1, 0).Select Loop If errorCode = 1 Then errorMsg = errorMsg + vbCrLf & "- Invalid Customer #" End If '------------------------ 'End - Validate Customer# '------------------------ ' Go to first cell. Range("A1").Select 'If errors then display the error msg and do not save the file otherwise save the excel file. If errorCode = 1 Then MsgBox "Workbook not saved. Following are the fields that contain invalid values." _ & vbCrLf & errorMsg & vbCrLf & vbCrLf & _ "Please correct the values highlighted in RED color.", vbCritical, "Data Validation ERROR" Cancel = True End If End Sub
Now save the macro code and close the editor. Now try to Save the Excel file, you will get the cells with invalid values highlighted in RED, as shown in the image. The excel file not be saved and when closing you will get the dialog box to save it again & again.
In order to save it correctly you need to correct the invalid values, i.e. InsertDate & CustomerNo, and then save it. The RED highlighted cells will become WHITE for the correct values.
This excel file is now very much validated and clean to import in SQL Server or any other database.
Try this code and tweak it as per your needs.
I’ve also discussed this logic in MSDN’s TSQL forum at following link: http://social.msdn.microsoft.com/Forums/en-US/transactsql/thread/09299d7d-9306-4ea4-bb29-87207572aa04
Suggestions & comments are welcome!!!
Steps to create a Pivot Report
1. Open Excel Workbook, assuming that we are on sheet1.
7. Now Click on OK button of the “Create New Data Source” window as shown at step-4.
Leave the 4th step for the default table.
When you are done with the query, go to the File menu and select “Return Data to Microsoft Office Excel”.
Also you can apply filters and formulas to the PivotTable report.
17. The final PivotTable report is here.
I’ve chosen 4 columns from the table (as shown inside the Row Labels box) and the 5th column is the derived column, count of Customers/Records (as shown inside the Value box).