Sunday, January 17, 2016

How to read data from an Excel file starting from the nth row using SSIS

I have Excel files I need to read using SQL Server Integration Services (SSIS), but the first few rows in the file are just information about the data and these rows should be ignored. How can I read data from an Excel file starting from the nth row in SQL Server Integration Services.
The Excel source is one of the most used data sources in SQL Server Integration Services (SSIS). In this tip I will demonstrate how can we read Excel data starting from any row. Consider a scenario like the Excel file below screen where the data starts on row number 7
Click here to read complete article.

Sunday, January 10, 2016

MSSQLTips.com Rookie of 2015 Award

Thank you world for your vote. I won MSSQLTips.com Rookie of 2015 Award just because of you!

2015 was a great year for the MSSQLTips.com community and dedicated team of authors who provide value to the SQL Server Community every second of the day.

Please visit here for more detail.

https://www.mssqltips.com/sqlservertip/4153/mssqltipscom-author-and-rookie-of-2015/

Thursday, January 7, 2016

SSAS Interview Questions

1.      What is Data Source?
2.      Can we create more than one Data Source?
3.      What is Impersonation information?
4.      What are options available in Impersonation information tab?
5.      What is Isolation in Data Source?
6.      What is default maximum number of connection in Data Source?
7.      What is query timeout in Data Source?
8.      What is data source reference in Data Source?
9.      What are different types of provider in Data Source?
10.  What language is used to query cube?
11.  What is Data Source View?
12.  What is named query?
13.  What is named calculations?
14.  How can we view data in DSV?
15.  How can we create relationship between two tables in DSV?
16.  Can we create relationship between two tables if data type doesn’t match?
17.  What is Dimensional modeling?
18.  What are the types of schema?
19.  What are the types of dimensions?
20.  What are the different types of dimensions you have used?
21.  What is user friendly name in DSV?
22.  How can you stop joining two tables automatically in DSV
23.  Can you create more than one DSV?
24.  What is attributes?
25.  What is hierarchy?
26.  What is attribute hierarchy?
27.  What is the maximum level of attribute hierarchy?
28.  What is user hierarchy?
29.  What are the types of hierarchy?
30.  What is attribute relationship?
31.  What is rigid relationship?
32.  What is flexible relationship?
33.  If customer user hierarchy is created with rigid relationship, will it work? How it will impact on cube performance and aggregation.
34.  What will be the impact on performance if attribute relationship are wrongly used?
35.  What is translation in Dimension?
36.  What is Error configuration in dimension table?
37.  What is processing?
38.  What are the different types of processing options?
39.  What is unknown member?
40.  What is to enable Writeback in Dimension?
41.  What is different dimension type option available in Dimension tab?
42.  What is collation designator?
43.  What is MDXmissingmember mode?
44.  Aggregation created only on key column; will it show aggregation for named column?
45.  What is proactive caching?
46.  What are different options available in proactive caching?
47.  What is different storage mode available in SSAS?
48.  What is attributehierarchyenabled property in dimension?
49.  What is default member?
50.  What is discretization method property?
51.  What is isaggregratable property?
52.  What are options available to order by?
53.  What is Parent – child in dimension?
54.  What is key named and value column in dimension?
55.  What is UDM?
56.  What is semi additive measure?
57.  How can you create cube for two fact tables?
58.  What is degenerated dimension?
59.  What is role playing dimension?
60.  What is conformed dimension?
61.  What are the steps you follow to create a cube?
62.  What is duplicate key found error? How can you resolve it?
63.  What is key attribute is missing error? How can you fix it?
64.  Do you create duplicate dimensions, if one dimension is used multiple times?
65.  What is measure group?
66.  How to create measure group?
67.  How to create new measure?
68.  What are aggregation functions available on measure?
69.  What is measure expression?
70.  What are different tabs available in cube design window?
71.  What is Dimension Usage?
72.  What are different relationship type options available in dimension usage?
73.  What is null processing? What are all option available to handle NULL?
74.  What is the purpose of calculation tab in cube design tab?
75.  What is calculated member?
76.  What is calculated set?
77.  What is cube metadata?
78.  What is KPI tab in cube design window?
79.  What are all steps required to create a KPI?
80.  What is KPI Value Expression, Goal expression, status, trend and additional properties?
81.  What are different types of actions?
82.  Can we create two partitions on same measure group?
83.  What are all steps you follow to create a partition?
84.  What are the best practices you must follow to create a partition?
85.  How would you know if partition is created for limited row or complete data?
86.  What is writeback property in partition?
87.  In what case we can’t use writeback property?
88.  What are different partition process options?
89.  How processing works?
90.  What id dimension key log file?
91.  What is process affected objects?
92.  What is aggregation tab in cube design?
93.  What are two types of aggregation design?
94.  What are steps to create or redesign aggregation?
95.  What is perspective?
96.  What is translation?
97.  How currency conversion works?
98.  What would be your currency dimension structure?
99.  Can we access cube while processing?
100.                      How did you deploy cube from Dev to production?
101.                      What are different types of deployment methods?
102.                      What are different types of security roles in cube?
103.                      How will you process your cube for incremental load?
104.                      What is semi additive measure?
105.                      How will you know who is accessing your cube?
106.                      What is SSAS architecture?
107.                      What is difference between SSAS 2008 R2 and 2012?
108.                      What are the best practices you will follow to create a cube?
109.                      What is cell level, dimension level, member level security?
110.                      How would you create a log file for SSAS?
111.                      How does locking works in SSAS?
112.                      What are the best practices to create dimension in SSAS?
113.                      What is meant by incremental processing?
114.                      Why do we use incremental processing?
115.                      How does processing works?
116.                      How does processing works on dimensions?
117.                      Why do we do cube partition?
118.                      How will you handle insert update in cube?
119.                      What is mining structure?
120.                      What is authentication mode for SSAS?
121.                      I have processed the cube but there is no data, how would you troubleshoot?