SQL Server 2016 Discovery Day – Data Visualization using R and Power BI.

SQL Server 2016 Discovery Day – Data Visualization using R and Power BI.

Last week we conducted the SQL Server 2016 Discovery Day –Release Event Kathmandu, Nepal successfully on July 9^th 2016. SQL Server Launch 2016 Event and Discovery Day is a free, one-day event where individuals come together, learn about SQL Server 2016 and solve a pre-determined problem.

https://www.eventbrite.com/e/sql-server-2016-discovery-dayrelease-event-kathmandu-nepal-tickets-25888946536?aff=efbnreg#

I got a chance again to speak in this great event and it was fun and share knowledge along with other SQL Geeks, I had given presentation on

SQL Server 2016 Discovery Day – Data Visualization using R and Power BI.

You can find my slides using below link:

SQL Server 2016 Discovery Day – Data Visualization using R and Power BI. from Anil Maharjan

Also, we had a small solution development competition in the event where we use Power BI Desktop free tool in order to create some visualization and tell some story behind data.

Below is some quick visualization that I had created in the event by using PASS SQL Saturday real Data.

Steps for Making Data visualization by using Power BI Desktop.

Step 1: Firstly, download and install the Microsoft Power BI Desktop tool, which is free from the link below also for R related charts and play around with R codes we need to download R , RStudio IDE and install too. Below is link for Power BI Desktop, R, and RStudio IDE.

https://powerbi.microsoft.com/en-us/desktop/?gated=0&number=1

https://www.r-project.org

https://cran.r-project.org/src/base/R-3/

https://www.rstudio.com/products/rstudio/download/

Step 2: Get the data related to SQL PASS, SQL Saturday from the link below:

https://drive.google.com/file/d/0BzlPwGX6UtxUNnlfZ01KczF0NHc/view

This zip data files contains the different .sql files with data included within scripts.

Step 3: Load those scripts into SQL Server Database and then use Power BI Desktop ‘Get DATA’ tab to load the Data into Power BI Desktop and do visualization.

Alternative: If you haven’t install the SQL server Database then you can use a single file such as ‘dbo.SQLSatSessions.Table.sql’ and then Excel file to manipulate the data in your required format.

Step 4: Load the only data part from the file ‘dbo.SQLSatSessions.Table.sql’ into excel as below

Then use Text to Columns tab in DATA tab section as below to make the data into proper column format.

Once you prepare your data as below format then now you can start analysis using Power BI Desktop.

Step 5: Use Power BI step 3 or Step 4 to load the data into Power BI. We have use step 4 here so now use Power BI to load data from Excel source then you will get data columns in right hand side as below.

Step 6: Now start visualization using Power BI Desktop free tool, here we are using data of SQL Saturday session details and prepare different line chart, tree map, filled map , Table , R script Visual different charts that we have used to show different visualization details as below.

One can learn how to create these different charts by going through below links

https://powerbi.microsoft.com/en-us/documentation/powerbi-service-visualizations-for-reports/

Step 7: For now just for sample how we can simply create a line chart by just drag and drop feature of Power BI Desktop.

Just go to right side of Power BI Desktop and select EventDate and SQLSATURDAY filed and drop Eventdate in Axis and SQLSATURDAY into Values section and change the SQLSATURDY value as count by just clicking on SQLSATURDY field in Values section.

Just simple so you can try these different charts by simply drag and drop feature.

For R script Visual you need to know some R language first in order to create R visualization chart in Power BI . One can learn R from https://www.r-project.org/

Step 8 :Publish these reports on Power BI cloud service by just clicking Publish button from Power BI Desktop , also now one can also publish these reports to publicly in web. Once you publish your report in Power BI service go to Reports -> Your Report -> File -> Publish to web . After that anyone can go through these reports publicly in the web.

Also, URL that you get through publish to web can be embedded to your website along.

Summary:

In Summary, from these above Power BI Desktop Visualization it helps to tells us that which SQL Saturday is conducted in which state, country in which year and a particular Day along with total sessions conducted. It also helps us to tell us that SQL Saturday trends year on year and mostly in September month most of SQL Saturday is conducted as per SQL Saturday data history.

Also, it helps to tell the distribution of state wise SQL Saturday conducted states .which shows most of SQL Saturday happens in North America.

Think while Rename a Column Name of Table in SQL.

If someone tries to rename the column of table then we write a query as

SP_RENAME  'TableName.[OldColumnName]' , '[NewColumnName]', 'COLUMN'
--we use as
SP_RENAME  'DimCustomer.[FirstName]' , 'CustomerName' , 'COLUMN'
--Mistakenly,
SP_RENAME  'DimCustomer.[FirstName]' , '[CustomerName]' , 'COLUMN'
which gives the column the name [CustomerName], not CustomerName.
For rename the column 
http://blog.sqlauthority.com/2008/08/26/sql-server-how-to-rename-a-column-name-or-table-name/

You don’t use the [] escape in the second argument but by mistakenly if we use ‘[CustomerName]’ as new column name instead of CustomerName.

It sucessfully updates the column but while we select that column it’s now gives the error as

"Either the parameter @objname is ambiguous or the claimed @objtype (COLUMN) is wrong"

Also while we try to drop the column itself we cannot delete it. It also gives error as

ALTER TABLE DROP COLUMN failed because column '[CustomerName]' does not exist in table 'Customer'.

Does that mean we have to recreate the customer table since the renamed column [CustomerName] cannot use in select statement too.

Well there is a method for changing the mistakenly rename column ‘[CustomerName]’ to CustomerName . Just simple you need to go to the table DimCustomer and then click plus sign of the table and then you can see the list of columns of DimCustomer table then right click and add New Column tab which will edit all the list of columns and there you go just change the ‘[CustomerName]’ to CustomerName . This will solve the issue we are having.

columnrename

If someone by mistakenly rename the column as above and getting similar issue this post will be surely helpful.

Also, there might be any alternative T-SQL method then please suggest some idea that will surely helpful.

Thanks,

Anil Maharjan

The power of SIMBA into the world of BI on top of BIG DATA

After a long time gap, I would love to post this blog regarding the power of SIMBA into the world of BI on top of BIG DATA .

It’s really interesting and exciting to see these kinds of new technology and tools on the way. Well ,I am talking about the SIMBA MDx Provider developed by the simba Technologies .

Yesterday, while I was researching regarding BIG DATA and reading some great blog post then I came to know about the SIMBA MDx Provider which seems to be a great and cool tool .

What actually the Simba’s MDx Provider :

Simba’s MDX Provider is an ODBO provider installed on the same machine as Excel. Simba also has a tool for building cube definitions, which we call schemas. These schemas are saved in XML. Simba’s schema maps MDX metadata constructs to Impala table structures. When an ODBO compliant tool such as Excel issues an MDX query, Simba’s MDX Provider maps the MDX query to HiveQL, sends the HiveQL to Impala, collects the results and returns them to the end user.

The most important technical concept is that there is no intermediate server or cube structure that caches data, all queries go direct from Simba’s MDX Provider to the Cloudera Impala server in real time.

You can find out more on this through the main blog post by samba itself through the link below:

http://blogs.simba.com/simba_technologies_ceo_co/2013/02/demo-microsoft-excel-pivottables-on-cloudera-impala-via-simba-mdx-provider.html

As the technology is in early development, it is not generally available for early testing.

Demo of Simba’s integration of doing MDX queries over Cloudera’s Impala for use with Microsoft Excel Pivot Tables .http://youtu.be/kZahPE9Puv0

Also there is a great PDF regarding the Simba Teradata case- study.

http://www.simba.com/docs/Teradata-Case-Study.pdf

Hope to see and use these kind of great tools in near future into our world of BI.

Reference :

http://blogs.simba.com/simba_technologies_ceo_co/2013/02/demo-microsoft-excel-pivottables-on-cloudera-impala-via-simba-mdx-provider.html

http://blog.cloudera.com/blog/2012/10/cloudera-impala-real-time-queries-in-apache-hadoop-for-real/

http://cwebbbi.wordpress.com/2013/02/25/mdx-on-cloudera-impala/

Thanks,

Anil Maharjan

SQL PASS Summit 2012 Hot News !!!

Hello all,

Some Hot news of the SQL PASS SUMMIT 2012.

PASS Summit is the world’s largest, most-focused, and most-intensive conference for Microsoft SQL Server and BI professionals.

Organized by and for SQL Server and BI users, PASS Summit delivers the most technical sessions, the largest number of attendees, the best networking, and the highest-rated sessions and speakers of any SQL Server event in the world.

The 3 most awaited days from Nov 7-9 are here for all the SQL and BI lovers around the world.

Currently, I couldn’t attend this session but I wish to attend this session in upcoming days or in near future summit’s a long run way to go .No worries, PASS Live TV is here for us who couldn’t make it to this SUMMIT in Seattle. The live covering of the SUMMIT can be browsed through the link below.

http://www.sqlpass.org/summit/2012/

Also, it’s nice to hear the upcoming project news from the Summit Keynotes .Apart from that ,I have read few blogs regarding the Summit Keynotes from the great persons and want to share with you guys too .So that one can know what’s in upcoming future technologies and updates in SQL and BI field mainly.

These are some most highlighted upcoming updates in our field. You can have a look on these from the great writer blogs ( By Chris ,Marco and Denny ).

Project Hekaton: Project Hekaton,codename for in-memory OLTP technology with improvement in performance and scalability.

Polybase : Polybase allows you to query data from an external source from within the SQL Server 2012 PDW. This will allow you to create an external table which simply links to a Hadoop cluster.By using Polybase a single T-SQL query will run queries across relational data and Hadoop data. A single query language for both. Sounds really interesting for using BigData in a more integrated way with existing relational databases.

By Chris “Wouldn’t it be cool if you could query the Facebook graph or Twitter or even Bing directly from TSQL?”Really looking forward to see these kinds of upcoming project by Microsoft SQL Server Team.

Updates on Columnstore: In the next major release of SQL Server the columnstore indexes will be updatable and it will be possible to create a clustered index with Columnstore index. This is really a great news for near real-time reporting needs.
Power View works with Multidimensional cube
Microsoft HDInsight Server CTP which is Microsoft’s Hadoop offering
SQL Server 2012 SP1 and Office 2013
Mobile BI and more

You can have a look in the links below for more details .

http://sqlblog.com/blogs/marco_russo/archive/2012/11/07/pass-summit-2012-keynote-and-mobile-bi-announcements-sqlpass.aspx

https://cwebbbi.wordpress.com/2012/11/08/thoughts-on-the-pass-summit-2012-day-1-keynote/#comment-6615

http://itknowledgeexchange.techtarget.com/sql-server/sql-pass-day-1-keynote/

Just want to share the updates with you guys.we will be looking forward to play around with these great products and features from Microsoft.

Thanks,

Anil Maharjan

Does Linked Measure Group allows to delete the not used Measures for that cube?

Hello all,

After long time gap, I just want to share something regarding the Linked Measure Group and which might be sure helpful to all of us mainly in the field of SSAS .

While creating and processing cube with Linked Measure Group I got certain error so posted on the MSDN and got the response .so, I think it will be good to share this .

Below is the scenario :

I have developed the Linked Measure Group in order to share data across cubes within duplicating data for sharing same Measure Group within multiple cubes .

While processing a cube I got certain error and all I want to know that

Let’s say we have a cube A and Cube B and we create a linked Measure group within cube B by using a Measure group of Cube A and within linked Measure Group in Cube B , we delete some measures that is not needed for cube B and Processed cube B.

Then I got certain error while processing a cube B.

But It’s strange that it won’t give any issue while deleting the dimension that linked within Linked Measure group in Cube B.

So, does Linked Measure group allows to delete the not used Measure for that cube.

If we process the cube without deleting any Measure from Linked Measures then successfully both the Cube A and Cube B processed.

Also ,just a quick do we need to process the Cube A first and then Cube B after in order to use Linked Measure Group correctly .since while processing only cube B I got error as

‘Errors in the OLAP storage engine: The metadata for the statically linked measure group, with the name of ‘Fact Table’, cannot be verified against the source object’.

Hope to know from the one who knows better in this field. Your small help will be really appreciated.

Response and findings:

It is not recommend to delete the irrelevant measures when create a linked measure group. We can set the “Visible” property to “False” to hide this measure. so, in simple we cannot delete the Measures from the Linked Measure Group.

If you add a linked measure group to a cube, the BIDS interface will let you delete some of the measures in the group (since you may not want to show all the available measures in your new cube) – however, doing this causes the above error when processing the cube! The error is basically saying that the linked measure group metadata no longer matches that of the source measure group.

The solution is that when you add a linked measure group, all the existing measures should be left intact. To hide them from the end user, set the “Visible” property for each measure

Reference:

http://social.msdn.microsoft.com/Forums/en-US/sqlanalysisservices/thread/1cfa1caf-2ff1-4991-a8e5-af842f287e38

http://bizintnz.blogspot.com/2009/10/ouch-nasty-ssas-linked-object-error.html

Thanks,

Anil Maharjan

Free download Microsoft Press eBook: Microsoft SQL Server 2012 Pocket Consultant.

Hello all,

I just want to share a link to download a free copy of Microsoft Press eBook ‘Microsoft SQL Server 2012 Pocket Consultant’.
This could be a great eBook for you to learn all about how to manage SQL Server .

Currently, Microsoft Press just posted the first two chapters within this free copy as Managing SQL Servers and Policy-Based Management.

So, SQL Geeks !!! Grab your digital copy and learn all about how to manage SQL Server!

I have already downloaded and have started to read and learn the things.Hope you will too.

This link could be helpful for all of us in the field of BI-SQL server to know more about SQL Server 2012 and to manage SQL Server.

http://blogs.msdn.com/b/microsoft_press/archive/2012/07/25/free-download-managing-sql-servers-and-policy-based-management-an-excerpt-from-microsoft-sql-server-2012-pocket-consultant.aspx

Contents at a Glance

Chapter 1: Managing SQL Servers

Using SQL Server Management Studio
Managing SQL Server Groups
Managing Servers
Using Windows Power Shell for SQL Server Management
Starting, Stopping, and Configuring SQL Server Agent
Starting, Stopping, and Configuring MSDTC
Managing SQL Server Startup
Managing Server Activity

Chapter 2: Implementing Policy-Based Management

Introducing Policy-Based Management
Working with Policy-Based Management
Configuring Central Management Servers
Managing Policies Throughout the Enterprise

Thanks,

Anil Maharjan

Hadoop Summit 2012- Big Data in Focus !!!

Hello all,

This is not any blog describing about something helpful but just want to inform all the data and technology loving person that Hadoop Summit 2012 is going to be held soon.

Looking forward to hear more about the Hadoop Summit 2012 which is going to be held tomorrow ,JUNE 13th – 14th @ San Jose Convention Center .Wish to watch the live coverage of Hadoop Summit 2012 and hear more tweet’s regarding on this and upcoming focuses of this summit.

For more:

http://hadoopsummit.org/

http://siliconangle.tv/channels/Hadoop

Also, I have only few knowledge regarding on this ‘Microsoft Big Data and Apache Hadoop’ but want to learn more on this .So if anyone knows some good links or something regarding on this technology then please you can share and provide some knowledge or help along within this blog. That would be really appreciated 🙂 ..!!!

Also, really looking forward if any universities offering the Master’s courses that includes Big Data and Apache Hadoop.

Regards,

Anil Maharjan

SSAS Compare, a cool tool for Microsoft Business Intelligence professionals.

Hello all,

It’s been long time gap and I was really missing my blogging time part, busy blogging for my friends at Two Hour Blogger.

Through this blog I would just want to share the new tool developed by the Red –Gate called

SSAS Compare . which I think , SSAS Compare is one of the cool tool .

It has a great feature as

With SSAS Compare, you can select SSAS databases to compare differences between cubes, dimensions, measures and other objects.

SSAS Compare can create an XMLA deployment script to deploy changes to the target database.

This tool can be helpful when we have several versions of the same catalog/database within Production side and development side and needs to compare and find out how they differ. sometime we get confuse that which one is the latest catalog and what’s the latest different within the catalog between Production side and development side.

So, this tool can be must helpful which allows to compare the catalog and choose the required cube structure need to deploy by simply generating the XMLA script.

Download a free copy of Microsoft Press eBook ‘Introducing Microsoft® SQL Server 2012’

Hello all,

After a long time gap, I just want to share a link to download a free copy of Microsoft Press eBook ‘Introducing Microsoft® SQL Server 2012’.

This could be a great eBook for you to learn and know about what’s inside the ‘ SQL Server 2012 ’ released by Microsoft.

I have already downloaded and have started to read and learn the things .Hope you will too.

This link could be helpful for all of us in the field of BI-SQL server to know more about SQL Server 2012 , virtual lunch event-sessions and more.

http://www.microsoft.com/sqlserver/en/us/default.aspx

Contents at a Glance

Part I Database Administration (by Ross Mistry)

1. SQL Server 2012 Editions and Engine Enhancements
2. High-Availability and Disaster-Recovery Enhancements
3. Performance and Scalability
4. Security Enhancements
5. Programmability and Beyond-Relational Enhancements

Part II Business Intelligence Development (by Stacia Misner)

6. Integration Services
7. Data Quality Services
8. Master Data Services
9. Analysis Services and PowerPivot
10. Reporting Services

Regards,

Anil Maharjan

How can we find the distinct count of all the Hierarchy and attribute members from all the dimensions.?

Hello all,

Firstly ,Happy New Year 2012 🙂 to all .!!!

In order to start this year blogging, I would like to post another blog which might be helpful to the one who works in SSAS.

Last time, I was just trying for some trick or some queries in order to quick find the distinct count of all the Hierarchy and attribute members from all the dimensions .

For Example, If we have ‘ PaidDate ‘ dim having Hierarchy members as PaidYear ,PaidMonth, PaidDay then we need to calculate the Distinct count of all those members and similarly for all the others dimension .Then I simply used the

SQL query method as:

Select count(Distinct(PaidYear,PaidMonth,PaidDay)) from PaidDate and similar for others but all I want is some Quick methods or some DMV queries to find out my solution.

After some research and through MDSN, I finally got my solution .It’s just a simple DMV query as

select * from SYSTEMRESTRICTSCHEMA($SYSTEM.DISCOVER_DIMENSION_STAT,DIMENSION_NAME='Dimension', DATABASE_NAME = 'Database')

--Dimension -replace with the Dimension you want to find Distinct count and Database -replace with your DB catalog.

In the returned tabular result, the ATTRIBUTE_COUNT column is to describe the count that I was looking for.

Is there any alternative solution for this kind of issue .Hope someone have some idea regarding on this.

Hope this post will be useful for all of us in the field of BI mainly SSAS.

Regards,

Anil Maharjan