SQL Group by

 SELECT p.PROJ_NAME ,
  max(distinct case when  (MPCF.MD_PROP_NAME)='Partner' then (LTV.LT_VALUE_TEXT)  end )PARTNER
 ,max(distinct case when   ( MPCF.MD_PROP_NAME )='Región' then  (LTV.LT_VALUE_TEXT)  end) REGION
 ,max(distinct  case when ( MPCF.MD_PROP_NAME)='Fase' then (LTV.LT_VALUE_TEXT)  end )FASE
 ,max(distinct case when ( MPCF.MD_PROP_NAME)='Area' then (LTV.LT_VALUE_FULL)  end ) AREA
 ,max(distinct case when ( MPCF.MD_PROP_NAME)='Responsable Implementación' then  (LTV.LT_VALUE_FULL)  end ) RESPONSABLE
 ,max(distinct case when ( MPCF.MD_PROP_NAME)='Responsable TISA' then  (LTV.LT_VALUE_TEXT ) end ) RESPONSABLE_IMPLE
 FROM MSP_PROJECTS P      
 INNER JOIN MSP_PROJ_CUSTOM_FIELD_VALUES PCFV ON  (P.PROJ_UID = PCFV.PROJ_UID)
 INNER JOIN MSP_CUSTOM_FIELDS MPCF ON PCFV.MD_PROP_UID=MPCF.MD_PROP_UID -- AND MPCF.MD_PROP_NAME='Partner'
 INNER JOIN MSP_LOOKUP_TABLE_VALUES LTV ON PCFV.CODE_VALUE=LTV.LT_STRUCT_UID
 where
 MPCF.MD_PROP_NAME IN('Partner','Región','Fase','Area','Responsable Implementación','Responsable TISA')
 Group by PROJ_NAME
 order by PROJ_NAME

SSAS: Visibilidad de datos a nivel de totales y o a nivel de detalle

SSAS Different Dimension Granularity

Fuente: http://www.bidn.com/blogs/mikedavis/ssis/706/ssas-different-dimension-granularity

In an SSAS cube you sometimes need to connect a dimension at a different granularity than other dimensions. For example, if you have budget measures and actual numbers. If you are trying to budget your expenses you budget at a higher level than the actual amounts. You budget money for groceries, not for Eggs, Milk, and bread. But your actual amounts will be down to the individual items.

In the cube browser image below you can see this in use. Notice the budget numbers only show on the category level and not on the individual items.

Here are the tables being used in this cube.
FactActuals

FactBudget

DimAccount

To set up this you will need to open the dimension usage tab in the cube editor. Click on the connection between dim account and fact budget and set up the category code as the key between the tables. The category code will be the relationship between the dimension and the fact table. A foreign key between the tables does not need to exist.

One other change is to set the budget measure group to ignore unrelated dimensions. This is in the Cube Structure tab, click on the budget measure group and change this property. It will need to be false, I know this seems backwards.
 

That is all you need.

MOSS 2010: Tips para diseño moss 2010

http://www.sharepointpromag.com/article/sharepoint/15-sharepoint-branding-tips-136366

SQL - Registrar errores de carga en tabla

ALTER PROCEDURE [dbo].[spUpd_CargaDM_xxxx_FactCom-Fin]
==========*/
AS

BEGIN TRY
-- Carga Fact Finanzas y Comercial
BEGIN TRANSACTION;
EXEC usp_TW_FileNumber
EXEC usp_FactFinanzasCostos
EXEC usp_FactFinanzasFileNumber
EXEC usp_FactFinanzasMorosidad
EXEC usp_FactComercialVentas
EXEC usp_FactComercialPresupuesto
COMMIT TRANSACTION;
END TRY
BEGIN CATCH
IF XACT_STATE() <> 0
BEGIN
ROLLBACK TRANSACTION;
END
INSERT INTO Err.CargaDM(ErrorNumber,ErrorSeverity,ErrorState,ErrorProcedure,ErrorLine,ErrorMessage)
SELECT
ERROR_NUMBER() AS ErrorNumber
,ERROR_SEVERITY() AS ErrorSeverity
,ERROR_STATE() AS ErrorState
,ERROR_PROCEDURE() AS ErrorProcedure
,ERROR_LINE() AS ErrorLine
,ERROR_MESSAGE() AS ErrorMessage;
END CATCH;
/*-----------------------------------FIN---------------------------------*/

Cargar tabla Tiempo

SET LANGUAGE spanish



SET DATEFIRST 1

declare @fi datetime, @ff datetime

set @fi = '2010-01-01'
Set @ff = '2050-31-12'

while @fi <= @ff
begin
insert into Tiempo (Idtiempo, Fecha, Anio, Mes,nummes,trimestre,semana,numdiasemana,Diasemana)
--Cuarto, Semana, NumDiaSemana, DiaSemana)
select year(@fi)*10000+month(@fi)*100+day(@fi) as TimeId,
@fi as Fecha,
year(@fi) as Anio,
--year(@fi)*100+month(@fi) as MesID,
DateName( month , DateAdd( month , month(@fi) , 0 ) - 1 )NombreMes,

convert(varchar,month(@fi))Mes,
datepart(q,@fi) as Trimestre,
datename(ww,@fi) as Semana,
datepart( dw, @fi) as NumDiaSemana,
datename( dw, @fi) as DiaSemana
set @fi = @fi + 1
end

MOSS: Ruta LOGS

C:\Program Files\Common Files\Microsoft Shared\Web Server Extensions\14\LOGS

MOSS: Ruta imagenes

C:\Program Files\Common Files\Microsoft Shared\Web Server Extensions\14\TEMPLATE\IMAGES Referencia: ~/_layouts/Images/Circle_Green.png

MOSS 2010 - Configurar reporting service integrate mode moss 2010

CONFIGURACION REPORTING SERVICE CON MOSS2010 Para hacer la integracion es necesario escenario1: moss y reporting en un mismo servidor, no hay problema Escenario 2: moss y reportin gen servidores distintos, el MOSS web front end debe de estar instalado en el report server y bueno en el servidor de aplicaciones que aloja el moss 1. Seguir el step by step de Paso 1: http://anyrest.wordpress.com/2010/06/17/integrating-sharepoint-2010-and-sql-reporting-services-2008-in-6-easy-steps/ Paso 2: http://bhavikmerchant.wordpress.com/2011/02/23/missing-report-library-template-in-sharepoint-2010/ 2. Crear carpeta de tipo conexion, el proyecto de reporting debe de apuntar a esta carpeta para los datasource 3. los reportes debe de ser deployados en una biblioteca de informes (REPORT LIBRARY ), para habilitarla seguir ojo esto es a nivel del sitio no en la admin. central 3. en caso que no se visualice el tipo de biblioteca (REPORT LIBRARY ) debe de seguir lo siguiente : a) ingrese a las configuracion del sitio de proyecto b) Entrar al grupo SITE ACTIONS y dentro de esta hacer click en la opcion MANAGE SITE FEATURES c) Dentro de esta pagina alguna de estas opciones activas debe de habilitar la biblioteca que no esta visible - Group work list - offline Synchronization for external list - sharepoint server enterprise site features - sharepoint server publishing - Tema colaboration list En caso aun no se visulicen los reportes y el error sea de alternate access en el log Ir a: http://social.technet.microsoft.com/Forums/en-US/sharepointgeneral/thread/aea5a5c7-761b-4c49-89df-d313d52f1ee7

SQL - Convert Array to table SQL

DECLARE @ArrayList VARCHAR(MAX)

SET @ArrayList = 'tim,zach,chris,wendi,brad'
-----------------
DECLARE @ArrayList VARCHAR(MAX)

SET @ArrayList = 'tim,zach,chris,wendi,brad'SELECT * FROM dbo.udf_PivotParameters(@ArrayList, ',')
------------------
CREATE FUNCTION [dbo].[udf_PivotParameters]
(
@ParamaterList VARCHAR(MAX),
@Delimiter CHAR(1)
)
RETURNS @ReturnList TABLE
(
FieldValue VARCHAR(MAX)
)
AS BEGIN
DECLARE @ArrayList TABLE
(
FieldValue VARCHAR(MAX)
)
DECLARE @Value VARCHAR(MAX)
DECLARE @CurrentPosition INT

SET @ParamaterList = LTRIM(RTRIM(@ParamaterList))
+ CASE WHEN RIGHT(@ParamaterList, 1) = @Delimiter THEN ''
ELSE @Delimiter
END
SET @CurrentPosition = ISNULL(CHARINDEX(@Delimiter, @ParamaterList, 1), 0)

IF @CurrentPosition = 0
INSERT INTO @ArrayList ( FieldValue )
SELECT @ParamaterList
ELSE
BEGIN
WHILE @CurrentPosition > 0
BEGIN
SET @Value = LTRIM(RTRIM(LEFT(@ParamaterList,
@CurrentPosition - 1))) --make sure a value exists between the delimiters
IF LEN(@ParamaterList) > 0
AND @CurrentPosition <= LEN(@ParamaterList)
BEGIN
INSERT INTO @ArrayList ( FieldValue )
SELECT @Value
END
SET @ParamaterList = SUBSTRING(@ParamaterList,
@CurrentPosition
+ LEN(@Delimiter),
LEN(@ParamaterList))
SET @CurrentPosition = CHARINDEX(@Delimiter,
@ParamaterList, 1)
END
END
INSERT @ReturnList ( FieldValue )
SELECT FieldValue
FROM @ArrayList
RETURN
END

SSAS - Parent child Hierarchies

Bueno hasta que lo encontre...e aqui una manera simple de hacer una jerarquia padre hijo en una dimension, para el ejemplo se ha tomado una tabla denominada DIM_UBICACION_GEOGRAFICA



A partir de esta dimension procedemos a realizar los cambios necesarios en el Dimension structure de la dimension, comemzando con la configuracion del Padre,tal cual se muestra en la imagen





Y para terminar la configuración del hijo




Con estos pasos ya tendriamos lista nuestra jerarquia para ser procesada y visualizada.




Para terminar aqui les dejo el link de referencia

SQL - Registrar errores de carga en tabla

ALTER PROCEDURE [dbo].[spUpd_CargaDM_xxxx_FactCom-Fin]
==========*/
AS

BEGIN TRY
-- Carga Fact Finanzas y Comercial
BEGIN TRANSACTION;
EXEC usp_TW_FileNumber
EXEC usp_FactFinanzasCostos
EXEC usp_FactFinanzasFileNumber
EXEC usp_FactFinanzasMorosidad
EXEC usp_FactComercialVentas
EXEC usp_FactComercialPresupuesto
COMMIT TRANSACTION;
END TRY
BEGIN CATCH
IF XACT_STATE() <> 0
BEGIN
ROLLBACK TRANSACTION;
END
INSERT INTO Err.CargaDM(ErrorNumber,ErrorSeverity,ErrorState,ErrorProcedure,ErrorLine,ErrorMessage)
SELECT
ERROR_NUMBER() AS ErrorNumber
,ERROR_SEVERITY() AS ErrorSeverity
,ERROR_STATE() AS ErrorState
,ERROR_PROCEDURE() AS ErrorProcedure
,ERROR_LINE() AS ErrorLine
,ERROR_MESSAGE() AS ErrorMessage;
END CATCH;
/*-----------------------------------FIN---------------------------------*/