MS BI | SQL with Manoj

Download & Install SQL Server Reporting Services (SSRS) 2017 (decoupled from SQL Server engine setup)

January 7, 2018 3 comments

In one of my [previous blog] post related to SQL Server 2017, I mentioned regarding SQL Server Reporting Services (SSRS) that it will no longer be installed from the main feature tree of SQL Server engine setup, just like SSMS.

In SQL Server 2016, the SSMS Setup was taken away from the SQL Setup

As per Microsoft, this is basically to support the move to make a universal version of SSRS that may ship more frequently that the whole SQL Server v-next.

So, now on wards after installing SQL Server 2017 you need to install SSRS separately, just like SSMS.

–> Till SQL Server 2016 you have an option of choosing “Reporting Services” in the Feature selection tree, but with SQL Server 2017 and on wards this option is taken out, can be seen in the pic below:

Click to expand

–> Now to download SSMS 2016 you can either visit the Microsoft [download page]

– Or –

Install directly via the “Installation Center” as shown below. This will install SSMS directly online.

Click to expand

So, once you take appropriate action above to download SSRS 2017, the Installation kicks off like this:

: Click to expand

: Click to expand

: Click to expand

: Click to expand

: Click to expand

: Click to expand

: Click to expand

Check my blog posts on most of the new features released in SQL Server 2017.

Tabular Model Project does not load in Visual Studio – need SSDT

May 22, 2017 Leave a comment

Today one of my colleague started got one Change Request to add few attributes to a Tabular Model already deployed from a previous project. He installed Visual Studio and connected the Repository. But when he tried to load the Project it was giving following error:

Unsupported
This version of Visual Studio is unable to open the following projects. The project types may not be installed or this version of Visual Studio may not support them.
For more information on enabling these project types or otherwise migrating your assets, please see the details in the “Migration Report” displayed after clicking OK.
– abcTabularCube, “D:\VSO\abcTabularCube\xyz.smproj”

No changes required
These projects can be opened in Visual Studio 2015, Visual Studio 2013, Visual Studio 2012, and Visual Studio 2010 SP1 without changing them.
– abcTabularCube, “D:\VSO\abcTabularCube\xyz.sln”

I checked his Visual studio, opened a new Project and checked if the MS BI templates for SSAS, SSRS and SSIS are showing or not. You can check them below:

If those won’t show up then you need SQL Server Data Tools (SSDT), which you can install from here.

Categories: Analysis Services, Tabular Model Tags: SQL Server Data Tools, SSDT

Get SSRS Report Stats, Usage report and Runtime

October 12, 2016 Leave a comment

SELECT 
       Cat.[Name] AS ReportName, 
       Cat.[Path] AS ReportPath, 
       ELog.[Format] AS ReportFormat, 
       ELog.[UserName] AS RunBy, 
       ELog.[TimeStart] AS RunDateTime, 
       CONVERT(nvarchar(10), ELog.[TimeStart], 101) AS RunDate,  
       CASE(ELog.[Source])
              WHEN 1 THEN 'Live'
              WHEN 2 THEN 'Cache'
              WHEN 3 THEN 'Snapshot' 
              WHEN 4 THEN 'History'
              WHEN 5 THEN 'AdHoc'
              WHEN 6 THEN 'Session'
              WHEN 7 THEN 'Rdce'
              ELSE 'Unknown'
       END AS [DataSource],
       CASE(ELog.[RequestType])
              WHEN 0 THEN 'Interactive'
              WHEN 1 THEN 'Subscription'
              WHEN 2 THEN 'Refresh Cache'
              ELSE 'Unknown'
       END AS RequestType,
       1 AS [NumRuns], 
       ELog.[TimeDataRetrieval],
       ELog.[TimeProcessing],
       ELog.[TimeRendering],
       ELog.[ByteCount],
       ELog.[RowCount]
FROM ELogecutionLog AS ELog 
INNER JOIN [Catalog] AS Cat ON ELog.ReportID = Cat.ItemID 
WHERE Cat.Type = 2
AND ELog.[TimeStart] >= '2016-08-01'
ORDER BY ELog.TimeStart DESC

Categories: Reporting Services Tags: SSRS, SSRS Stats, SSRS Usage

What is ODS (Operational Data Store) and how it differs from Data Warehouse (DW)

December 17, 2014 2 comments

I see lot of people discussing about ODS, and citing their own definitions and ideas about it. Some people also use the name as a synonym for a Data Warehouse or Factory Database. Thus, at times it becomes very difficult to tell or convince people while you are designing or architecting a DW/BI solution.

So, I thought to give some time to explain what actually an ODS is.

Simple definition: An Operational Data Store (ODS) is a module in the Data Warehouse that contains the most latest snapshot of Operational Data. It is designed to contain atomic or low-level data with limited history for “Real Time” or “Near Real Time” (NRT) reporting on frequent basis.

Detailed definifion:

– An ODS is basically a database that is used for being an interim area for a data warehouse (DW), it sits between the legacy systems environment and the DW.

– It works with a Data Warehouse (DW) but unlike a DW, an ODS does not contain Static data. Instead, an ODS contains data which is dynamically and constantly updated through the various course of the Business Actions and Operations.

– It is specifically designed so that it can Quickly perform simpler queries on smaller sets of data.

– This is in contrast to the structure of DW wherein it needs to perform complex queries on large sets of data.

– As the Data ages in ODS it passes out of the DW environment as it is.

–> Where does ODS fits in a DW/BI Architecture?

–> Classes of ODS (Types):

Bill Inmon defines 5 classes of ODS shown in image below:

– Class-1 ODS would simply involve Direct Replication of Operational Data (without Transformations), being very Quick.

– Whereas Class-5 ODS would involve high Integration and Aggregation of data (highly Transformed), being a very time-consuming process.

Categories: DW and BI, Misconception Tags: Bill Inmon, Data Warehouse, ODS, ODS Class, Operational Data Store

Self Service BI by using Power BI – Power Pivot (Part 2)

October 29, 2014 Leave a comment

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.

1. Open Excel, and go to POWERPIVOT tab and click on Manage, this will open a new PowerPivot Manager window.

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.

3. Provide SQL Server Instance name that you want to connect to. Select AdventureWorksDW2012 Database from the Database name dropdown, and click Next.

4. Click Next again and select the required Tables (10 selected), click Finish.

5. Make sure you get Success message finally, click Close.

6. In the PowerPivot Manager window you will see many tabs listing records. Click on Diagram View to see all the tabs as tables and relations between them. This is your Power Pivot – Data Model:

–> Now as your Data Model is ready, you can create Pivot Reports in Excel, let’s see how:

1. Go to the PowerPivot Manager window and click on PivotTable icon and then select PivotTable from the dropdown.

2. The control moves to the Excel sheet, select Existing Worksheet on the Popup.

3. Now select following columns form the PivotTable Fields list:
– DimGeography.EnglighCountryRegionName
– FactInternetSales.SalesAmount
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.

You can align, move, resize the report, slicers and beautify the report as you want, as shown below: