A. Set a linked server (analysis server) in your SQL Server
In the SQL Server Surface Area Configuration enable 'Ad Hoc Remote Queries'
1. Open SQL Surface Area configuration
2. Go to Database enginer --> Ad Hoc Remote Queries --> Set Enable OPENROWSER andn OPENDATASOURCE support
In SQL Mgmt Studio in Server Objects to the MSOLAP Linked Server provider to 'Allow inprocess'
- Open SQL Server 2005 Management Studio, and then connect to an instance of SQL Server 2005.
- In Object Explorer, expand Server Objects, expand Linked Servers, expand Providers, and then double-click MSOLAP.
- In the Provider Options dialog box, click to select the Allow inprocess check box under Provider options.
- Click OK to close the Provider Options dialog box.
B. Write Stored procedure :
1. Create a parameterized stored procedure to return a flat table from the Analysis Services cube. I will explain the process of setting this in the demo session.
CREATE
PROCEDURE [dbo].[usp_DataSet]
(
@ProductCategoryID nvarchar(10), @MeasureValue float)
AS
SET NOCOUNT ON;
DECLARE @sql_data nvarchar(max)
--define the temporary table for the data
CREATE TABLE #DataSet (SalesReason varchar(255) NULL,
SalesTerritoryGroup
varchar(255) NULL,
SalesAmount
decimal(18,2) NULL,
OrderQuantity
decimal(18,2) NULL,
TotalProductCost
decimal(18,2) NULL)
--statement
SET @sql_data = 'INSERT INTO #DataSet (SalesReason, SalesTerritoryGroup, SalesAmount,
OrderQuantity)
SELECT * FROM OPENQUERY(ADVWORKS_SSAS,
''SELECT NON EMPTY { [Measures].[Internet Sales Amount], [Measures].[Internet Order Quantity]}
ON COLUMNS,
NON EMPTY { ([Sales Reason].[Sales Reason].[Sales Reason].ALLMEMBERS *
[Sales Territory].[Sales Territory Group].[Sales Territory Group].ALLMEMBERS ) }
having [Measures].[Internet Sales Amount] '
+ convert(varchar,@MeasureValue) + ' ON ROWS
FROM [Adventure Works]
WHERE ([Product].[Category].&['
+ @ProductCategoryID + '])'' )'
--populate the temp table, return nothing if there is an error in MDX
--select @sql_data
begin try
EXEC sp_executesql @sql_data
end try
begin catch
end catch
--return the dataset
SELECT SalesReason,
SalesTerritoryGroup
,
SalesAmount
,
OrderQuantity
FROM #DataSet
DROP TABLE #DataSet
C. Report setttings
2. Use this SP as the Data Source in your server report
3. Create the required parameters in the report
4. Set the report parameters in the winform application based on the value provided by the end user. I have not used operator parameter as of now. However, after the demo you will find it easy to implement.
rp[0].Name = "ProductCategoryID";
rp[1].Name = "MeasureValue";
rp[0].Values.Add(txtCategoryId.Text.Trim());
rp[1].Values.Add(txtMeasureValue.Text.Trim());
5. Run the winForm app. Pass the parameters in the textboxes and show the report
Final screen:
For implementing dynamic security in your SSAS cube you need make little change in the Dimension model.
Steps:
1. Assuming you already have a dimension to store the employee credentials for whom you want to apply the security. If such dimension is not there then create one.In AdventureWorksDw we have such a table called DimEmployee
2. Decide what you want to secure, let’s say Customer dimension members
3. Create a fact table (factless) relating the Customer and Employee dimensions
4. Create / modify the cube to include the following
5. Add the test data (in employee table)
6. Test it
Connect to the cube through any client (Cube browser/SSMA/Excel) as a test user and you will see the dynamic security working.
If anybody need this sample write to me at azazr@microsoft.com
In this post let’s see how you can pass the parameters into your OLAP report. The parameters have to be passed dynamically through the WinForm application.
Here we will NOT be using the report parameters which you would have added on the report at the design time.
The report is displayed through report control in winApp (or webApp).
Final output:
Steps-A: (Design a parameterized report using BIDS and publish on to the report server)
1. Create a report server project and design a Pivot table report
2. Set the DataSource for the report as follows and also set the required parameters. Note that setting parameters is not must at design time. You can add parameters at runtime also.:
3. Preview the report in BIDS to check if everything is fine before you publish it
4. Deploy the report solution
Steps-B: Accessing report in the Client App
1. Create a winForm App and add a report viewer control. The screen will look something like this:
2. Here comes the real stuff. How to populate the parameter controls in winform from the dimension/hierarchies members and to set the report viewer control properties to show the report.
The code will be as follows:
a. In form load:
reportViewer1.ProcessingMode = Microsoft.Reporting.WinForms.ProcessingMode.Remote;
reportViewer1.ServerReport.ReportServerUrl = new Uri(@"http://localhost/reportserver");
reportViewer1.ServerReport.ReportPath = "/OLAP Reports/OLAP Report-CalculatedMeasures";
reportViewer1.ShowParameterPrompts = false;
this.reportViewer1.RefreshReport();
GetParam();
b. To populate the parameter control:
public void GetParam()
{
server = @"http://localhost/reportserver";
report = "/OLAP Reports/OLAP Report-CalculatedMeasures";
bool forrendering = true;
string historyId = null;
ParameterValue[] values = null;
DataSourceCredentials[] credentials = null;
ReportParameter[] parameterSSRS = null;
parameterSSRS = rs.GetReportParameters(report, historyId, forrendering, values, credentials);
ValidValue[] pvs = null;
ArrayList[] a =new ArrayList[parameterSSRS.Length];
for(int i=0;i<parameterSSRS.Length;i++)
{
a[i] = new ArrayList();
if (parameterSSRS[i].ValidValues != null)
{
pvs = parameterSSRS[i].ValidValues;
foreach (ValidValue pv in pvs)
{
a[i].Add(new ComboItem(pv.Label, pv.Val
ue));
}
}
}
//The ValueMember is required to get the fully qualified hierarchy path for the members used as the parameter value
cmbCountry.DataSource = a[0];
cmbCountry.DisplayMember = "Display";
cmbCountry.ValueMember = "Val";
cmbYear.DataSource = a[1];
cmbYear.DisplayMember = "Display";
cmbYear.ValueMember ="Val";
}
c. And finally to load the report in report viewer control
private void btnRefresh_Click(object sender, EventArgs e)
{
Microsoft.Reporting.WinForms.ReportParameter[] rp = new Microsoft.Reporting.WinForms.ReportParameter[2];
rp[0] = new Microsoft.Reporting.WinForms.ReportParameter();
rp[1] = new Microsoft.Reporting.WinForms.ReportParameter();
rp[0].Name = "DeliveryDateCalendarYear";
rp[1].Name = "CustomerCountry";
if (cmbYear.SelectedValue != null && cmbYear.SelectedValue.ToString() != string.Empty)
{
rp[0].Values.Add(cmbYear.SelectedValue.ToString().Trim() );
}
if (cmbCountry.SelectedValue != null && cmbCountry.SelectedValue.ToString() != string.Empty)
{
rp[1].Values.Add(cmbCountry.SelectedValue.ToString().Trim());
}
reportViewer1.ProcessingMode = Microsoft.Reporting.WinForms.ProcessingMode.Remote;
reportViewer1.ServerReport.ReportServerUrl = new Uri(@"http://localhost/reportserver");
reportViewer1.ServerReport.ReportPath = "/OLAP Reports/OLAP Report-CalculatedMeasures";
reportViewer1.ServerReport.SetParameters(rp);
reportViewer1.ShowParameterPrompts = false ;
this.reportViewer1.RefreshReport();
}
3. Now you can run the winApp and change the parameter values to see how the report’s columns and rows changes accordingly.
In one of my current data warehouse projects I wanted to use a time dimension. The time dimension structure I wanted was very similar to the one available with AdventureWorksDW but it has the date only till year 2004.
I had written a simple SP to populate the time fields. If anyone has a similar requirement you can try it.
---Populate Time dimension of AdventureWorksDW sample database with latest
--- You can either Create a new time dimension table or Use the table available in
--- AdventureWorksDW sample database of SQL Server 2005. Download from codeplex.com
set datefirst 1
declare
@starting_dt datetime
,@ending_dt datetime
,@cntr_day datetime
,@diff int
,@cntr int
Select
@starting_dt ='2009-01-01'
,@ending_dt = '2009-01-31'
,@cntr = 0
select
@diff = datediff(dd,@starting_dt,@ending_dt)
while @cntr <= @diff
begin
select @cntr_day = dateadd(dd,@cntr,@starting_dt)
insert into [DimDate]
(FullDateAlternateKey,
DayNumberOfWeek ,
EnglishDayNameOfWeek,
DayNumberOfMonth,
DayNumberOfYear,
WeekNumberOfYear,
EnglishMonthName,
MonthNumberOfYear,
CalendarQuarter,
CalendarYear,
CalendarSemester)
select
@cntr_day
,datepart(dw,@cntr_day)
,case datepart(dw,@cntr_day)
when 1 then 'Monday'
when 2 then 'Tuesday'
when 3 then 'Wednesday'
when 4 then 'Thursday'
when 5 then 'Friday'
when 6 then 'Saturday'
when 7 then 'Sunday'
end
,datepart(day,@cntr_day)
,datepart(dy,@cntr_day)
,datepart(wk,@cntr_day)
,case datepart(mm,@cntr_day)
when 1 then 'January'
when 2 then 'February'
when 3 then 'March'
when 4 then 'April'
when 5 then 'May'
when 6 then 'June'
when 7 then 'July'
when 8 then 'August'
when 9 then 'September'
when 10 then 'October'
when 11 then 'November'
when 12 then 'December'
end
,datepart(mm,@cntr_day)
,datepart(qq,@cntr_day)
,datepart(yy,@cntr_day)
,case (datepart(mm,getdate()))
when 1 then 1
when 2 then 1
when 3 then 1
when 4 then 1
when 5 then 1
when 6 then 1
else 2
end
set @cntr = @cntr + 1
end
--And to get the fiscal properties in your time dimension check this one
http://www.sqlteam.com/article/returning-a-week-number-for-any-given-date-and-starting-fiscal-month
1. Use calculated members with Scope assignments instead of using IIF
The reason this option is faster is because the scope subcube definitions enable the Query Execution Engine to know ahead of time the calculation space for each business rule. Using this information, the Query Execution Engine can select an optimized execution path to execute the calculation on the specified range of cells. As a general rule, it is a best practice to always try to simplify calculation expressions by moving the complex parts into multiple Scope definitions whenever possible.
No-one writing MDX ever wants to show the 1.#INF value in a cell is because it’s looks ugly and typically end user tends to see it as errors in the data. However, it’s not an error it only look like that. If you don’t check for divide by zero errors, you get either positive or negative infinity represented as 1.#INF.
On the other hand, empty cells are typically easier to interpret as no input data. To have this check, lot of time is spent checking division by zero conditions. If it is SQL AS 2005, take a call...you want to perform expensive calculations and deliver clean output OR fast calculations with such ugly looking output (1.#INF).
If it is SQL Server 2008 then things are pretty much taken care, if the numerator is empty, the formula returns empty (regardless of the denominator), but if only the denominator is empty, you get an error. Multiplication by empty results in empty, but with addition and subtraction, empty = 0. The idea seems to be that if the numerator is empty, then an empty denominator is OK, but if the numerator is not empty and the denominator is, something is wrong and an error should appear. Think of examples like price = dollars/units or ratio = current/parent. (This comes from a recent training I had on SQL BI Performance Tuning)
Following are the different ways of checking the denominator. Give it a try and see which one works best for you...
IIF(b = 0, NULL, a/b)
IIF(IsEmpty(b), NULL, a/b)
IIF(b IS NULL, NULL, a/b)
Using IIF statement, calculation space is evaluated Cell-by-Cell. Because of this Query Execution engine selects less optimized path and so its query response time is high. Another computation method is 'Block Computation'
2. Remove empty tuples from MDX result.
This is always a good practice, because empty tuples can not only increase the number of rows and/or columns in your result set, but they can also increase query response time.
Empty tuples can be removed by using NON EMPTY keyword, NON EMPTY function or the NON EMPTY BEHAVIOR (NEB) hint
You can also evaluate empty by using Exists function on a measure group
Use should use Non_Empty_Behavior where ever possible to enable the query execution engine to use bulk evaluation mode. Beware, if you use Non_Empty_Behavior incorrectly, you will return incorrect results. Let's see what Mosha says on this... a case study on different ways of controlling non-empty-behavior here: http://www.sqljunkies.com/WebLog/mosha/archive/2006/11/05/non_empty_behavior.aspx
3. Other techniques of Summarizing data
Summarizing data through MDX can potentially result in slow performance in large-scale cubes or cubes with many nested MDX calculations. If you experience performance issues summarizing data in MDX then you may need to consider other approaches for improving performance like Creating Named Calculations in DSV, Using Measure expressions, Use semiadditive measures and unary operators
And now, I am sure you would be keen to know what are performance improvements done with MDX in SQL Server 2008 (Katmai)....
Performance Improvements for MDX in SQL Server 2008 Analysis Services: http://msdn.microsoft.com/en-us/library/bb934106(S...
MDX in Katmai: Dynamic named sets: http://sqljunkies.com/WebLog/mosha/archive/2007/08...
If you are new to MDX:
MDX Syntax Elements (MDX): http://msdn.microsoft.com/en-us/library/ms146020(S...
MDX Language Reference (MDX): http://msdn.microsoft.com/en-us/library/ms145595(S...
MDX Solutions: With Microsoft SQL Server Analysis Services 2005 and Hyperion Essbase http://www.amazon.com/MDX-Solutions-Microsoft-Anal...
I will be writing about 'Block computation in Katmai and its Impact on Performance' in my next post...stay tuned... :)
TFS Migration and Synchronization Toolkit is now available at
http://www.codeplex.com/MigrationSyncToolkit
This is awesome! A much needed thing has finally arrived. We have been getting request from customer and partner community about How to use TFS effectively in their company/projects with the real work constraint. Thanks a lot to P & P team who worked with the customers and industry partners and finally let this guide out for all of us.
Today morning when I reached office and read J.D.Meier's post, I was really excited looking at the content and reading the first few pages. It has lot of things, not from too much technical perspective (for that we have many blogs, msdn articles, and web casts) but this guide more focused towards ‘How a team/company can use TFS as one product for different activities involved in the SDLC and AM’.
While other articles and blogs talks about technical architecture of TFS or individual features but we were really in need of a guidance which could tell us how all these great technical features will work together for any development team/project. This guide seems to address most of those issues and you will find a very good logical workflow integrating different components of TFS with specific teams like Testing, Development, and Architect etc.
Project Managers and Source Control admins would find these sections of the guide very useful
· Structuring Projects and Solutions in Source Control
· Structuring Projects and Solutions in Team Foundation Source Control
‘Defining Your Branching and Merging Strategy’ section could be used along with "Team Foundation Server Branching Guidance" which provides different approaches of Branching in different release scenarios.
That is all by the quick first look at the guide. Well, no too much to comment now. I need to explore the guide more before I do that.
Njoy reading and implementing the guide!
Microsoft Visual Studio 2005 Team Foundation Server Power Tool
The Microsoft Visual Studio 2005 Team Foundation Server Power Tool (formerly known as Power Toys) is a set of enhancements, tools and command-line utilities that improve the Team Foundation Server user experience. This release includes two new command-line tools for the developer and three non-command line tools: a process template editor, a set of custom check-in policies, and a test tools build task:
- Team Foundation Server Power Tool Commands (tfpt.exe) - A command-line tool with enhanced functionality for Team Foundation Version Control with graphical user interfaces for some commands.
- Process Template Editor - A tool integrated with Visual Studio for authoring custom work item types and some of the associated process template components.
- Check-In Policy Pack - A set of handy check-in policies to address needs customers have expressed.
- Test Tools Build Task - A tool that allows running unit tests by simply specifying the DLLs, or by specifying a file name pattern in TfsBuild.proj, instead of using .vsmdi files to specify tests to run.
For more details and download visit: http://msdn2.microsoft.com/en-us/vstudio/aa718351.aspx
1. Export the exiting workitem type (xml)
Syntax: witexport /f <filename>.xml /t < Team Foundation Server name > /p <project name> /n <WorkItem Type name>
Example: witexport /f task.xml /t MyTFS /p MyProject /n Task
2. Open the task.xml in an editor
4. Import the modified workitem type (xml)
Syntax: witimport /f <filename>.xml /t < Team Foundation Server name > /p <project name>
Example: witimport /f task.xml /t MyTFS /p MyProject
5. Refresh the Team Project and Add a New Task, you should see the changes as follows:
- TeamPlain Web Access is now available as a free download < http://www.devbiz.com/teamplain/webaccess/download.aspx > to all Team Foundation Server customers.
- TeamPlain Web Access will be re-released as Team System Web Access as part of the Team Foundation Server Power Tools. Keep watching the blog for this
- Team System Web Access will be fully integrated into a future version of Visual Studio Team System
TeamPlain Web Access: TeamPlain Web Access is a web interface for Team Foundation Server that allows managing work items, documents, reports, and source control repositories through the web without installing additional software. TeamPlain extends Team Foundation Server to many platforms and provides a convenient, easy to use interface for accessing core Team Foundation Server features.
TeamPlain Web Access product availability: TeamPlain Web Access can be dowloaded from the devBiz website free of charge. In several months, Microsoft will make available an updated version of TeamPlain as part of the Team Foundation Server Power Tools. The TeamPlain product will then be fully integrated into a future release of Visual Studio Team System.
TeamPlain Web Access cost: TeamPlain Web Access starting today is being made available as a free download from the devBiz website. The product can be installed on your existing Team Foundation Server deployments at no additional charge. However, each user who accesses the TeamPlain web portal must have purchased a Team Foundation Server Client Access License (CAL) prior to use.
