Wednesday, February 25, 2015
Reference Dimensions - SSAS 12
Refer Source: Reference Dimensions - SSAS 12
note:
i.In the star schema , if there is a snowflake schema structure has to be clubed together , the reference dimension
come into picture.
example: the fact table is connected to the Customer table
but geography table is not and it has a link to customer table.
i. Materialized if checked it's created in the physical storage.
SSAS - Developing CUBE Part II - 7
SSAS - Developing CUBE Part II - 7
Measures -> Properties ( IgnoreUnrelatedDimensions = False )
Note: if the dimension & measure are not related the cube will not show data, for example for dimEmployee & Orderquantity does not have link , so the above property won't show data.
Formatting: Orderquaantity: #,# SalesAmount:$#,#
Thursday, February 19, 2015
SSAS - Parent Child Dimension
Refer Source: SSAS - Parent Child Dimension - Unbalanced Hierarchy 5
|
DimEmployee
In it the Employee & the Manager is their and Manager is also the Employee. So to know who is the
manager "ParentEmployeeKey" column holds the Manager id that is the EmployeeKey.
EmployeeKey
ParentEmployeeKey:SelfReferencingFK
All
-A
-A
-abc
-B
-B
Things to Fix.
1. All is on the top, instead choose a Employee.
Dimension: DefaultMember: Choose One say A
2.Employee Appearing Twice
i.Not in same level - A , it's not right
ii.Same Level : means ( the employee is evaluated at employee level & there are employee reporting )
Fix:Parent Employee Key: properties
Parent - Child
MemberWithData: NonLeafDataHidden i.fix
MemberWithDataCaption:*(DirectReports) ii.fix
NamingTemplate:
1.All
2.CEO
3.Account Manager
4.Manager
5.Engineer
6.Staff
note: ISaggregatable:False
SSAS - Date Dimension
Refer Source:
Dimensions:
Unbalanced Hierarchy:
Dimensions:
*
DimDate: Key Column: Date Key
Dimension Attributes:
Month: Properties
KeyColumns:
EnglishMonthNumberYear
EnglishMonthName
Name Column: EnglishMonthName
*
Day: DayNumberoftheWeek
*
Fiscal:
-------
Fiscal Year
Fiscal Semester
Fiscal Quarter
Month
Date Key
Calendar:
-----------
Calendar Year
Calendar Semester
Calendar Quarter
Month
Date Key
*
To uniquely identify: Add key columns property.
*
Dim Date:
Calendar Quarter ( Calendar Year,Calendar Semester,Calendar Quarter)
Calendar Semester( Calendar Year,Calendar Semester)
Calendar Year
Date Key
Day
Fiscal Quarter ( Fiscal Year,Fiscal Semester,Fiscal Quarter)
Fiscal Semester( Fiscal Year,Fiscal Semester)
Fiscal Year
Month
note: Except "Day" attribute , all rest "AttributeHierarchyVisible:false"
Browser will show: "Fiscal" , "Calendar" , "Day"
Month:
[Source Attribute] [Related Attribute]
Month Calendar Quarter
Calendar Semester:
[Source Attribute] [Related Attribute]
Calendar Quarter Calendar Semester
Note:
Balanced Hierarchy:
Unbalanced Hierarchy:
Wednesday, February 18, 2015
SSAS - Dimensions - Natural and Un-Natural Hierarchy
Refer Source: SSAS - Dimensions - Natural and Un-Natural Hierarchy 3
|
DimProduct
Key Columns:ProductKey
Name Column:English Product Name
(Descriptive Value)
note:automatically adds the related tables.
note: can add "Filter" to the dimension.
ProductKey:Properties
KeyColumn: Name Column:
DimProduct: ( in the Dimension "Property")
UnknownMember:Visible
UnknownMemberName: UnknownValue
Note:
Attributes Hierarchies Data Source View (DSV)
note:
i.Hierarchies are build from Dimensions.
ii.After Hierarchies are build, in the browser those will show, along that "Dimensions" same attributes will show too, so on the Dimension , properties, "AttributeHierarchyVisible:false.
iii. In "Dimension" properties, for unknown members
UnknownMember:visible
UnknownMemberName: UnknownValue
Key Columns:ProductKey
Name Column:English Product Name
(Descriptive Value)
note:automatically adds the related tables.
note: can add "Filter" to the dimension.
ProductKey:Properties
KeyColumn: Name Column:
DimProduct: ( in the Dimension "Property")
UnknownMember:Visible
UnknownMemberName: UnknownValue
Note:
Attributes Hierarchies Data Source View (DSV)
note:
i.Hierarchies are build from Dimensions.
ii.After Hierarchies are build, in the browser those will show, along that "Dimensions" same attributes will show too, so on the Dimension , properties, "AttributeHierarchyVisible:false.
iii. In "Dimension" properties, for unknown members
UnknownMember:visible
UnknownMemberName: UnknownValue
SSAS - Data Source Views
Refer Source : SSAS - Data Source Views 2
|
Data Source Views: Fact,Dimension Tables are chosen
Diagram Organizer: Helpful , when it has multiple Fact Table, to neatly see the ones one need , one can create it.
Add Diagram - Add Related Tables
DimCustomer:
" New Named Calculations"
"Replace Named Query"
Set Logical Primary Key
SSAS:Introduction
Refer Source: SSAS Introduction - Data Source 1
|
Choose the Database. Basically having the connection to Database to have access to the tables.
Tuesday, February 10, 2015
Reporting Services (SSRS) Part 6 - Grouping in Tables
Refer Source:
Reporting Services (SSRS) Part 6 - Grouping in Tables
Applying Grouping to Tables:
- Creating a Parent Group
- Adding Subtotal
- How to Sort with Groups
- Creating Collapsible Sections
- Creating Nested Group
*
[Row Groups] [Column Groups] ->Advance mode
*
[Row Groups] ->right click [Add Group] -> [Parent Group]
- Group By [Director Name]
- Add Group header
- Add Group footer
note: if you want sum on a field , in that column in a field right click and choose
Looking at "Expression" can see [Sum(field)]
*
Sorting
Design view complete left corner can see "Tablix properties" when right click , but sorting is not done here.
[Row Groups]
[DirectorName] -Properties - Sorted
[Details] -Group Properties - Sorting - Add
*
Page Break
[Row Group]
[DirectorName] - Group properties - Page Breaks
Page Breaks
- Page break options:
- Between each instance of a Group
- Also at the start of a Group
- Also at the end of a Group
*Collapsing
Remove Page Break - to show this
[Details]
Categories
Visibility
Hidden [True]
ToggleItem [DirectorName1]
*
[Row Groups] [Column Groups] - right click - Advanced Mode
(note:show more options now)
[(DirectorName)]
[Static] - True , ToggleItem[Director1]
[Details]
[Static]
*Nested Groups
[DirectorName] -> Choose [ParentGroup] -> [CountryName]
[CountryName] -> Group Properties - Page Break
Sunday, February 8, 2015
Reporting Services (SSRS) Part 17 - Linked Reports
Refer Source Video :Reporting Services (SSRS) Part 17 - Linked Reports
Linking Reports using Tables:
- Designing the Basic Reports
-Creating Parameters
-Applying Filters
*
Creating Links Using Other Items:
- Adding Actions to Charts
- Creating a clickable WorldMap
-Linking to Reports using a Matrix
*
What is the Linked Report?
Creating a Parameter
Parameter:
Applying Filters:
DataSet
-DataSet Properties
Filters
Expressions:
Value: <- specify the parameter
*
Assigning the values to the Table
Textbox properties:
Action
Go To Report
Specify a report
Use these parameters to run the reports
Add
prmCountryID CountryID
note:Action just not only to the a text box, it can be on any object, (Chart etc.)
*
Creating Links using Charts:
Chart Data
Values:
FilmBoxOfficeDollars
[Avg(FilmBoxOfficeDollars)]
Category Groups:
[CountryName]
Series Groups:
Right Click on the Chart :
Choose "Series Properties"
Action:
rest all the same.
*
Creating a clickable Map:
Table & Chart ( Action : example is shown )
Polygon Layer:
Polygon properties:
Action
Go To Report
(all same ...)
Can not choose the value directly ( Expression )
*
Passing values to Multiple Parameters:
example: Matrix report
Parameter:
prmCountryID
prmCertificateID
DataSet:
properties
Filter
other report:
textbox
Action: Go To Report
Use these parameters
Add
prmCountryID
prmCertificateID
Textbox properties:
Action
Go To Report
Specify a report
Use these parameters to run the reports
Add
prmCountryID CountryID
note:Action just not only to the a text box, it can be on any object, (Chart etc.)
*
Creating Links using Charts:
Chart Data
Values:
FilmBoxOfficeDollars
[Avg(FilmBoxOfficeDollars)]
Category Groups:
[CountryName]
Series Groups:
Right Click on the Chart :
Choose "Series Properties"
Action:
rest all the same.
*
Creating a clickable Map:
Table & Chart ( Action : example is shown )
Polygon Layer:
Polygon properties:
Action
Go To Report
(all same ...)
Can not choose the value directly ( Expression )
*
Passing values to Multiple Parameters:
example: Matrix report
Parameter:
prmCountryID
prmCertificateID
DataSet:
properties
Filter
other report:
textbox
Action: Go To Report
Use these parameters
Add
prmCountryID
prmCertificateID
Saturday, February 7, 2015
Top 10 SQL Server Integration Services Best Practices
Source:
Top 10 SQL Server Integration Services Best Practices
*********************************************************************************
"Does your system need to scale beyond 4.5 million sales transaction rows per second?"
SQL Server Integration Services can process at the scale of 4.5 million sales transaction rows per second.
SSIS is an in-memory pipeline, so ensure that all transformations occur in memory.
*
...robust pipeline that can efficiently perform row-by-row calculations and parse data all in memory.
...If transformations spill to disk (for example with large sort operations) ...
...extract and load phases of the pipeline will touch disk (read and write respectively), the transformation itself should process in memory.
:Construct your packages to partition and filter data so that all transformations fit in memory.
*
if your packages are staying within memory is to review the SSIS performance counter Buffers spooled, which has an initial value of 0; above 0 is an indication that the engine has started swapping to disk.
*
Plan for capacity by understanding resource utilization.
i.e., the CPU, memory, I/O, and network utilization of your packages.
CPU Bound
...how much CPU is being used by Integration Services and how much CPU is being used overall by SQL Server while Integration Services is running.
:important if you have SQL Server and SSIS on the same box
The perfmon counter that is of primary interest to you is Process / % Processor Time (Total).
Measure this counter for both sqlservr.exe and dtexec.exe.
:Application contention: SSIS ~ Ms Sql Server
:Hardware contention: suboptimal disk I/O or not enough memory to handle the amount of data being processed.
:Design limitation: SSIS package is not making use of parallelism
:Network Bound:
...important to understand your network topology and ensure that the path between your source and target have both low latency and high throughput.
The following Network perfmon counters can help you tune your topology:
Current Bandwidth:
Bytes Total / sec:
Transfers/sec:
...If it is approaching 40,000 IOPs, then get another NIC card and use teaming between the NIC cards.
...using gigabit network adapters, increasing the number of NIC cards per server, or creating separate network addresses specifically for ETL traffic.
I/O Bound:
...
But if your I/O is slow, reading and especially writing can create a bottleneck.
...
The following Network perfmon counters can help you tune your topology:
Current Bandwidth:
Bytes Total / sec:
Transfers/sec:
...If it is approaching 40,000 IOPs, then get another NIC card and use teaming between the NIC cards.
...using gigabit network adapters, increasing the number of NIC cards per server, or creating separate network addresses specifically for ETL traffic.
I/O Bound:
...
But if your I/O is slow, reading and especially writing can create a bottleneck.
...
Memory bound:
"How much memory does my package use?"
Baseline source system extract speed.
Measure the speed of the source system by creating a very simple package reading data from your source with the a destination of "Row Count":
Rows / sec = Row Count / TimeData Flow
:Improve drivers and driver configurations:
Note that for 64-bit systems, at design time you may be loading 32-bit drivers; ensure that at run time you are using 64-bit drivers.
:Start multiple connections:
...
if you start several extracts at once. If concurrency is causing locking or blocking issues, consider partitioning the source and having your packages read from different partitions to more evenly distribute the load.
...
:Use multiple NIC cards:
*
Optimize the SQL data source, lookup transformations, and destination.
When you execute SQL statements within Integration Services
Data access mode
SQL Command:
Select A,B,C,D From dbo.Staging WITH(NOLOCK)
:to read a source, to perform a look transformation, or to change tables, some standard optimizations significantly help performance:
- Use the NOLOCK or TABLOCK hints to remove locking overhead.
- ...SELECT only the columns you actually need.
- perform your datetime conversions at your source or target databases
- SQL Server 2008 Integration Services, there is a new feature of the shared lookup cache.
- ... Commit size 0 is fastest on heap bulk targets. For an indexed destination, I recommend testing between 100,000 and 1,000,000 as batch size.
- Use a commit size of <5000 to avoid lock escalation when inserting; ...SQL Server 2008 you can now enable/disable lock escalation at the object level
...
Tune your network.
A key network property is the packet size of your connection.
By default this value is set to 4,096 bytes.
This means a new network package must be assemble for every 4 KB of data.
SqlConnection.PacketSize Property in the .NET Framework Class Library:increasing the packet size will improve performance because fewer network read and write operations are required to transfer a large data set.
If your system is transactional in nature, with many small data size read/writes, lowering the value will improve performance.
Since Integration Services is all about moving large amounts of data, you want to minimize the network overhead. This means that the value 32K (32767) is the fastest option. While it is possible to configure the network packet size on a server level using sp_configure, you should not do this. The database administrator may have reasons to use a different server setting than 32K. Instead, override the server settings in the connection manager as illustrated below.
*********************************************************************************
*********************************************************************************
*********************************************************************************
SSIS Memory allocation
*********************************************************************************
*********************************************************************************
*********************************************************************************
*********************************************************************************
SSIS Interview Questions for Memory Management and Deployment
*********************************************************************************
*********************************************************************************
*********************************************************************************
Wednesday, February 4, 2015
Creating Basic Reports from an SQL Server Analysis Services Cube
------------------------------------------------------------------------
Refer Source: Creating Basic Reports from an SQL Server Analysis Services Cube
------------------------------------------------------------------------
Summary:
Business users can create reports using "Report Builder". For
Sql Server 2008 , it is Report Builder 2.0 , for 2012 is Report
Builder 3.0.
Choose Analysis Service Database. It has a wizard & the report created here can be deployed to Report Server.
------------------------------------------------------------------------
Automate Report Delivery in SQL Server Reporting Services
--------------------------------------------------------------------------------------------------------------------------
Refer Source: Automate Report Delivery in SQL Server Reporting Services
--------------------------------------------------------------------------------------------------------------------------
Summary:
Report Manager, in the "Data Source", have the loginid & password and check the option
windows authentication. If it has sql server users , then windows authentication need not be checked.
Email delivary: click on the report , on top click details , in that window check, "Subscription".
To see "Email" option, first in SSMS, configuration tool, set up the email. Then email
option shows and one can set it up. Here in this screen"schdule" the report too.
--------------------------------------------------------------------------------------------------------------------------
MDX Query Basics (Analysis Services 2012)
MDX tutorial, introduction to Multidimensional Expressions
The Logic/Flow of MDX (a.k.a. How to Think in MDX)
Refer Source: MDX Query Basics (Analysis Services 2012)
Adventureworks 2012 multidimensional models installed on your SSAS MD mode instance.
Source to get: Codeflex etc.
Start "SSMS:" "Server Type:" Analysis Services
MDX:
1.
/*
This is multiline comment.
*/
-- This is single line comment
2.
SELECT ... FROM ... WHERE
Select From [insert_your_cube_name_here]
Select From [ Adventure Works]
3.
Measure Group: Internet Orders
Measures:
Dimensions:
4. -- adding Measures:
Select {measures} on COLUMNS --0
FROM [insert_your_cube_name_here]
Select { [Measures]. [Internet Order Count]} ON COLUMNS
FROM [Adventure Works];
note: for multiple columns { [Measures]. [Internet Order Count], }
or
Select { [Measures]. [Internet Order Count]} ON 0
FROM [Adventure Works];
5. -- adding Dimensions:
Select { [Measures]. [Internet Order Count]} ON COLUMNS ,
{ [Date]. [Calendar Year].[CalendarYear]} ON ROWS
FROM [Adventure Works];
note: when above rows show some null values to remove null values "Functions".
6.Functions:
Select NONEMPTY({ [Measures]. [Internet Order Count]}) ON COLUMNS ,
NONEMPTY({ [Date]. [Calendar Year].[CalendarYear]}) ON ROWS
FROM [Adventure Works];
7.SLICERS: -- WHERE Clause
Select { [Measures].[Internet Order Count]} ON COLUMNS Where [Date].[Calendar Year].& [2005];
Note: Can not have the where condition one the Slicer in the selection.
Select { [Measures].[Internet Order Count]} ON COLUMNS , {[Date].[Calendar Year].& [2005] }
Where [Date].[Calendar Year].& [2005];
Tuesday, February 3, 2015
Subscribe to:
Posts (Atom)